Just a small update here. This topic has been for the last 6 years, every year, in top 5 of most read posts in my site. I guess security never goes out of style!
A first post of 2014 and it sure took me awhile to write it up. I was hoping to return to this subject much sooner, however my work schedule has been just plain crazy. Just this week I’ve spent two nights migrating databases to new database clusters. The situation should fix itself in a couple of weeks though, with few bigger projects coming to completion.
But to return to the actual subject of securing SQL Server network traffic.. I previously wrote about using SSL for this purpose, a method that was quick and simple to implement. This was done in my Azure demo environment, which allowed me to take few shortcuts in the implementation. When dealing with production environments, you’ll naturally need to test, test and test it once more before actual implementation.
The second method I’ll be writing about is IPSEC, done with the components that are included with all current Microsoft Windows operating systems. If you’ve read my previous posts about the subject, you’ll remember the OSI model. SSL was done in the higher layers that were above the Protocol Layer and this required the applications to be able to take advantage of it. IPSEC on the other hand is done in the lower one, called the Network Layer. Being implemented there it is effectively transparent to applications operating at the higher layers.
As I’ve said previously the tools required are part of the Windows operating system, so no additional software is required to take advantage of IPSEC. The tools required to implement IPSEC solution are:
- Active Directory and Group Policies
- Windows Firewall with Advanced Security.
You could do it with just using the Windows Firewall but to use proper authentication methods and for having Group Policies for enabling it easily across all your workstations, the Active Directory is necessary. And since we’re dealing with components of the infrastructure we DBA’s rarely have access to in production, one very important requirement is the co-operation of the AD and/or workstation administrators. They are also the ones that should be involved with this project from the day 1 of planning phase, since they’re likely the ones doing the actual implementation.
There are several benefits when using IPSEC for network encryption. First of all your applications don’t need any changes to make use of it since it’s completely transparent to any application, being implemented in the lower layers of OSI model. It can also be implemented in multiple stages, where you start by allowing all connections but preferring encrypted ones and then step by step tighten the security of your network by eliminating unencrypted connections, thus making the transition easier to manage.
To keep this post reasonably short, I’ll be splitting the actual setting up the rules part into their own posts. They’ll be coming up shortly!