Using a self-signed SSL certificate with SQL Server


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

Short version

To configure SQL Server to use a self-signed SSL certificate, you will have to:

  1. Create a self-signed certificate
  2. Set permissions for this certificate
  3. Configure SQL Server to use this certificate

Point number 2 is the one I had most problems with. The SQL Server log had entries like these:

  • “The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.”
  • “Unable to load user-specified certificate …”
  • “… Unable to initialize SSL support. Cannot find object or property.“

Solution: Grant SQL Server rights to read the private key for the certificate.

All steps are described below.

Create a certificate

With IIS

If you have IIS on your machine:

  • Start IIS Manager
  • Go to Server Certificates
  • Right Click > Create Self-Signed Certificate

With makecert

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.

Set permissions for the certificate

Find the service account name for your SQL Server instance

  • Start SQL Server Configuration Manager
  • Go to SQL Server Services
  • Select your instance
  • Right-click Properties
  • Copy the account name for later re-use

Launch the Certificates snap-in

One way to manage your certificates is to

  • Launch MMC (type mmc.exe in the Windows search bar)
  • Go to File > Add / Remove Snap-in …
  • Select Certificates ( when prompted choose Computer Account)

Grant SQL Server rights to read the private key for the certificate.

  • In MMC, go to Certificates (Local computer) > Personal > Certificates
  • The certificate should be listed there. ( If  you created the certificate using makecert, you may have to import it.)
  • Right click > All Tasks > Manage Private Keys
  • Add the service account for your instance of SQL Server
  • Give the service account Read permissions

Configure SQL Server to use this certificate

  • Start SQL Server Configuration Manager
  • Go to SQL Server Network configuration
  • Select your instance
  • Right-click > Properties > Certificate tab
  • Choose the certificate you just created
  • Restart the service

Conclusion

I tried to make this post as detailed as possible, but not too detailed. Any questions, suggestions, or corrections? Use the comments below.

Advertisements

8 Responses to “Using a self-signed SSL certificate with SQL Server”

  1. ryan werts Says:

    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

  2. tensigh Says:

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

    • TheDataSpecialist Says:

      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.

  3. Case of the SCCM 2012 Application Catalog Communication Breakdown | chentiangemalc Says:

    […] https://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-serve… […]

  4. Steve Wells (@KnightNZ) Says:

    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!

  5. Sharif Says:

    Thank you, this is awesome.

  6. michele3825 Says:

    Thanks, this was helpful

  7. FIX: TDSSNIClient initialization failed with error 0x80092004,SQL Server could not spawn FRunCommunicationsManager thread | Sqljunkieshare Says:

    […] https://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-serv… […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: