Encrypted Data Connections to SQL Server 2000

We recently had a requirement from one of our web application customers to be able to access a copy of their data using ODBC.  They wanted to run some custom queries and reports directly against the database but needed a secure connection because the data is confidential.  Their data is stored in a Microsoft SQL Server 2000 database. 

Researching the web turned up many articles that were vague or overly complex.  Some said the root certificate had to be shared on the client computer and gave lengthy instructions on how to accomplish that.  Others said you had to use protocols other than TCP/IP.  I don't know what problem they were solving but it wasn't ours.

After a little experimentation, we determined that it is actually quite easy to get the SQL Server data encrypted using SSL while moving between server and client (and back again).  Of course, a security certificate on the server is a prerequisite.  We already had one installed for IIS and it worked as is - no parameter changes, no sharing root certificates, no nothing!  By the way, we use GoDaddy.com when we need to purchase a security certificate - they are inexpensive and easy to install.  To turn on encryption, connect to your database in Enterprise Manager, right-click on the SQL Server registration name and select Properties; it should look something like this.  Click on the Network Configuration button on the bottom of the dialog.  This will display the SQL Server Network Utility, similar to this.  You can also get to this utility screen by executing the utility directly, it is one of the programs included with SQL Server 2000.  Assuming that you are using TCP/IP as one of your protocols, merely check the box on the left that is labeled "Force protocol encryption".  Apply your change and then stop/restart SQL Server.  The data packets moving to and fro through port 1433 will now be encrypted.

But you should verify this on your own because it is probably pretty important that it is being done correctly.  To test the encryption, first create an ODBC connection on a client computer.  Make sure that you do NOT check "use strong encryption for data" as that is for something else (I don't know exactly what because it is not documented in the ODBC Help).  Test your ODBC connection. 

Now we need something that will transfer some data.  I used Microsoft Access (any version should do).  I linked to a table on SQL Server using the ODBC connection that I just created.  Opening the table verified that the data could be read but did not tell me if it had been encrypted on the way over from the server.  For that, I used Microsoft Network Monitor 3.1.  If you don't have it already, you can download it here for free.  You can, of course, use any other protocol sniffer that you like.  Using MS Network Monitor, transfer some data and look for the TDS packets.  These TDS packets contain the data coming and going from SQL Server.  If you look at the TDS packets with the monitor before you enable encryption, you will know exactly what you are looking at and will also know when that same data is encrypted during testing.

I think you will agree, that enabling SSL for SQL Server 2000 is surprisingly simple.