Forum Discussion

Bhargava_B's avatar
Bhargava_B
Frequent Visitor
9 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...
dataflow
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    9 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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 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
9 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
    Icon for Super User rankSuper User
    9 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.

    • Bhargava_B's avatar
      Bhargava_B
      Frequent Visitor
      9 months ago

      Hi  , Happy New year and Thanks for your response. 

      After deeper investigation, the root cause turned out not to be parameters, gateway mapping, or StrongDM configuration.

       

      The issue was SQL Server TLS certificate trust on the Power BI Gateway machine.

      Although our paginated (RDL) reports work fine using:

      TrustServerCertificate=True (in the Dataset)

      Power BI Service + On-Premises Gateway is not supporting the same. I think , Power BI does not support TrustServerCertificate or Encrypt overrides in M queries.

      As a result, if the SQL Server certificate chain (root / intermediate CA) is not trusted by Windows on the Gateway VM, refresh fails with:

      The certificate chain was issued by an authority that is not trusted

       

      I feel fix will be on infrastructure-level certificate trust, not a Power BI parameter or gateway configuration issue.How ever curious to know if there are any other fixes around ?

       

      Thanks again for the help.

      MFelix