Forum Discussion

Bhargava_B's avatar
Bhargava_B
Frequent Visitor
8 months ago
Solved

Gateway keeps using old SQL Server data source (StrongDM suffix) even after parameter change

I’m facing a persistent issue with Power BI Desktop → Power BI Service → On-Premises Data Gateway when using StrongDM (SDM) as a secure access layer to SQL Server.

 

Environment

  • Power BI Desktop (Import mode)

  • Power BI Service

  • On-Premises Data Gateway (Enterprise mode)

  • SQL Server (on-prem)

  • StrongDM client used for database access

  • Authentication via service account on Gateway VM

StrongDM Context

For developers, StrongDM exposes SQL Server using a local alias with role + port, for example:

Dev-Mhgh01.dev.local.all_rwd_sql,10700

However, for Power BI Service + Gateway, the dataset must use the real hostname only, without the StrongDM suffix:

Dev-Mhg01.dev.local

StrongDM client runs on the Gateway VM and brokers access transparently.

What I’m Trying to Achieve

  • Develop the semantic model locally using the StrongDM alias

  • Publish the same model to Power BI Service

  • Bind it to the On-Premises Data Gateway using only the real SQL hostname

  • Avoid embedding the StrongDM suffix in the published dataset

What I Have Done

  1. Built a semantic model in Import mode

  2. Created Power Query parameters:

    • serverWithSDM = Dev-Mheg01.dev.local.all_rwd_sql,10700

    • serverWithoutSDM = Dev-Mheg01.dev.local

    • Server (active selector)

    • DB

  3. All queries use:

     
    Sql.Database(Server, DB, [Encrypt=true, TrustServerCertificate=true])

  4. Verified no hardcoded SDM suffix exists in any query

  5. Deleted the existing semantic model from Power BI Service

  6. Closed and reopened the PBIX

  7. Switched parameter to serverWithoutSDM

  8. Clicked Close & Apply

  9. Published again

Problem 1 – Desktop

When I switch the parameter to serverWithoutSDM in Power BI Desktop and click Close & Apply, I get:

 

 
OLE DB or ODBC error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

(This is expected locally because my machine can only connect via StrongDM alias.)

Problem 2 – Power BI Service / Gateway (Main Issue)

Even after:

  • Deleting the dataset from Service

  • Republishing with serverWithoutSDM

  • Ensuring no SDM suffix exists in M code

👉 In Power BI Service → Gateway → Cloud connections,
the dataset still shows the old server value with the StrongDM suffix, e.g.:

 

 
Dev-Mheg01.dev.local.all_rwd_sql,10700

As a result:

  • Gateway binding fails

  • Dataset refresh fails

  • Error: Gateway is not configured

Key Observation

In Power BI Desktop → Model / Power Query, I still see parameters listed under Expressions, e.g.:

  • serverWithSDM

  • serverWithoutSDM

  • db

Even though the active parameter is set to serverWithoutSDM, Power BI Service appears to retain the original data source identity that was created when the model was first evaluated using the SDM suffix.

My Questions

  1. Is Power BI Service persisting the initial data source identity permanently, even after dataset deletion and republish?

  2. Is there any supported way to force Power BI Service to:

    • Recreate the data source identity

    • Bind the dataset to the Gateway using a different server name?

  3. Is it architecturally impossible to use a single PBIX when:

    • Desktop can only connect via one hostname

    • Gateway can only connect via another?

  4. Is the only supported approach to maintain:

    • One PBIX for development (StrongDM alias)

    • One PBIX for publishing (real hostname, prepared on a gateway/jump VM)?

Expected Outcome

I want Power BI Service to see and bind only:

Dev-Mheg01.dev.local

 

and never retain:

Dev-MHeg01.dev.local.all_rwd_sql,10700
 

Clear guidance on data source identity behavior, gateway binding, and supported deployment patterns would be extremely helpful.

Dataflow
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    7 months ago

    Hi Bhargava_B 

    Your summary is accurate. This issue is not related to parameters, dataset identity, or StrongDM. The underlying problem is due to TLS certificate trust enforcement by the Power BI On‑Premises Data Gateway.

    Unlike Power BI Desktop, SSMS, or paginated reports, the gateway does not respect TrustServerCertificate=True or encryption overrides in M queries. When refreshing, the Power BI Service expects the SQL Server certificate chain to be fully trusted by the Windows OS on the gateway VM. If the root or intermediate CA is missing or not trusted, the gateway fails the TLS handshake and shows a “certificate chain was issued by an authority that is not trusted” error.

    There is no supported workaround on the Power BI side. To resolve this, install the proper root and intermediate certificates on the gateway VM, or use a SQL Server certificate from a trusted CA. Once the certificate chain is trusted, the gateway and dataset refreshes will work as expected.

    If you have any more questions, please let us know and we’ll be happy to help.

    Regards,

    Microsoft Fabric Community Support Team

