Feb 3

Written by: Thomas Hundley
2/3/2008 4:12 PM

 So, I've had limited time to work on my encyrption problem.  The reason I wanted to decrypt my data in my business object was to avoid protected data being sent across the wire in clear text.  Once I have time, I'm still going to work some code to do that, but here's the thing.  Who the hell knew you could create a SQL connection using SSL?!  That's about a million times easier and it solves my problem.

 

Ever click the Options button on Management Studio when connecting to a server?  Neither did I. Well, there is an option to encrypt connection.  Doh.  In your connection string, it's a simple as using "encrypt=true" in your connection string.  Alright, well not quite that simple, but close.

Stand-alone servers are easy, while clusters are a little more involved.  Here is a high level review of what you need to do:

• Install a Certificate from a CA on your network for Server Authentication.
• Make sure the CA you a getting the certificate from is in your Trusted Authorities.  Make sure the certificate is trusted and then the certificate chain is installed.
• On stand-alone servers, use the server name as the name for the certificate.  Your server name in your connection string must match the name in the certificate.
• On clusters, use the cluster name as the name for the certificate.  There are a few more things you need to do for clusters.  I'll update this thread with my notes once I get back to my client (too lazy to look them up right now).  Make sure you install the same certificate on your cluster nodes too- don't issue multiple certs.
• Make sure the CA is in your trusted CA's on the server you're using to connect to SQL Server 2005.

Badda boom, badda bing.  You can now connect to your SQL server with a secure connection. 

Tom Hundley
Elegant Software Solutions

Tags: