Enabling SSL in SQL 2005 cluster

  • Article

 

Did you tried to enable SQL encryption between the client and your SQL instance? I thought that configuring SQL 2005 cluster to encrypt its traffic is simple however I discovered that it is not that straight forward and you always get this error “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” to understand more about SSL in SQL 2005 & how to configure it read below.

Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to all SQL Server clients except DB Library and MDAC 2.53 clients.

SSL can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server. Encryption with a self-signed certificate is possible as the Credentials (in the login packet) that are transmitted when a client application connects to SQL Server 2005 are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide authentication or nonrepudiation. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate. Note that SSL connections that are encrypted by using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.

Note: Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance. When all traffic between SQL Server and a client application is encrypted using SSL, the following additional processing is required:

  • An extra network roundtrip is required at connect time.
  • Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.
  • Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.

To configure SSL encryption to work with a certificate from a public certification authority follow the below steps:

  1. Generate a certificate with the following requirements
    • Certificate CSP should be “Microsoft RSA SChannel Cryptographic Provider”
    • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1)
    • 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
  2. Import the same certificate on both nodes into the following locations
    • Computer container
    • SQL services account personal container
  3. Add the SQL service account into the local administrator group of both cluster nodes
  4. Open the registry editor and add the thumbprint of the certificate into the following string key “Certificate” under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib”
    • Using Certificate Mgr (MMC), double-click on the Certificate.
    • Select the Details tab
    • Scroll down to Thumbprint and highlight
    • Copy the Thumbprint numbers and paste into Notepad
    • Remove all the spaces from the string
    • Copy the string and paste in Registry in the value for Certificate string at:
  5. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib
  6. Restart the cluster node

Note that after doing all of the above steps you will notice that the certificates is not listed in the SQL Server configuration manager - > Protocols for the instance. However the SSL is working & you can check by looking into the SQL logs. To get to know that your certificate loaded successfully try to search SQL Server Error log (in SSMS) for

Source: Server

Message contains: certificate

Note: When Microsoft SQL Server 2005 is running under the Network Service account, you cannot enable encryption by using a certificate. If you provision a certificate for use in encryption, SQL Server will not start. Additionally, you may notice an error message in the SQL Server error log. To solve this problem compile the code in KB 900495 https://support.microsoft.com/?kbid=900495