I had a request from the client on how to enable SSL connections to their production SQL Server 2008 R2 instance to ensure that all network traffic between client application and related database is encrypted. The requirement came out of a PCI audit that dictated that Credit Card numbers should not be transported over the

internal network as viewable text.

So you would think the Microsoft KB Article http://support.microsoft.com/kb/316898 would make the exercise straight forward. Wrong!!

The certificate was imported/configured correctly on the server but still the certificate would not show in the SQL Server Configuration Manager so that the server side SSL could be enabled. In addition, there was another complication, the application used a DNS alias for the SQL Server name instead of the server name (default instance).

The second problem was easy, rather than play with Subject Alternative Names in the certificate setup, the customer bought a wildcard certificate so anything connecting with <somename>.<FQDN> would match the certificate over the FQDN. Too easy! Note though that the server is a SQL Server 2008 R2 instance, this also worked for SQL Server 2012.

The first problem was addressed by performing the following steps :-

1. Go to MMC snapin and double-click on the Certificate.
2. Go to the Details tab and locate the Thumbprint, copy the Thumbprint numbers and paste into Notepad.
3. Remove the spaces between the hex characters in the thumbprint value in Notepad, if any.
4. Copy the string and paste in Registry in the value for Certificate at:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCENAME>\MSSQLServer\SuperSocketNetLib
5. Turn Encryption (set Force Encryption to Yes) on for SQL Server via the SQL Server Configuration Manager.
6. Restart the SQL Server