Friday, January 25, 2013

How to get a SSL Configured on SQL server.

Getting a valid Certificate:

The first steps involves getting a valid certificate from the certificate autority. Since I do not have a Certificate Authority (CA) of my own. I will make use of a utility called MakeCert.exe which is part of .net Framework SDK. You can download it from the following link .NET Framework 2.0 Software Development Kit (SDK) (x86).

Just any certificate cannot be used to enable SSL on SQL server. There are certain requirements a certificate should meet. These are described in the following knowledge base article Encrypting Connections to SQL Server.
Certificate Requirements

For SQL Server to load a SSL certificate, the certificate must meet the following conditions:
  • The certificate must be in either the local computer certificate store or the current user certificate store.
  • The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (
  • The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
  • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
  • SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates. For more information, see the client documentation and KB258858.
So inorder generate a proper certficate on MakeCert utility I used the following command

MakeCert "c:\temp\MyCertnew.cer" -pe -n "" -ss my -sr LocalMachine -a sha1 -sky exchange -eku -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 -iR LocalMachine 

Once the command is completed you can check the Certificate store in the local computer for the certificate. It should look similar to the ones shown below.

 Now let us map each of the command to see if the certificate fulfils our requirement for SQL server.

-pe-- Create a Private Exportable Key.
-n "" -- Set the subject propert with the host FQDN name.
-ss -- Load the certificate in the local store.
-sr-Load the certificate in the local computer store.
-a Signature algorithm used.
-sky exchange-- Set Key spec option on AT_Exchange
-eku-- Enhanced Key used for setting certificate for server authentication.
-sp-- Cryptographic API provider
-sy-- CryptoAPI providers
-ir-- issue certificate store.

So we have got pretty much what is required for configuring SSL on the SQL Server.

Now the questions is how do we confirm that the certificate I have from the CA is valid
Let us verify the certificate properties.

The is a valid FQDN name
The validity of the certificate
and the message " You have a private key that corresponds to this certificate"

The Enhanced key usage property should show the purpose as Server authentication
the subject should also show the valid FQDN name.

Once we have validated it we can use this certificate to configure SQL server using SQL server configuration manager.
Right click on the SQL server 2005 Network configuration
Right click on Protocols for MSSQLSERVER and then select the certificate tab
Under the drop down you can see the certificate.

Also under the flags tab select " Force protocol encryption"

Now go ahead and restart the SQL server. Once we have restarted successfully we can look up the SQL server errorlog to confirm if the certificate is loaded correctly

We should see a message similar to

2013-01-25 13:14:27.150 Server The certificate was successfully loaded for encryption.

Now let us connect to the SQL server and verify if our connections are encrypted.

We could use the following DMV to check the status of the connections

please feel free to ask your question using the comment section.