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).
Program Manager -- SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights