Infrastructure snapshots

The place where you will find posts for Microsoft infrastructure articles, info, latest news and offcourse articles that i create!

Enabling SSL in SQL 2005 cluster

Enabling SSL in SQL 2005 cluster

  • Comments 5
  • Likes

 

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 http://support.microsoft.com/?kbid=900495 

Comments
  • PingBack from http://chaespot.com/mssql/2007/05/02/the-internal-network-adapter-to-let-the-2/

  • "3.  Add the SQL service account into the local administrator group of both cluster nodes"

    -- Why ON EARTH is this step necessary?  Possibly the single most beautiful feature of SQL2005 over previous versions of the product is how well the installer doles out just enough account priveleges to the service accounts to let the sw run, without requiring poor operational practices just like this one.  For many environments (especially ones concerned enough with security to bother implementing SSL) putting the SQL acct in the admin group is a major no-no.  I'm trying to enable ssl encryption on multiple (non-clustered) servers.  I have a 'Server Authentication' certificate installed on each server within the 'Personal' cert store of the service acct. that SQL Server runs as.  Any time I attempt to start SQL with that account *not* in the local admin group, I get the following:

    2007-06-21 14:24:23.93 Server      The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030e. Check certificates to make sure they are valid.

    2007-06-21 14:24:23.93 Server      Error: 26014, Severity: 16, State: 1.

    2007-06-21 14:24:23.93 Server      Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

    2007-06-21 14:24:23.93 Server      Error: 17182, Severity: 16, State: 1.

    2007-06-21 14:24:23.93 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

    2007-06-21 14:24:23.95 Server      Error: 17182, Severity: 16, State: 1.

    2007-06-21 14:24:23.95 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

    2007-06-21 14:24:23.95 Server      Error: 17826, Severity: 18, State: 3.

    2007-06-21 14:24:23.95 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

    2007-06-21 14:24:23.95 Server      Error: 17120, Severity: 16, State: 1.

    2007-06-21 14:24:23.95 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

    C'mon man... there's gotta be a way to enable SSL encryption without making SQL Server root on every box in my environment.  What have ya got for me?

  • Found the answer:

    http://blogs.technet.com/mscom/archive/2007/05/30/how-to-get-sql-to-accept-the-cert-or-a-day-or-two-in-the-life-of-an-mscom-debug-engineer-part-2.aspx

    use winhttpcertcfg.exe to grant the sql service acct access to the local_system cert store without being a local admin.

  • Hi Dan,

    Thanks for posting the URL for the "winhttpcertcfg" workaround, i wish that it was posted before December timeframe where i was in bad need for it:)

    Thanks again for adding this info to the post,

  • Hi Mohamed,

    Thanks for such a nice blog, it helps a lot. One more thing if you can mention, we have certificates installed from private authority on the database server but still connections from clients where those private certificates are not installed gets connected to SQL Server, it should be refused as per my understanding when we have third party certificates are installed on the database server, isn't it? So, if you can put some light on it, how to refuse connections from clients where certificates are not installed?

    I guess those clients get connected using self signed certificates, if its true then we want to stop using self signed certificates.

    Thanks a lot for your time in advance.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment