It has become customary to release updates for Power BI gateways. With this latest release, users connecting to SQL will need to make some changes or they will see SQL errors.

💠 When establishing a connection to a local SQL Server using the on-premises data gateway or Power BI Desktop and SQL Server uses a self-signed certificate, the refresh operation for a Fabric semantic model or dataflow may fail with the following error message:

“Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)”

To avoid the above error, you need to modify a gateway configuration:

💠 On the local computer where the on-premises data gateway is installed, navigate to C:\Program Files\On-premises data gateway.

💠 Back up the configuration file named Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.

💠 Open the original Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config configuration file and locate the SqlTrustedServers entry.

💠 Update the value with the names of the SQL servers to be trusted and connected to. The value contains a comma-delimited list of server names and supports * as a wildcard character. For example, in the following example:

the value matches , , and , but doesn’t match .SqlTrustedServers contososql,mysvrcontososql,mysvrcontososql6contososqlazuremysvrmysvr.microsoft.com

🧇 To resolve this error when using Power BI Desktop, modify the environment variable value to include SQL Server. The supported values are the same as those described for the gateway configuration (as described in step 4 above) PBI_SQL_TRUSTED_SERVERS.

For connections to SQL Server from Power BI Desktop and on-premises data gateway versions starting from February 2024, follow one of these options:

💠 Follow the previously mentioned solution to add the environment variable PBI_SQL_TRUSTED_SERVERS.

💠 Ask SQL administrators to acquire a certificate from a known certification authority.

💠 Modify the setting in the gateway configuration file SqlTrustedServers.

Link here

👊🏽 Just Like That