Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Error : While connecting to SQL DB Table using Power BI DirectQuery

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;

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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://social.technet.microsoft.com/Forums/en-US/84b08d13-ca15-4295-bf10-3a6f195fcf35/sql-server-prelogin-handshake?forum=sqldatabaseengine 

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.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

v-stephen-msft
Community Support
Community Support

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://social.technet.microsoft.com/Forums/en-US/84b08d13-ca15-4295-bf10-3a6f195fcf35/sql-server-prelogin-handshake?forum=sqldatabaseengine 

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.

 

mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

We need to daily refresh and we have lot of dashboards, hence looking for Solution via DirectQuery.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.