Forum Discussion

hcv's avatar
hcv
New Member
1 year ago
Solved

Trust server certificate

I wanted to use Power BI Desktop to make visualizations of data on a SQL Server. And ran into an error message. “Certificate not trusted” The solution is to set an environment variable PBI_SQL_TRUST...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi hcv,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you rohit1991 for the prompt response.

     

    If there are 100+ SQL server instances, manually trusting certificates for each one would be inefficient. If the SQL server instances use certificates from an internal Certificate Authority (CA), import the Root CA certificate into the Trusted Root Certification Authorities store on the machine running the power bi gateway. This ensures all certificates issued by that CA are automatically trusted and no need to import each SQL Server certificate manually.

    Regarding the additional task, there is no need to trust each instance separately if the Root CA certificate is imported once.

    Yes the SSL certificates expires, but if we use a Root CA, only the CA certificate needs to be renewed and re-imported when it expires.

    Microsoft does not have specific documentation for this exact scenario, but below resources may be helpful:

    https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot#step-3-check-sql-server-connection

    https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

rohit1991's avatar
rohit1991
Super User
1 year ago

Hi ,Yes, while setting the PBI_SQL_TRUSTED_SERVERS environment variable works for Power BI Desktop, the Power BI Gateway has its own handling of trusted connections and doesn’t use that environment variable. To resolve the “Certificate not trusted” error on the gateway, you’ll need to ensure that the SQL Server’s SSL certificate is properly trusted on the machine where the gateway is installed.

 

This usually means importing the server’s root or intermediate certificate into the Trusted Root Certification Authorities store on the gateway machine. Alternatively, configure the SQL Server connection in your gateway settings to not require encryption, or set it to encrypt without validating the certificate, though this is less secure. For production environments, trusting the certificate is the recommended and secure approach.