Ssl – Certificates in SQL Server 2008

connectionencryptionsql serversslssl-certificate

I need to implement SSL for transmissions between my application and Sql Server 2008.

I am using Windows 7, Sql Server 2008, Sql Server Management Studio, and my application is written in c#.

I was trying to follow the MSDN page on creating certificates and this under 'Encrpyt for a specific client', but I got hopelessly confused. I need some baby steps to get further down the road to implementing encryption successfully.

First, I don't understand MMC. I see a lot of certificates in there… are these certificates that I should be using for my own encryption or are these being used for things that already exist? Another thing, I assume all these certificates are files are located on my local computer, so why is there a folder called 'Personal'?

Second, to avoid the above issue, I did a little experiment with a self-signed assembly. As shown in the MSDN link above, I used SQL executed in SSMS to create a self-signed certificate. Then I used the following connection string to connect:

 Data Source=myServer;Initial Catalog=myDatabase;User ID=myUser;Password=myPassword;Encrypt=True;TrustServerCertificate=True

It connected, worked. Then I deleted the certificate I'd just created and it still worked. Obviously it was never doing anything, but why not? How would I tell if it's actually "working"? I think I may be missing an intermediate step of (somehow?) getting the file off of SSMS and onto the client?

I don't know what I'm doing in the least bit, so any help, advice, comments, references you can give me are much appreciated.

Thank you in advance. 🙂

Best Answer

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.

Related Topic