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:

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.

2 Answers
2

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.

Thanks again, Remus - it explains a lot that the client is accepting any certificate that the server throws out. I've noticed that none of the certificates that show up in MMC for my server show up in SSMS under mydatabase->security->certificates, but making self-signed ones you can make multiple. How can you specify which certificate to use, either from the client or the server?
–
BrandiJun 9 '10 at 14:40

"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: