通过

Server Side Encryption in SQL Server

  • 项目

Hi Friends,

After 3 posts on Database Corruption, I hope you would not mind if I divert my attention a bit and post some blogs related to some other topics. This topic that I going to write about is a very interesting topic; so I thought of taking some time out to write a few lines regarding this.

In this post, I will be writing about enabling Server Side Encryption in SQL Server. All of us know that to enable Server Side Encryption, we need a certificate. However, there is a very strange issue involved into this as well. Before I actually go ahead and explain the issue, I would like to spend a few lines about the properties of the certificate that are required, and how SQL Server loads that certificate to encrypt incoming and outgoing data.

The prerequisites for using a certificate for Server Side Encryption in SQL Server are:

  1. The certificate should be issued to either the Fully Qualified Domain Name of the SQL Server machine, or the Hostname of the SQL Server box. In case of a clustered instance of SQL Server, the certificate should be issued to the FQDN or the NetBIOS name of the Virtual SQL Server Name.
  2. The certificate must have a Private Key corresponding to it.
  3. The certificate Subject Name should be equal to the FQDN of the computer (or the Virtual Server Name, in case of clustered instances); although it may or may not have various other values.
  4. The intended purpose of the certificate should be for Server Authentication.
  5. The certificate path must have a valid chain to the root authority.
  6. The certificate needs to be installed in the SQL Server Startup Account (Current User)\Personal\Certificates or Local Computer\Personal\Certificates folder. (In Certificates Snap-in in MMC).
  7. In case the SQL Server Service Account is not a Local Administrator on the machine, it should have Full Control over C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys.

What happens when SQL Server tries to start when "Force Protocol On" is turned on:

  1. SQL Server starts by reading the following Registry values:
    HKLM \ Software \ Microsoft \ MSSQLServer \ MSSQLServer \ SuperSocketNetLib \ Certificate (for SQL Server 2000 Default Instance)
    HKLM \ Software \ Microsoft \ Microsoft SQL Server \ <Instance Name> \ MSSQLServer \ SuperSocketNetLib \ Certificate (for SQL Server 2000 Named Instance)
    HKLM \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL.n \ MSSQLServer \ SuperSocketNetLib \ Certificate (for SQL Server 2005 Instance)
  2. If it finds a value over there, it tries to look into the Certificates - Current User store to find a certificate that has a thumbprint corresponding to the value it has read from the registry.
  3. In case it does not find any such certificate there, it looks into Certificates (Local Computer) store to find a certificate that has a thumbprint corresponding to the value it has read from the registry.
  4. If it finds a match in any of the stores, it uses the Certificate to load SQL Server.
  5. But, in case, it does not get a match, it simply errors out and stops. A typical error that you will see is as follows:

2007-07-16 08:57:03.66 server Encryption requested but no valid certificate was found. SQL Server terminating.
2007-07-16 08:57:03.66 server Error: 17826, Severity: 18, State: 1
2007-07-16 08:57:03.66 server Could not set up Net-Library 'SSNETLIB'..
2007-07-16 08:57:03.66 server Unable to load any netlibs.
2007-07-16 08:57:03.66 server SQL Server could not spawn FRunCM thread.

That said, let me come to the issue we were facing. We had a machine which had SQL Server installed. The FQDN of the machine was SQLServerMachine.Foo.Bar.local. We had a certificate created for this machine. The properties of this certificate perfectly matched the requirement. In our case, we had the following:

  1. The certificate is issued to SQLServerMachine.Foo.Bar.local.
  2. The certificate has a Private Key corresponding to it.
  3. The certificate Subject Name is equal to the FQDN of the computer; although it has various other values.
  4. The intended purpose of the certificate is for Server Authentication.
  5. The certificate path has a valid chain to the root authority.
  6. The certificate has been installed in the SQL Server Startup Account (Current User)\Personal\Certificates.
  7. In Internet Explorer, we are able to see the certificate under the Current User Login.
  8. The thumbprint value is stored in the Registry in a Binary Value under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Certificate.
  9. The SQL Server Service Account has Full Control over C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys.

However, when we tried to start the SQL Server, it would not start, and threw the error message as discussed above in the SQL Server Errorlog. We did a lot of research to try to figure out the cause of the issue, and what we found is really really interesting.

In our case, we had the thumbprint value of the certificate as:
32 77 5c 23 e9 d8 23 cc 00 68 79 21 92 fd 23 f1 35 99 b7 c3

We figured out that the problem was being caused due to the two consecutive zero's in the thumbprint which was interpreted as a NULL by SQL Server. So, when the SQL Server instance was reading the thumbprint of the certificate from the registry, it interpreted the thumbprint as 32 77 5c 23 e9 d8 23 cc 00. It tried to match the thumbprint of the certificate with the thumbprints of the certificates available in the Current User Store of the SQL Server Service Account and the Local Computer Store. Obviously, it did not find a match, and thereby threw the errors and stopped.

Workaround to the issue:

There are actually two workarounds to the issue:

  1. Go to the registry key HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib. Now, delete the Certificate value. Start SQL Server, and it should start up fine.

    Here is what would happen if we delete the registry entry mentioned above:

    a) SQL Server looks into the registry for a value in HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Certificate. It does not find any.
    b) SQL Server now moves on to the Certificates - Current User store to find any certificate issues to the machine (either to its FQDN or its HostName).
    c) If it does not find a match, it goes to Certificates (Local Computer) store to find any certificate issues to the machine (either to its FQDN or its HostName).
    d) If a match is found, SQL Server checks if that certificate is enabled for "Server Authentication". For this, it queries the "Enhanced Key Usage" structure.
    e) If the certificate is enabled for Server Authentication, this certificate is used to load SQL Server.
    f) However, if SQL Server finds more than one such certificate in the stores, SQL Server would randomly select any one of them.

  2. Get a new certificate from your certificate issuing authority. Make sure that you check that the thumbprint of the new certificate does not contain a sequence of two consecutive zero's. If it does not and if the certificate satisfies all the prerequisites as above, you should be able to use the certificate for Server Side Authentication.

I hope this post will be useful to my readers.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Comments