A while back I had a request to encrypt communications between an instance of SQL Server and the clients connecting to it. I knew that SQL Server had the ability to use SSL to encrypt communications, so this all seemed simple enough to do. The easy way, however, didn’t work for me because of some of the older protocols used did not support SSL encryption. So I was encouraged to look at some other options.
Outside of buying some fancy specialized hardware to handle all your encryption needs, there are two readily available methods for securing your SQL Server network traffic. There are of course some other options as well, but if you’re interested in using what the Windows has to offer, your options are to:
- Use the SSL encryption offered by SQL Server.
- Use IPSEC provided by Windows Firewall.
The first one, using SQL Server to do the SSL is by far the easiest method. If you’re not concerned about the man-in-middle type of attacks (you probably should be, though) you don’t even need to acquire a certificate for this. SQL Server can (and will) create one for you. This would be my first choice on how to go about it, it’s simple and quick to set up and requiring only little downtime when you restart your SQL Server instance. In the traditional OSI model the SSL is at the upper layers (Session Layer), just above the Transport Layer where the TCP protocol resides.
The second one, using IPSEC, takes a little more designing and testing before you can implement it, or to do it successfully at least. Using IPSEC requires setting up policies on both your clients and server, fortunately you don’t need to do it manually as these can be implemented through Group Policies in your Active Directory. In OSI model IPSEC resides in the lower layers (Network Layer), just below the Transport Layer.
OSI model
As these two methods reside in layers on both sides of the TCP protocol, they also work rather differently. The way SSL works is that it uses TCP on behalf of the higher, Application Layer protocols, such as HTTP. For an application to take advantage of the SSL the capability needs to be built into it, an example of this is Microsoft Internet Information Service (commonly known as the IIS) or the SQL Server.
IPSEC on the other hand is implemented on the Network Layer of the OSI model and this is what makes it a very powerful solution. Provided at the IP layer it can be used by all the protocols and applications on the higher layers while being transparent to them. The IPSEC isn’t really a single protocol, but collection of methods and protocols offering data protection, encryption and authentication among other things.
In coming weeks I’ll be making two more posts on this matter, one explaining how to implement SSL and another one describing on how to set up IPSEC on your network to provide security for your SQL Server network traffic.
Leave a Reply