Can't connect to SQL Clone Server when 'Force Encryption' is enabled in SQL Server
Published 29 April 2021
If your SQL Server is forcing encryption, then you need to explicitly add its user to the certificate used by SQL Clone.
You can check if your SQL Server is forcing encryption by going to the SQL Server Configuration Manager
tool and verify if the Force Encryption setting is enabled in the protocols for your SQL Server version:
If it's enabled and for some reason you cannot disable this setting, you can try the following steps to get it to work in SQL Clone.
Grant permissions to SQL Clone Server user
In the same window, under the Certificate
tab, check the name of the SSL certificate being used for the SQL Server encryption. We'll call it YourCertificateName in our example.
We now need to add the Windows user SQL Server runs under so it can see the certificate's private keys. You can find out its name by going to Windows Services
and checking the properties of the SQL Server service
:
With this information in hand, in your Windows Start Menu, type Manage Computer Certificates
and pick the certificate being used by SQL Server (for this example it's clive.testnet.red-gate.com) and choose to Manage Private Keys...
:
Add the SQL Server user we gathered above and set it to Full control
:
Use the fully qualified name of the certificate's subject
The final step is to ensure SQL Clone Server uses a database connection string that matches the fully qualified name of the host to match the subject in the certificate:
You can easily do this with the SQL Clone Server Setup Tool (accessible from the Windows Start Menu on the machine hosting the SQL Clone Server) and using the fully qualified name matching the certificate's Subject
above: