Forum Discussion

jvangrunderbeec's avatar
jvangrunderbeec
Icon for Helper I rankHelper I
2 years ago
Solved

Not able to connect to Lakehouse SQL endpoint

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

Need Help

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      poli361
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Element115
        Icon for Memorable Member rankMemorable Member
        2 years ago

        Are you using the SQL endpoint connection string?

         

  • Element115's avatar
    Element115
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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.

  • Element115's avatar
    Element115
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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's avatar
      jvangrunderbeec
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Element115
        Icon for Memorable Member rankMemorable Member
        2 years ago

        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.

  • Element115's avatar
    Element115
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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!  😠