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?

3 Answers
3

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.

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!
–
SeanApr 23 '12 at 19:25

"...SQL Server data can be encrypted during transmission by using IPSec. IPSec is provided by the client and server operating systems and requires no SQL Server configuration. For information about IPSec, see your Windows or networking documentation."

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.
–
SeanApr 23 '12 at 18:07

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
–
fencepostApr 23 '12 at 18:52

fencepost - not correct. IPSec does not only operate as or via tunnels.
–
mfinniApr 23 '12 at 19:26

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."
–
mfinniApr 23 '12 at 19:29