Install a self-signed test certificate that can be loaded by SQL Server automatically
In the blog Certificate for SQL Server 2005, I explained the requirements of a certificate for SQL Server 2005. Customer usually needs to purchase a certificate from certificate authorities (such as VeriSign), or apply a test certificate from some of the free providers available from Internet. The process is usually complicated and time-consuming. If you just want a certificate for testing purpose, there is an easy way to get one. You can use the makecert tool to generate a self-signed certificate for your own use. The following command would do the work:
makecert -r -pe -n "CN=YOUR_SERVER_FQDN" -b 01/01/2000 -e 01/01/2036 -eku 18.104.22.168.22.214.171.124.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:test.cer
You must replace YOUR_SERVER_FQDN with appropriate stuff. You can get the usage of makecert tool from https://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx, makecert tool comes with winsdk and .Net SDK.
With this, your SQL Server will automatically load the certificate when the SQL Server restarts (if there are multiple certificates meets the requirements, SQL Server will load the first one it finds from the cert store.).
Note that, if you don’t have a certificate, SQL Server will automatically generate one self-signed certificate for you. However, the difference here is: the certificate generated by SQL Server will change every time the server restarts and the certificate’s subject CN is not your FQDN which is in general a critical part if client choose to authenticate the server. More specifically, if the client forces encryption, the connection attempt may fail because the subject CN does not match the server FQDN (in general) and the certificate is not trusted by the client. With the certificate we just generated, your connection attempt may also fail if your client forces encryption. You may get the following on your client machine:
C:>osql -E -Syourserver
[SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
[SQL Native Client]Client unable to establish connection
The reason of the failure is obvious, as stated in the error message. You can solve the issue by doing the following steps:
Copy c:test.cer into your client machine, run c:test.cer from command window, select "Install Certificate". -> click "Next" -> select "Place all certificates in the following store" --> click "Browser" -> select "Trusted Root Certification Authorities" -> select OK and Finish
By trusting the specific certificate, the client does not have to select “trust server certificate” when “force encryption” is selected. Client force encryption and trust server certificate properties can be configured by SQL Server Configuration Manager or by connection string. Check the following page about how the various properties interact. https://msdn2.microsoft.com/en-us/library/ms131691.aspx
Please note that the certificate generated by makecert tool should only be used for testing purpose.
Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights