Install a self-signed test certificate that can be loaded by SQL Server automatically

  • Article

 

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 1.3.6.1.5.5.7.3.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

Comments

  • Anonymous
    July 03, 2007
    Процесс настройки SSL шифрования трафика в SQL сервере можно условно разделить н

  • Anonymous
    September 05, 2007
    For me it is saying the sql server does not exist.It also says that it may of been renamed or moved or deleted since it was downloaded

  • Anonymous
    October 30, 2007
    I would like to know the following. I want to achieve mutual authentication and if not atleast certificate based authentication between sql server and applications connecting to it. How will a self signed certificate function help me with this in sql server.

  • Anonymous
    October 30, 2007
    The best is to install a formal certificate that meets SQL Server's requirements. If you rely on the self-signed certificate automatically generated by SQL Server, you will fail. However, if you follow my steps above, and generate a self-signed certificate up front and let SQL Server load it, you should be able to achieve what you want(note you need install your certificate to your client machine properly). Thanks.

  • Anonymous
    November 14, 2007
    Is there a way to force the sql 2000 to load the newly created self signed certificate without restarting the sql server? We need to use the self signed certificate as temporary solution in our production environment. Thx Nikolas

  • Anonymous
    August 26, 2009
    Self signed certificate does not work when force encryption is set to true. So why did SQL team made this feature availabale if it is not working anyway, what is the point?

  • Anonymous
    June 04, 2014
    I created the cert exactly like you said , but the server didn't recognize it. I checked all the requirements , including FQDN,enhanced key usage and etc. Still didn't work. Any suggestions please?