Certificates in SQL Server 2008

Solution 1:

If I understand the MSDN spec correctly, all you need is to specify in the connection string Encrypt=True;TrustServerCertificate=True. This means that the client requests encryption and is willing to accept any certificate the server may use. The server always has a self-signed certificate generated at server startup time to use, if nothing else is available. If the client is willing to accept any certificate, then it will accept that server's temporary self-signed one, is just as good as any.

What such a setup provides is an encrypted communication channel between your application and your server, a channel that cannot be ear dropped with ease. However, the channel is open to a malicious man-in-the middle attack. If an attacker can fool the client to connect to him instead of the server (eg. by having control of the DNS records, more exactly the DNS server IP the client will use, which is a trivial DHCP setting to control) then the attacker can present any certificate and the client will accept it, it can then do the full authentication round-trip with the client, thus obtaining the SQL username and password used, then it can connect to the true server and forward back and forth all the communication, with a free look at all the content. The client will never know is being 'monitored'. This is the 'man-in-the-middle' attack.

To prevent the situation above, the client must remove the TrustServerCertificate=True from the connection string. Once this is done though, the certificate used by the server has to be trusted by the client, and this is when all the complications arise. If you are OK with a weaker setting on which you have an encrypted traffic but you understand that you may be subject to a man-in-the-middle attack and you are OK with it, then use the much simpler TrustServerCertificate=True setting. If not, then unfortunately you must really understand what you're doing and is not trivial. If the data is so important, then perhaps shelling out the moneys for a VeriSign, Thawte or GlobalSign (these being the 3 roots trusted by every Windows client) certificate for your server (~$500/year) is not so outlandish.

Solution 2:

"Personal" is a misleading name for the certificate store. When you are in the MMC, if you see a certificate that can be selected, you're probably ok. I recommend using a real certificate. This can be a certificate that is created in-house using Microsoft Certificate Server, or one that is purchased from a cert provider. I would not use a self-signed cert. The SQL Server instance service must also be restarted for it to take effect.

Some general tips:

If you enforce encryption on the client, all SQL communication on the client must use transport level encryption.

If you enforce encryption on the server, all SQL communication for that instance must use transport level encryption.

Regardless of the configuration that you choose (selective or enforced), your application still requires support for the various connection options. Such as the Encrypt connection keyword.

I have personally found that the SQL Native client provides more descriptive error messages than the built-in SQL client, but you can use/enforce encryption with either.

The documentation from Microsoft is a bit sketchy, but there are a couple of good articles:

Selectively using secure connection to SQL Server
http://blogs.msdn.com/b/sql_protocols/archive/2009/10/19/selectively-using-secure-connection-to-sql-server.aspx

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
http://support.microsoft.com/kb/316898

Using Connection String Keywords with SQL Server Native Client
http://msdn.microsoft.com/en-us/library/ms130822.aspx