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,
http://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 (13)

  1. ShadowChaser says:

    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 😀

    You have no idea how long your customers have been waiting for this!

  2. MSDNArchive says:

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

  3. Matt Flynn says:

    Hi Il-Sung,

    I have recently upgraded to SQL 2005 from SQL 2K. The whole upgrade process was fairly seemless with the big exception of encryption.

    As soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. So my question is, what could cause this?

    We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.

    In the How section above, you mention in #1 about using the certificate the admin has specified. Well, we have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.

    Any ideas why we can’t see the cert in the list or why the encryption failed after a SQL upgrade?

    We’re in much need of help on this as it’s already been two long weeks trying to resolve.

    Many thanks!

    -Matt

  4. SQL Protocols says:

    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.

  5. Matt Flynn says:

    Yes, that’s me. I’ll move discussion there….

  6. Michael says:

    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

  7. Il-Sung says:

    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.

  8. Nir says:

    Hi Il-Sung,

    How do you set, at the client,  the "Trust Server Certificate" flag to override the server validation.

    Thanks,

    Nir

  9. Mauro SB. says:

    i ve read that when using self signed cert in sql2005, the error "the certificate chain was issued by an untrusted authority".

    A simply change in the conn string to "TrustServerCertificate=true" can workaround the problem, but what happen when you dont have acces to this conn string I.e. when you use "OSQL". it happened to me ,when i upgraded a website using OSQL from w2k to w2003(its uses SSL in the IIS that cant be shut down) i suppose that this w2003 service is forcing OSQL to connect using SSL. what do you suggest?

  10. Yan Liberman says:

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

  11. Within SQL Server 2005 you could take help of certificates to restrict the access from a particular client’s