SQLStarters

It has been couple weeks since my last post and as we’re going through the end of the year frenzy at the office, I find myself having little energy or time left after work to finish up my posts (I do have a couple of other posts waiting to be finished).

I previously wrote about different methods on how to secure the network traffic on your SQL Server. I mentioned there being two readily available tools that can accomplish this, the SSL and the IPSec. In this post, we’ll take a quick look at how the SSL is implemented on SQL Server.

But first, let us have a look at the unencrypted and unsecured connections. To do this we’ll fire up a network monitoring tool and look at what an unencrypted connections and queries look like. We’ll run a simple query against the instance we’re about to encrypt.

The network monitoring tool running in the background picks this up, as well as lot of other traffic happening on your server. Easiest way to find the packages is to filter by TCP port, which we know to be 1433. Then we’ll pick one packet and choose Follow TCP Stream, which gets all the packets involved in the conversation between your client software and the server.

It’s not as cool looking as 0’s and 1’s in Matrix and it’s not as pretty as it’s in your Query Tool, but it’s still perfectly readable. Now if we don’t want people to find out that your Research and Development group has an Engineering team we’ll need to encrypt it and we’ll do that by using SSL.

SSL stands for the Secure Sockets Layer and it’s a standard technology for securing network traffic between clients and servers, most often used with web and mail servers. To create a secure connection in this manner requires a certificate to be used, it can be requested from a certificate authority (CA) or you can use self-signed one. To acquire certificate from CA you need to pay for it, but it’s definitely more secure of the two options. To use self-signed certificate all you need is one of the tools available, such as MAKECERT or OPENSSL.

I’m not a certificate expert so I wont even try to go into depths of how they work, here are few links for those of you who are interested.

What is SSL?
SSL By Symantec, how it works?

The point of this post is to show you how to use SSL with your SQL Server. On a quick note: The most challenging thing, as far as I’ve discovered, was to actually create a certificate my SQL Server would accept. Took me couple of tries to get it correctly, as it appears to be really strict about the server names given in creation (I made my own certificate for the purpose of this post). Then there are possibly few other places you can go wrong, such as not having enough permissions to use the private key.

After having installed your certificate, double-click it to open and you should see something similar to this.

To make SQL Server use your certificate, you need to fire up your SQL Server Configuration Manager. Then go to SQL Server Network Configuration and choose Protocols for MSSQLSERVER. Right click it and choose Properties. On the first tab called Flags, choose the option: Force Encryption and set it to Yes.

Then go to next tab called Certificate. Click the drop down list and choose your certificate, which should then display it something similar to this.

At this point, you can click OK and have your SQL Server traffic encrypted. There are however two more options at the Advanced tab that might be some interest to you. The first one, Accepted NTLM SPNs allows you to define other Service Principal Names for which your clients are allowed to connect to.

The second option called Extended Protection gives you control on how secure you would like to be regarding your client connections. If set to Off (Default) the instance will accept connections from any client, it doesn’t matter if it’s protected or not. If set to Allowed connections coming from protected client OS’s are rejected if they are unprotected. The final option, Required, is the most secure as it will only allow protected connections from protected operating systems and rejecting everything else.

In mixed environments where you might have operating systems and client applications might not have support for Extended Protection, the safe choices are probably to use Off or Allowed.

One more note regarding the certificates, if you’re not planning to use it to anything else than encrypting your network traffic, you don’t need to acquire a certificate for it. If no certificate is specified, SQL Server will create one for you, but like all self-signed certificates it will only offer limited amount of protection (encryption).

And that’s pretty much how it’s done. Of course, there’s still the little matter of validating what you have done. This can be done in a few different ways, but I prefer to look at the actual network traffic itself first. For this purpose I usually use Wireshark, but I’ve been looking at Microsoft Network Monitor as well and it’s not a bad tool either.

Performing the same query than we did previously, filtering and choosing the correct packets and following the TCP stream we’ll be looking a quite a different conversation between clients and the server. Good luck trying to decipher that!

You can also look at the connections themselves from the SQL Server point of view. Remember that SSL is done in the higher levels of OSI model, which means that they’re visible to applications. Performing a quick query to SYS.DM_EXEC_CONNECTIONS also gives us a bit of a good information regarding encryption.

SELECT * FROM sys.dm_exec_connections

And now we’re done! Just remember, never do things on production you haven’t thoroughly tested elsewhere. It’s also worth noting that turning on the SSL encryption requires SQL Server restart.