9 Replies

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    7 months ago

    Hi Bhargava_B 

    Your summary is accurate. This issue is not related to parameters, dataset identity, or StrongDM. The underlying problem is due to TLS certificate trust enforcement by the Power BI On‑Premises Data Gateway.

    Unlike Power BI Desktop, SSMS, or paginated reports, the gateway does not respect TrustServerCertificate=True or encryption overrides in M queries. When refreshing, the Power BI Service expects the SQL Server certificate chain to be fully trusted by the Windows OS on the gateway VM. If the root or intermediate CA is missing or not trusted, the gateway fails the TLS handshake and shows a “certificate chain was issued by an authority that is not trusted” error.

    There is no supported workaround on the Power BI side. To resolve this, install the proper root and intermediate certificates on the gateway VM, or use a SQL Server certificate from a trusted CA. Once the certificate chain is trusted, the gateway and dataset refreshes will work as expected.

    If you have any more questions, please let us know and we’ll be happy to help.

    Regards,

    Microsoft Fabric Community Support Team

    • Bhargava_B's avatar
      Bhargava_B
      Frequent Visitor
      7 months ago

      Hi v-karpurapud ,

       

      Thank you for the information, I have set up a call with My Infra(admin) team to discuss this, I will post the outcome post discussions.

      Thank you

      Bhargava

  • MFelix's avatar
    MFelix
    Super User
    8 months ago

    Hi Bhargava_B ,

     

    My experience with this type of setup is the following:

    • You need to create a connection string for the parameter you want to have connect in this case the serverwithoutDSM
    • After publish your semantic model go to settings and change the parameter for the server, database, whatever other information you need, in your case believe is only the Server
    • Go to the Gateway and cloud connections and select the proper gateway connection you have created in the first step and check the authentication
    • You should get a message refering that the credentials are picked up from the connection string
    • Then do a refresh of the semantic model and schedulle your refresh

     

    After doing this you should be good but be aware I advise that for future publish of the semantic model you use the ALM toolkit because you can choose to keep the parameters of the service has they are and no need to go back to redo the previous steps, if you do publish trough Power BI desktop you need always to change the parameters and check the gateway connection.

     

    If you need any further guidance please let me know 

     

     

    • Bhargava_B's avatar
      Bhargava_B
      Frequent Visitor
      8 months ago

      Hello MFelix ,

       

      Thanks for the detailed response.

      The challenge in my case is that Power BI Desktop cannot successfully evaluate the model using the server value required by the Gateway (no StrongDM suffix).

      Desktop connectivity only works via the StrongDM local alias, while the Gateway requires the real hostname.

      Because Desktop evaluation fails, the dataset identity is never created with the non-SDM server value, so Service-side parameter changes and gateway mapping do not take effect.

      I am not sure but this appears to be a limitation when Desktop and Gateway require different connection identities. 

       

      Regards,

      Bhargava

      • MFelix's avatar
        MFelix
        Super User
        8 months ago

        Hi Bhargava_B ,

         

        Just to clarify one thing how are you seting up the parameters?

        You refer:

        • serverWithSDM = Dev-Mheg01.dev.local.all_rwd_sql,10700

        • serverWithoutSDM = Dev-Mheg01.dev.local

        • Server (active selector)

        • DB

        What what do you mean in Server active selector? How are you passing the different parameters to your queries? Can you just share the Parameter setup and the first steps of the connections so I can check what I'm missing.

         

        My normal setup with this type of need is a single parameter for server in this case I would have:

        Server: Dev-Mheg01.dev.local.all_rwd_sql,10700 (on Power BI desktop)


        After publishing the semantic model I change that parameter to the following

        Server: Dev-Mheg01.dev.local (on Power BI Service)

         

        Then check if the gateway would connects to this server.

         

        Usually this setup is preety straighforward but again I may be missing something on your question.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    7 months ago

    Hi Bhargava_B 

    I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

    Thank You.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    7 months ago

    Hi Bhargava_B 

    We would like to confirm whether the issue has been resolved. If it is still outstanding, please share any additional information so we can assist you further.

    Thank you.