When create mirrored SQL server in Fabric to on-premises SQL database, we will need to use On-Premises Data Gateway. We might encounter issue with Use Encrypted Connection option with self-signed certificate. To resolve issue with Use Encrypted Connection, try following solutions:
Solution 1: Add your SQL Server to the “Trusted Servers” List:
There is a “hidden” configuration in the On-premises Data Gateway that allows you to bypass certificate validation for specific servers.
On the machine where the Gateway is installed, navigate to: C:\Program Files\On-premises data gateway
Find and open the file Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config in a text editor like Notepad (run as Administrator).
Search for the setting: SqlTrustedServers.
Add your SQL Server name (the one you typed into the Fabric connection box) as the value:
<setting name="SqlTrustedServers" serializeAs="String">
<value>YourServerNameOrIP</value>
</setting>
Note: If you have multiple servers, use a comma-separated list.
Save the file and Restart the On-premises Data Gateway service via the Windows Services app (services.msc).
Solution 2: Force the Gateway to Trust the Self-Signed Cert
If Solution 1 doesn’t work, you must manually move your self-signed certificate into the Windows Trusted Root store so the Gateway “trusts” it as a valid authority.
Export the Certificate if you don’t have a copy:
Open Manage Computer Certificates (certlm.msc).
Go to Personal > Certificates.
Find the certificate used by SQL Server (it usually matches your computer name).
Right-click > All Tasks > Export… (Choose .CER format, do not export private key).
Import the Certificate:
In the same certlm.msc window, go to Trusted Root Certification Authorities > Certificates.
Right-click > All Tasks > Import… and select the file you just exported.
Restart the Gateway service.