Desktop Bug? - Cannot load the data for this visual (on direct query). Connection Timeout Expired.

[erackstraw]

Hi there, I'm getting an error when loading a PBIX file (see below). The report uses a direct query from our data warehouse and works fine on the online service. However, I'm trying to edit the report and cannot see the visuals. I'm currently using Version: 2.111.590.0 64-bit (November 2022). My colleague tried opening the file on his desktop with the same error. Please can you help?

Kind regards,

Ed Rackstraw

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=15634; handshake=10744; ;The wait operation timed out. The exception was raised by the IDbConnection interface.

Feedback Type:
Frown (Error)

Timestamp:
2022-12-12T15:20:29.1398470Z

Local Time:
2022-12-12T15:20:29.1398470+00:00

Session ID:
2a34bfa2-5846-429e-b369-0d04772b9325

Release:
November 2022

Product Version:
2.111.590.0 (22.11) (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=15634; handshake=10744; ;The wait operation timed out. The exception was raised by the IDbConnection interface.

OS Version:
Microsoft Windows NT 10.0.22621.0 (x64 en-US)

CLR Version:
4.8 or later [Release Number = 533320]

Peak Virtual Memory:
70 GB

Private Memory:
566 MB

Peak Working Set:
806 MB

IE Version:
11.1.22621.0

User ID:
c2c1730d-e369-4d5c-bfdc-15bd3d571aa1

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\erackstraw\Microsoft\Power BI Desktop Store App\FrownSnapShot84eff036-6c5c-48ec-9d0a-8668f286d196.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\erackstraw\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_enableWebView2
PBI_sparklines
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_compositeModelsOverAS
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_NlToDax
PBI_horizontalFusion
PBI_optimizeTabRibbon
PBI_relationshipEditPane

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared Daily = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT gcd.[Content_ID]#(lf) ,gct.[Content_Name]#(lf) ,gct.[Web_Page_URL]#(lf)#(tab) ,gct.Published#(lf) ,[Day]#(lf) ,[Week]#(lf) ,[Month]#(lf) ,[Quarter]#(lf) ,[Theater]#(lf) ,[Total_Visits]#(lf) ,[Unique_Visits]#(lf) ,[Downloads]#(lf) ,[MQLs]#(lf) ,[SQLs]#(lf) ,[OpenPipeline]#(lf) ,[Pipeline]#(lf) ,[Wins]#(lf) ,[Total_Sales]#(lf) ,[Matched_MQLs]#(lf) ,[Matched_SQLs]#(lf) ,[Matched_OpenPipeline]#(lf) ,[Matched_Pipeline]#(lf) ,[Matched_Wins]#(lf) ,[Matched_Total_Sales]#(lf)#(tab) ,gct.[Display_Sequence]#(lf)#(tab) ,gct.[Product]#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Contents_Daily] gcd#(lf) inner join [MarketingDataWarehouse].dbo.Gated_Contents gct on gct.Content_ID = gcd.Content_ID#(lf) where gct.Expired = '0'"])
in
Source;

shared Downloads = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT gcw.[Content_ID]#(lf) ,gct.Content_Name#(lf)#(tab) ,gct.[Web_Page_URL]#(lf) ,gcw.[Day]#(lf) ,gcw.[Theater]#(lf) ,gcw.[Email]#(lf) ,gcw.[Company]#(lf) ,gcw.[FirstName]#(lf) ,gcw.[LastName]#(lf) ,gcw.[Country]#(lf) ,gcw.[DownloadCount]#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Contents_Downloads] gcw#(lf) inner join [MarketingDataWarehouse].[dbo].[Gated_Contents] gct on gct.Content_ID = gcw.Content_ID"])
in
Source;

shared MQLs = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT gcm.[Content_ID]#(lf) ,gct.[Content_Name]#(lf) ,gct.[Web_Page_URL]#(lf) ,gcm.[Day]#(lf) ,gcm.[Theater]#(lf) ,gcm.[HotlistID]#(lf) ,gcm.[HotlistName]#(lf) ,gcm.[HotlistType]#(lf) ,gcm.[Disposition]#(lf) ,gcm.[Country]#(lf) ,gcm.[Company]#(lf) ,gcm.[Email]#(lf) ,gcm.[PartnerType]#(lf) ,gcm.[MatchingProduct]#(lf) ,'https://barracuda2018.lightning.force.com/lightning/r/Hot_List__c/' + gcm.HotlistID + '/view' as HL_URL#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Contents_MQLs] gcm#(lf) inner join dbo.Gated_Contents gct on gct.Content_ID = gcm.Content_ID"])
in
Source;

shared SQLs = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT gcs.[Content_ID]#(lf) ,gct.[Content_Name]#(lf) ,gct.[Web_Page_URL]#(lf) ,gcs.[Day]#(lf) ,gcs.[Theater]#(lf) ,gcs.[OpportunityID]#(lf) ,gcs.[Status]#(lf) ,gcs.[Stage]#(lf) ,gcs.[Amount]#(lf) ,gcs.[MatchingProduct]#(lf) ,gcs.[Name]#(lf) ,gcs.[CreatedDate]#(lf) ,gcs.[ClosedDate]#(lf) ,gcs.[Company]#(lf) ,gcs.[Country]#(lf) ,gcs.[PrimaryProductFamily]#(lf) ,'https://barracuda2018.lightning.force.com/lightning/r/Opportunity/' + gcs.OpportunityID + '/view' as OPP_URL#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Contents_SQLs] gcs#(lf) inner join dbo.Gated_Contents gct on gct.Content_ID = gcs.Content_ID"])
in
Source;

shared Visits = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT gcv.[Content_ID]#(lf) ,gct.[Content_Name]#(lf) ,gct.[Web_Page_URL]#(lf) ,gcv.[Day]#(lf) ,gcv.[Session_ID]#(lf) ,gcv.[Theater]#(lf) ,gcv.[Visitor_ID]#(lf) ,gcv.[Landing_Page]#(lf) ,gcv.[Country]#(lf) ,gcv.[Source]#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Contents_Visits] gcv#(lf) inner join dbo.Gated_Contents gct on gct.Content_ID = gcv.Content_ID"])
in
Source;

shared ContentNames = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT [Content_Name]#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Contents]#(lf) where Expired = '0'"])
in
Source;

shared Theaters = let
Source = Sql.Database("10.42.96.133", "MarketingDataWarehouse", [Query="SELECT [Theater]#(lf) FROM [MarketingDataWarehouse].[dbo].[Gated_Content_Theaters]"])
in
Source;

[Anonymous]

Hi @erackstraw ,

According to my research, this is not an issue that appears on PowerBI's side, 

This may be due to many reasons. Please check the below steps to make sure you are not missing anything.

1. Make sure that TCP/IP is enabled as a client protocol on the application server. For more information, see Configure client protocols. On application servers where you don't have SQL tools installed, verify that TCP/IP is enabled by running cliconfg.exe (SQL Server Client Network utility).

2. Check the application's connection string to make sure it's configured correctly. For example, make sure that the connection string specifies the correct port (1433) and fully qualified server name. See Get connection information. 

3. Test the connectivity between the application server and the Azure SQL Database by using SQL Server management Studio (SSMS), a UDL file, ping, or telnet. For more information, see Troubleshooting connectivity issues and Diagnostics for connectivity issues. (You also did that )

Please do also check the below document if you are not missing any steps.
https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-connectivity-issues#di...

If you are still having issues. you may refer to this thread to see if it helps

Connection Timeout Expired. The Timeout Period Elapsed While Attempting To Consume The Pre-login Han...

Best regards.
Community Support Team_ Caitlyn

[erackstraw]

Thanks Caitlyn, well check all of the items on your list, with our IT team.

kind regards,

Ed