SQL Server Reference Guide

The best security method for a database system is to limit its exposure, removing it completely from the network. Of course, for most of the applications you need to run this isn't an option. Most database server systems are connected directly to the corporate network, if not the network at large. This isn't the most secure operation available, of course. A better approach is to connect the database server only to the local network, and even better if you limit its connection to a middle tier. Although this isn't perfect security, it allows a greater level of control over connections.

Sometimes that isn't possible. You simply have to connect the server to the full campus network, or even worse, directly to the Internet. Some applications, especially those distributed applications for the sales force or in a global environment, require this kind of access.

Although SQL Server is quite secure (especially SQL Server 2005) when you follow the instructions from Microsoft and those I've outlined in this guide, there are times when you need to take the security level higher, especially if you have these more open connections.

In this tutorial I'll explain one of the uses for encryption — which means to "hide" or "bury" a secret. Although SQL Server can encrypt data, in this case I'll explain how to encrypt the communications between the server and the client machines across the network. I'll use SQL Server 2005 for this demonstration, which actually has better security than SQL Server 2000.

Keep in mind that you should really need to encrypt data or connections to go to all this trouble. Anything you add to a connection makes it slightly slower, and if you lose or corrupt the encryption mechanism (keys or certificates, or both), you're pretty much out of luck. There's no magic key, or backdoor, that will get your data back or allow you to transfer encrypted data without it. This brings us up to a very important warning I need to give to you:

Here's the process we're going to follow, on that test server:

1. Create and export a certificate
2. Set up SQL Server to use the certificate
3. Import the certificate on the client system
4. Set up encrypted connections between the two systems

Create and Export a Certificate

A certificate is actually just a long string of numbers used to scramble data. It's actually a bit more complicated than that, because it includes information like the source of the certificate, its level, and how long it is valid for.

Normally you get a certificate from a Certificate Authority, or CA. Several commercial Certificate Authorities will sell you a certificate. But why use a third party at all? Why not just generate your own certificate?

In fact, we will generate and use our own certificate, but that's normally a bad idea. Let's take a look at this in more concrete terms. Let's assume that someone shows up at your house and asks to come in and take some personal data that belongs to you. You don't know this person, so you might hesitate to do that. He tells you, "but I know your address, and I know the secret name your family uses for your dog." Even though this person knew how to find you and knows some information you think is private, you're still probably not going to let him in. But In fact, that's what you do every time someone uses a simple password to access your application. All they need is the TCP/IP address of your server and a password and they are free to access your data.

But assume that your mother calls you one day and says "I'm sending a man named Jon over to your house to pick up some information I need from your bank." Someone shows up at your door (knows your address), tells you his name is Jon (knows a secret between you and your mother), and you let him in to get the paperwork. The difference between the first encounter and the second is that this time someone you trusted told you it was alright to let him in. That's the same purpose as purchasing a certificate from a CA. Each party is known by a third and that authority is trusted by both.

We're going to do something like that on SQL Server. Actually, the SQL Server Native Client in 2005 always encrypts logins — it's just part of the protocol. That's why it's inherently more secure than SQL Server 2000. What we're working on is the data encryption once the connection is made. It's sort of like giving that person that shows up at the door some personal data from your house, but it's written in a strong code in case he loses it on the train. That way no one else can read it. The certificate is the "key to the code" that lets the person (the server and client, in this case) read the data.

First we're going to check and export a certificate, so that we can use it both in SQL Server and on the remote connections. Click on the Start button and type MMC and press Return.

Figure 277

Then click File | Add/Remove Snap-in..., and click the Add button that shows up on that panel. Then find and click Certificates from the menu, and then the Add button again.

Figure 278

You'll be asked a question as to which kind of Certificates you want to manage. Select Computer Account and then click Next, and then select the Local Computer when asked. Now you're dropped back in the Snap-in panel again.

Now double-click the Certificates (Local Computer) | Personal | Certificates icons in the left side of the MMC Snap-in.

Figure 279

On my Windows 2003 Server system, a certificate has automatically been generated for me. It only lasts a day, but that's good enough for this demonstration.

There are some other caveats here. SQL Server requires certain settings for a certificate, so you'll need to read further in SQL Server Books Online about the requirements for the certificate options. Look up "Encryption" in Books Online for a complete rundown of what the requirements are.

Next, right-click the name of the certificate and then select All Tasks | Export... from the menu that appears. A wizard will guide you through exporting the certificate to a file. When you're asked about the file format, select the first option. Name the certificate and keep that file location handy. You'll want to delete that file when you're through with this exercise. When the wizard finishes you can close the panel and the MMC. If you want to revisit this process, you can save the settings to open them up again; just note what you call it and where you save that file. Double-clicking that file again will open up the Certificates Snap-in for you.

Set up SQL Server to use the certificate

Now we need to tell the server to user encrypted communications. Still on the test server (you ARE doing this on a TEST server, right?) open the SQL Server Configuration Manager from the Windows Start button | All Programs item. It's in the Configuration Tools submenu.

We're going to make two changes here. Double-click the SQL Server 2005 Network Configuration item, and then right-click the Protocols for (your instance name here). Click Properties from the menu that pops up. Once you do that, you'll see a panel similar to this one:

Figure 280

In the Flags panel, set the Force Encryption setting to Yes. After you restart the service, no one will be able to connect unless they use the certificate you exported. Now let's click the Certificate tab, and set that to the certificate name we exported earlier. Pull down the certificate name and then click the OK button.

Figure 281

You'll get a message that you have to stop and start the service for everything to take effect. You can do that now, by using the same tool you're already in. Just click the SQL Server 2005 Services item on the left and use the Restart icon in the button bar when you're on the SQL Server object.

Import the certificate on the client system

You'll need to copy the certificate we created earlier to the workstation you're going to connect from. Once again, click on the Start button, type MMC, and follow the same process we did earlier on the server – with one exception.

This time, navigate to exactly the same location in the Certificates MMC that we did on the server earlier, but this time, you may not have any certificates in the Personal area. Right-click the word Certificates underneath the Personal item, and select Import from the menu that appears. Just follow the defaults here, and point at the file you exported from the server.

Once you're done, you can close the MMC, once again saving it if you want to repeat the process.

Set up encrypted connections between the two systems

Now we have the certificate on the server, and the server is set to only listen to encrypted connections. We also have the same certificate installed on the workstation. There's only one step left: we need to tell the client application that we want to encrypt the transmissions.

We can do that for the applications that use the SQL Native Client communications by opening the SQL Server Configuration Manager tool on the client workstation.

In the Configuration Manager tool, right-click the SQL Native Client Configuration object and select Properties from the menu that appears. In the Flags panel change the Force Protocol setting to Yes. Now anything that uses the SQL Server Native Client will use encryption — for data. There are also settings in code that your developers can use to set an encryption call, and the SQL Server Management Studio tool also has an Options button that allows you to encrypt the connection.

Finally we need to put everything back the way it was. If you're using a virtual machine, as I am, you can just reset the image back to what it was when you started. If you're using a physical machine, you can just return to the Configuration Manager and change the settings for the connections back to allow unencrypted connections. Do the same on the workstation client component and you're back to normal. Also, remember to delete the certificate file from all the locations where you have it!

Informit Articles and Sample Chapters

As always, there are some fantastic security references right here on InformIT. You can read one of them on Secure Sockets Layer (SSL) here.

Online Resources

Those "extra" settings I told you about for certificates that SQL Server 2005 requires are described here.

There's more about the various settings I described between the server and the client here.