How do I encrypt SQL Server traffic with IPSEC?

Question

I've found all sorts of documentation on what IPSEC is and how to (in very non-specific ways) configure it, but I can't seem to configure a Windows Firewall With Advanced Security rule that both A) requires all SQL Server traffic to be encrypted, and B) works.

As an exercise, I created an inbound rule that allows all traffic the default MSSQLSERVER service when it is connected to the domain, and tested that I can connect to the SQL Server instance with that rule.

When I change the Action from "Allow the connection" to "Allow the connection if it is secure", using the "Require the connections to be encrypted" option, I can no longer establish a connection. I can't seem to find any information on how to instruct the client to use that encrypted connection, or whether there are other prerequisites that must be met. I also can't seem to find much on using IPSEC with SQL Server in general.

I know how to encrypt SQL Server connections via SSL, but the primary application that uses this server will not connect using SSL until some future release.

How do I get the client operating system to connect using IPSEC from the Windows Firewall with Advanced Security settings in Windows 7?

Solution

Here are the 2 papers you need to read in order to determine the best route you want to go in order to encrypt your server traffic. The first route is server isolation,which is setting up secure communications between specfic domain hosts. The second is domain isolation which is slightly more relaxed in that the only real requirement for communications is that the hosts all be domain members (note these are simplified explanations). These papers all seem to be writen for the vista/2008 timeframe but the concepts still apply. To get to the settings use the windows firewall control panel and choose conenction security rules -> new rule. Look here for more details on connection security rules.

Comments

[JohnThePro]

That's a lot of overhead, why do you need this? Your SQL Server shouldn't be in a place where its accessible by the net anyways.

[Sean]

Contractual obligation, unfortunately.

[mfinni]

Jim - I honestly haven't seen any companies where this was actually done (for the whole environment, anyway.)

[JohnThePro]

Best practice, but .... I don't know, I work with a lot of SMB environments, and this is rarely implemented.

[Joel E Salas]

This is an extraordinarily bad idea. PCI-DSS regulations (depending on how much customer information you possess) stipulate that database servers should not be directly accessible from the Internet. Please try to use a go-between like OpenVPN or the like.

[Sean]

I never said it was accessible from the Internet.

[raja]

@mfinni there are lots of documented best practices folks don't do (and there are lots of folks doing it). in SMBs there is usually no real excuse not to add it to group policy. In larger enterprise this was usually vetoed by the networking teams because they could no longer inspect traffic. Most modern network tools now know how to handle ipsec traffic (if that group needs to decrypt)

[Mark]

@JohnThePro Assume, for the purposes of this question, that both client and server are on the same local area network.

[Sean]

I'm aware that the operating system handles it. I just don't know how to instruct the operating system on the client side to do so.

[fencepost]

You need to have an IPSEC VPN connection to the network that the SQL server is on. If this is within the same network I'm not sure how you'd go about doing it without causing headaches - I think addressing in that situation could get very ugly and confusing very fast. This article is old, but may be relevant: microsoft.com/download/en/details.aspx?id=18254 . Also relevant? support.microsoft.com/kb/816514

[Sean]

Thanks Jim. I had read the document on connection security rules, but didn't grok that this was the piece I was missing until watching this tutorial on doing the same thing for telnet (technet.microsoft.com/en-us/edge/Video/ff711601). Once I set up a CSR on both the server and client, the firewall rules I had configured began working to encrypt the traffic. Problem solved!

[mfinni]

fencepost - not correct. IPSec does not only operate as or via tunnels.

[mfinni]

See Page 31 of the first document you linked : "Windows IPSec supports both IPSec tunnel mode and IPSec transport mode as an option in the rule. IPSec tunnel mode rule configuration is very different from IPSec transport mode rule configuration. ... Because the scenario in this paper describes only IPSec transport mode, the filters in this paper are referred to as IPSec transport mode filters."