Learning to configure SQL Server to use a self-signed SSL certificate was not really part of my training plan. However, Data Explorer required SSL encryption to connect to SQL Server. On the same day I managed to make it work, the DE team released an update that waives this requirement …* sigh* ...
The steps described below are just for development and tests, to connect to a local instance of SQL Server. Best practices for SSL encryption and server security may look different.
Here is a summary of how I did it on my machine (SQL Server 2008, 2012 – Express editions too -Windows 7 Ultimate).
To configure SQL Server to use a self-signed SSL certificate, you will have to:
Point number 2 is the one I had most problems with. The SQL Server log had entries like these:
Solution: Grant SQL Server rights to read the private key for the certificate.
All steps are described below.
If you have IIS on your machine:
The process as explained by SQL Server BOL uses the makecert command line utility. Since I could not find makecert , until I had solved my problem, I did not follow BOL. You can find the tool here.
One way to manage your certificates is to
I tried to make this post as detailed as possible, but not too detailed. Any questions, suggestions, or corrections? Use the comments below.
If your SQL Server instance service account is NT Service then this may not work. There is a workaround to make the NT Service account work, but easier just to use a normal user service account. http://support.microsoft.com/kb/900495
One question I have; SSL certs work on exchanging public and private keys for encryption, correct? For example, a web browser on a PC gets the public key from a site, it uses this to encrypt data and the private key on the web server can decrypt it. So the client has to use the public key to encrypt data which then gets sent to the sever for decryption.
In a SQL server environment, how does the client use the public key to encrypt data if the public-private exchange happens on the SQL server? I don’t understand how this part of the scenario works (I know it does; we did testing here at my office and could see previously unencrypted packets being encrypted).
Thanks for the interest and sorry for the late answer.
When there is a direct connection between the client and SQL Server, I do not see any reason why SQL Server would handle SSL encryption differently from a web server, which I would summarize as:
– the server sends the public key to the client
– the client sends back a random number that the server will decrypt with the private key
– this number is used by the client and the server to generate a session key
– further information exchanges are encrypted with the session key
Keep in my mind my knowledge of data encryption is very limited, so I may miss something here.
If there is a web site between the client and SQL Server, things probably get more complicated than that, but I will not be able to elaborate on this scenario.
YES! I spent almost an entire day yesterday trying to diagnose issues here, and it was all because for some reason the SCCM certificate wasn’t assigned to the database instance, how that happened (as it _was_ working) I’ve no idea, but this posting helped me fix it, thank you!
[…] Using a self-signed SSL certificate with SQL Server: https://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-serv… […]