Saturday, June 30, 2007

SSL Cipher Suites used with SQL Server

by SQL Protocols via SQL Protocols on 6/30/2007 1:38:00 AM

When enabling channel encryption between the application and SQL Server, users may wonder what encryption algorithm is being used to protect their data.  Unfortunately, this isn't an easy question to answer and here's why.

SQL Server (both 2005 and 2000) leverages the SChannel layer (the SSL/TLS layer provided by Windows) for facilitating encryption.  Furthermore, SQL Server will completely rely upon SChannel to determine the best encryption cipher suite to use.  Incidently, a cipher suite is a set of cryptographic algorithms that specifies the algorithm for key exchange, encryption, and message authentication (http://msdn2.microsoft.com/en-us/library/aa374757.aspx).  What the best cipher suite to use is negotiated by SSL/TLS and depends upon the cipher suites supported by the OS on the client and the server.  This negotiation is described in this white paper, http://download.microsoft.com/download/7/8/0/780b2238-1fc4-47f5-aa5b-def979ba558b/SSL%20Whitepaper.DOC, but simplistically it can be described as:

1. Determine the highest level protocol mutually supported by the client and the server.
   The currently recognised protocols are, from highest to lowest: TLS1.1, TLS1.0, SSL3.0, SSL2.0 
2. The client will provide the server with a list of its cipher suites from the negotiated protocol
3. The server will chose the strongest cipher suite that it is able to support from the client's list.

The following links list the cipher suites available for SSL2.0, SSL3.0, and TLS1.0:
http://msdn2.microsoft.com/en-us/library/aa380124.aspx
http://msdn2.microsoft.com/en-us/library/aa380512.aspx

Unfortunately, I have been unable to locate a reference providing a list of cipher sutes for each OS.  When I do find a reference, I will update this posting.

One last thing.  Some of you may be cuious to know if there is a way to influence the cipher suites that are used for channel encryption.  While there is nothing you can do at the SQL Server level, you can do something at the OS level as descrbed in this KB article, http://support.microsoft.com/kb/245030.  I don't expect that this is something that most of you will want or need to do.  If you're concerned about the cipher suites used because of FIPS compliance, then please refer to this article instead: http://support.microsoft.com/kb/920995.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights