Not able to connect to Lakehouse SQL endpoint

[jvangrunderbeec]

Hi,

When trying to connect to a SQL-endpoint from a Lakehouse in Microsoft Fabric with Power BI Desktop I got this error message:

I have no idea how i can verify that the server is allowed remote connections, as it is just the SQL-endpoint from the Lakehouse i'm connecting to...

Has anybody had this problem before?

Kind regards,

Joël

[v-kongfanf-msft · Accepted solution]

Hi @jvangrunderbeec ,

The chief must already have an existing lakehouse. Create a lakehouse in Microsoft Fabric. And be able to successfully open the SQL analytics endpoint file.

Then you can connect to existed lakehouse in desktop, refer to below screenshoot:

For more details, you can read below document:

Power Query Lakehouse (Beta) connector - Power Query | Microsoft Learn

Lakehouse Connector In Power BI Desktop (fabric.guru)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[poli361]

Hi , 

I already have a LakeHouse and a SQL analytics endpoint in the lakehouse. However I cant connect to SQL endpoint from Power Bi desktop using these methods. I keep getting the same error.

Unable to connect

We encountered an error while trying to connect.

Details: "The server name provided doesn't match the server name on the SQL Server SSL certificate. Please see https://go.microsoft.com/fwlink/?LinkId=394782 or try connecting without encryption through the Data Source Settings dialog to continue. (Source at "String SQL Endpoint")"

I can connect from SSMS with these options.

I haven't found a solution yet to connect to power bi desktop , which is mandatory for me.
Thanks in advance

[Element115]

Are you using the SQL endpoint connection string?

[durack99]

Having the same issue. 

Seems as though the connection between the sql endpoint for a lakehouse and power bi desktop is faulty. 

In my case, I simply waited a few hours, and then the connection was suddenly available again. 

[poli361]

Yes friend, obviously!

[Element115]

Ok then I say:  BUG!!  😁

[Element115]

Something is rotten in the kingdom of Denmark!  Look at this:  I am able to connect directly to the lakehouse using the worksapceId and lakehouseId from within a M query BUT NOT using the Power Query GUI! 

let
  Source = Lakehouse.Contents(null){[workspaceId = "id"]}[Data]{[lakehouseId = "id"]}[Data],
  Navigation = Source{[Id = "DimOCState", ItemKind = "Table"]}[Data]
in
  Navigation

And also able to connect to the lakehouse via the GUI by selecting SQL Server connection, which generates this M code:

let
    Source = Sql.Database("SQL_endpoint_ID.datawarehouse.fabric.microsoft.com", "LH_ECOCOUNT", [CreateNavigationProperties=false]),
    dbo_DimOCState = Source{[Schema="dbo",Item="DimOCState"]}[Data]
in
    dbo_DimOCState

I have no effing idea what's going on!!! Why doesn't the GUI connection ADLS Gen2 work? I give it the lakehouse URL and this is the error it spits back in my face!  😠

[Element115]

Actually could you please describe the steps you took to get to this error msg?  Because I was able to connect to my lakehouse SQL endpoint by using a SQL Server connection.

[jvangrunderbeec]

I go to Power BI Desktop

Click on One lake data hub

Choose lakehouses

Select the Lakehouse I want to connect with

Click on the dropdown and choose connect to SQL endpoint

I'm doing this on another tenant as well, and there it works... Very strange... Maybe it could have to do with some security settings?

[Element115]

If I had a dollar for every time I also used the word 'strange'  😁 

The user you are using to sign in to Power BI Desktop, does this user have the same permissions in the tenant/workspace where this LH is that PBI Desktop can't connect to?   

I am assuming that it is not the firewall config since you are PBI Desktop to connect successfully to LHs in other tenants/workspaces.

[jvangrunderbeec]

Yes, my user has access and is able to see the lakehouses and select them. But the connection just doesn't work... 

[Element115]

Could you pls give more details and screenshot of the PBI Desktop GUI to show how you specify the connection paramters? ie what are you selecting on screen while in the Connect dialog?

[Element115]

Yes, had it before and experiencing it again just now.  I vaguely remember posting something about this already in this forum... trying to find it and will get back to you.