SSL in SQL Server 2005 [Il-Sung Lee]

I often receive questions inquiring about the (channel) encryption capabilities in SQL Server 2005. Like SQL Server 2000, 2005 can use SSL (Secure Socket Layer) to secure transmissions over the wire independent of the network protocol used. However, unlike its predecessor, SQL Server 2005 will always make encryption available, even if the administrator hasn’t provisioned a SSL certificate on the server machine. For this reason, SQL Server 2005 can and will ensure that the login packet is encrypted even if encryption hasn't been explicitly turned on. More precisely, unless either the client or the server requests encryption, the channel will not be encrypted beyond the login packet.

How? When initializing SSL support at startup time, the server will use the following order to load a certificate:
1. Use the certificate that the administrator has specified in the SQL Server Configuration Manager (right-click on "Protocols for <instance>" under "SQL Server 2005 Network Configuration" and the select the "Certificate" tab)
2. If no certificate has been specified, the server will search the machine and user’s certificate store for an appropriate certificate. The following KB article, a explains what the server considers a valid certificate, https://support.microsoft.com/default.aspx?scid=kb;en-us;318605. (Note that the article was written for SQL Server 2000 but the criteria for a valid server certificate are still employed by SQL Server 2005.)
3. If nothing appropriate can be found, the server will generate a self-signed certificate.

Any other implications? The auto-generation of a certificate is a new feature to SQL Server 2005 and is the reason why encryption will be available even though a SSL certificate has not been provisioned on the server machine. This certificate can be used for full channel encryption but with one caveat. If the client is the one requesting encryption, then it will attempt to perform server validation on the certificate to verify the identity of the server machine. This is a prudent security practice but is impossible to do with a self-signed certificate since it hasn't been signed by a trusted root authority. To overcome this problem, the client may specify the "Trust Server Certificate" flag to override the server validation. Alternatively, you can turn encryption on at the server and leave the client encryption flag off to enable channel encryption.

Conclusion: Whether or not you need channel encryption depends entirely upon your system security requirements and performance tolerance. But one of the nice features of SQL Server 2005 is that it will always be available even if you haven’t installed an SSL certificate on the machine (although I still strongly recommend using a certificate signed by a trusted authority whenever possible).

Il-Sung Lee
Program Manager -- SQL Server Protocols

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

Comments

• Anonymous
  August 05, 2005
  Wow... does this actually mean that "SQL Server Authentication" login packets are finally encrypted, or does this only apply somehow to Integrated Security?
  
  Your posting definately seems to imply that it does encrypt user/pass logins, but it's still hard for me to believe, one of those "pinch me I must be dreaming" type of things :D
  
  You have no idea how long your customers have been waiting for this!

• Anonymous
  August 08, 2005
  Hi shadowchaser, Thanks for your comment/feedback. This applies to all login packets and not just specific to Integrated Security.

• Anonymous
  April 07, 2006
  The comment has been removed

• Anonymous
  April 07, 2006
  Hi Matt,
  
  I'll try to help you get you back up and running as fast as I can.  The only thing that I can think of is that the certificates that you are generating and the one you used for SS2K do not meet the stricter requirements of SS2K5.  Please have a look at this post to see what properties we require from a cert:
  http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx.
  
  Incidentally, I'm assuming that this is you in this forum thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=335106&SiteID=1?  If so, I'd like to move our future correspondance to the forum so we can consolidate the discussion.
  
  Thanks,
  Il-Sung.

• Anonymous
  April 10, 2006
  Yes, that's me. I'll move discussion there....

• Anonymous
  April 26, 2006
  PingBack from http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

• Anonymous
  May 21, 2006
  Hi, I am fairly new to SQL 2005. If I connect to my client's SQL server via SQL Server Management Studio using default setting, does it means it will automatically encrypt the login info?
  
  I tried to tick the option "Encrypt connection" but it came back with message saying:
  
  "A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an untrusted authority.) (Microsoft SQL Server, Error: -2146893019)"
  
  Does it mean I can only use Encrypt connection if the client's server has SSL installed?
  
  Thx
  
  

• Anonymous
  June 02, 2006
  Hi Michael,
  
  If you are connecting from an ADO.Net application to a SQL Server 2005 server, then the login packet will be encrypted.  So if by "pose any security issue" you mean that you are concerned about clear-text password on the wire, then you're fine.
  
  Il-Sung.

• Anonymous
  June 05, 2006
  Hi Il-Sung,
  
  How do you set, at the client,  the "Trust Server Certificate" flag to override the server validation.
  
  Thanks,
  
  Nir

• Anonymous
  July 10, 2006
  The comment has been removed

• Anonymous
  June 08, 2007
  Этот пост посвящен использованию сертификата с собственной подписью (self-signed

• Anonymous
  July 01, 2007
  Within SQL Server 2005 you could take help of certificates to restrict the access from a particular client's

• Anonymous
  May 31, 2009
  PingBack from http://outdoorceilingfansite.info/story.php?id=5161