Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
We have a SQL Server hosted in Azure and its used for current project and we use Tableau with it. We need to migrate to Power BI and hence i tried to access the tables from Power BI in the same way i access from Tableau Desktop. Since the server data can be accessed via VPN i cant connect to the DB directly with credentials. We have a separate account for connecting to the DB but can use company SSO for accessing the reports vis Tableau Online.
I am able to get the data using Import option but i need to do the same using DirectQuery as thats how we want to build the reports. I have checked with many peers and experts and they are not able to find a way over this connectivity issue.
I am displaying the error below so that you can take a look at it and provide a solution for our connection issue. We will save lot of effort if we are able to connect the data directly from Power BI.
Error :
Feedback Type:
Frown (Error)
Timestamp:
2020-10-15T15:22:38.9648354Z
Local Time:
2020-10-15T20:52:38.9648354+05:30
Session ID:
c7ffd912-6137-40d4-b270-d96345301a82
Release:
September 2020
Product Version:
2.85.985.0 (20.09) (x64)
Error Message:
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=14846; handshake=10087; ;The wait operation timed out. The exception was raised by the IDbConnection interface.
OS Version:
Microsoft Windows NT 10.0.17763.0 (x64 en-US)
CLR Version:
4.7 or later [Release Number = 461814]
Peak Virtual Memory:
38.6 GB
Private Memory:
645 MB
Peak Working Set:
881 MB
IE Version:
11.1457.17763.0
User ID:
jxjdjfj-jdjf-9887-kjhgg-hshdhdh
Workbook Package Info:
1* - en-IN, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Snapshot Trace Logs:
C:\Usersxxxxxx\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot508210869.zip
Model Default Mode:
Composite
Model Version:
PowerBI_V3
Is Report V3 Models Enabled:
True
Performance Trace Logs:
C:\Usersxxxxxxx\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Enabled Preview Features:
PBI_NewWebTableInference
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_ImportTextByExample
PBI_qnaLiveConnect
PBI_inlineExplore
PBI_dataSearchCuration
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_narrativeTextBox
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
DPI Scale:
150%
Supported Services:
Power BI
Formulas:
section Section1;
shared tablexxx= let
Source = Sql.Databases("10.1.12.4"),
db= Source{[Name="db"]}[Data],
tablexxxx= db{[Schema="dbo",Item="table"]}[Data]
in
dbo_sf_tb_rpt_GAPM_bkp;
shared #"sf_tb_rpt_GAPM_bkp (2)" = let
Source = Sql.Databases("10.1.12.4"),
db= Source{[Name="db"]}[Data],
table= EY_Learning_QA{[Schema="dbo",Item="table"]}[Data]
in
table;
Solved! Go to Solution.
Hi @Anonymous ,
Based on my research, the error might occurs in the following possibilities:
1. An instance of the SQL Server Database Engine is not running.
2. The SQL Server Browser service is not running.
3. The TCP/IP is disabled.
4. The server name was typed incorrectly.
5. There are network problems.
6. The TCP/IP port for the Database Engine instance is blocked by a firewall.
7. The client and server are not configured to use the same network protocol.
Then for the solutions, please refer to following articles:
https://blog.devoworx.net/2017/01/30/connection-timeout-expired-pre-login-handshake/
https://support.na.sage.com/selfservice/viewContent.do?externalId=83234&sliceId=1
https://www.kb.blackbaud.com.au/articles/Article/114624
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Option 7 seems to be the issue 7. The client and server are not configured to use the same network protocol. We need to connect with the server to to configure the Power BI gateway
Hi @Anonymous ,
Based on my research, the error might occurs in the following possibilities:
1. An instance of the SQL Server Database Engine is not running.
2. The SQL Server Browser service is not running.
3. The TCP/IP is disabled.
4. The server name was typed incorrectly.
5. There are network problems.
6. The TCP/IP port for the Database Engine instance is blocked by a firewall.
7. The client and server are not configured to use the same network protocol.
Then for the solutions, please refer to following articles:
https://blog.devoworx.net/2017/01/30/connection-timeout-expired-pre-login-handshake/
https://support.na.sage.com/selfservice/viewContent.do?externalId=83234&sliceId=1
https://www.kb.blackbaud.com.au/articles/Article/114624
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Another option would be to build your report in Import mode, publish it to Power BI, and then build your reports with a live connection to the Power BI Dataset.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
We need to daily refresh and we have lot of dashboards, hence looking for Solution via DirectQuery.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |