Forum Discussion
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_TRUSTED_SERVERS.
With that setting it is possible to make the wanted visualizations. Great!
Now I published one of the Power BI reports. And want to use Power BI gateway to refresh the on-premises data. And I get the same “Certificate not trusted” error message.
Is possible to make a Power BI gateway trust the certificates of the on-premises SQl Server instances? Like I can do with a Power BI desktop?
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:
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
5 Replies
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.
Thanx for your reply!
What is the best option when the company you work for has 100, or more, SQL Server instances? On Power BI Desktop you can refer to *.mydomain.local for all the instances.
Is this an additional task for every installed instance?
Do the SSL certificates expire after some time? And do you have to update the import root certificates?
Is there some documentation on the options you describe?
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:
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
Hi hcv,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
