On-prem SQL server needs a Trust certificate

Question

Hello Team,

We have on-prem SQL Server which is running from Azure VM. From same Azure VM, we have Gateway running as well. However, we are facing an issue while connecting SQL server with PowerBI desktop and Services.

For this issue, PowerBI team has informed us to get connected with SQL Server Admin team to get a Trust certificate. Therefore, please help with this issue at the earliest..

Trusted Certificate.png

Regards,

Hemant

Answer 1

as it sounds your users are connecting remotely to the sqlserver instead over an internal lan, you should have a valid certificate install to avoid man in the middle attacks. you will purchase a certificate from a trusted provider, and have you sqlserver team install the certificate on the sqlserver.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver17

Answer 2

As long as everything is running on the same server, the easy solution is to add TrustServerCertificate=yes to the connection string in the PowerBI Desktop.

This setting is not suitable for network connections, as it opens for a man-in-the-middle attack. That is, someone may install an evil service between PowerBI and SQL Server that pretends to be SQL Server. This service can steal or manipulate data. But that is a little difficult to do on a local connection.

For network connections, the recommended solution is to install a proper certificate. I believe you buy this from a trusted Certificate Authority. As I work in a developer role, it is something I have never done myself, so I am foggy on the details.

Answer 3

The error indicates that the SQL Server certificate presented to Power BI isn’t trusted by the client.

For this scenario, the supported fixes are:

1. Install or configure a certificate on SQL Server that chains to a trusted root certificate authority.
   • This is the recommended long-term fix.
   • If the certificate is issued by an internal company CA, the client machines must trust that root or intermediate CA certificate.
   • If a self-signed certificate is being used, it must be distributed to client machines manually or through Group Policy.
2. For Power BI Desktop, add the SQL Server name to the PBI_SQL_TRUSTED_SERVERS environment variable.
   • This is supported for Power BI Desktop.
   • The value is a comma-delimited list of server names and supports * as a wildcard.
   • Example: contososql*,mysvr
3. For the on-premises data gateway, update the gateway configuration file to trust the SQL Server.
   • Path: C:\Program Files\On-premises data gateway
   • Back up Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config
   • Open the file and locate SqlTrustedServers
   • Add the SQL Server names to the value
   • Example:

          <setting name="SqlTrustedServers" serializeAs="String">
            <value>contososql*,mysvr</value>
          </setting>
     

   • contososql*,mysvr matches contososql6, contososqlazure, and mysvr, but not mysvr.microsoft.com.
4. If SQL Server administrators manage the server certificate, ask them to replace the self-signed certificate with one from a well-known certificate authority.

Important security note:

• Trusting a server certificate or bypassing certificate validation is a short-term mitigation.
• The best practice is a trusted encrypted connection using a certificate issued by a trusted CA.

Since both Power BI Desktop and the gateway are on the same Azure VM, the SQL Server admin team should verify whether SQL Server is using a self-signed certificate and then either:

• deploy a trusted CA-issued certificate to SQL Server, or
• ensure the required root/intermediate certificate is trusted on that VM.

---

• Troubleshoot custom visuals in Power BI Desktop for Power BI Report Server
• SQL Server
• Certificate validation failure
• Connect to an existing database in SSDT