Why do I have to add SqlTrustedServers to a Config file for my gateways to refresh from SQL Server?

[mmace1]

Recently all my gateway connections to SQL Server started failing. 

Found a solution: 

• Go on each gateway
• Edit the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file
• Add entries for all my SQL Servers in in SqlTrustedServers line (Exampel below)
  
  

But I'm wondering - why?  Why does refreshing data from the most common data source, require hand editing a config file suddenly? 


Our setup:  Data Gateways are on Azure VMs, and are pulling data from SQL Servers, also hosted on Azure VMs. 

Full example of setting in that file

<setting>
<setting name="SqlTrustedServers" serializeAs="String">
<value>ExampleServer1,ExamplerServer2</value> 
</setting>

[v-menakakota]

Hi @mmace1 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Thank you for sharing this. What you're seeing is likely due to a recent change in the on-premises data gateway, which added stricter security for SQL Server connections. The gateway now needs to know which SQL Servers it can trust, so if your server isn’t listed, the connection will fail even if it worked before.

By adding your SQL Server names to the SqlTrustedServers section in the gateway config file, you’re telling the gateway that these servers are safe to connect to. After saving the file and restarting the gateway service, your refreshes should start working again.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[mmace1]

Thanks, so would anyone running the latest 3000.266.4 version of the gateway, need to edit a config file to connect to SQL Server for data refreshes? 

That seems surprising to me (very user unfriendly, for a very common data source) so was wondering if there were other details. i.e. this only comes into play if the user's system has certain characteristics, etc. 

[v-menakakota · Accepted solution]

Hi @mmace1 ,

This behavior does not affect everyone using version 3000.266.4 of the on-premises data gateway. It typically applies when the gateway is connecting to a SQL Server using encrypted connections, and the SQL Server is using a self-signed certificate or one that isn’t fully trusted by the gateway machine. In these cases, the gateway requires the SQL Server to be explicitly listed in the SqlTrustedServers setting to allow the connection.

Please go through the Microsoft Updates blog:
https://powerbi.microsoft.com/en-au/blog/on-premises-data-gateway-september-2024-release/ 

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Thank you and Regards,
Menaka Kota.