4

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?

Sean
  • 183
  • 2
  • 6
  • 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. – JohnThePro Apr 23 '12 at 17:59
  • Contractual obligation, unfortunately. – Sean Apr 23 '12 at 18:19
  • @johnthepro Using IPSEC to encrypt all windows traffic has been a best practice since Windows 2000 – Jim B Apr 23 '12 at 18:38
  • Jim - I honestly haven't seen any companies where this was actually done (for the whole environment, anyway.) – mfinni Apr 23 '12 at 19:24
  • Best practice, but .... I don't know, I work with a lot of SMB environments, and this is rarely implemented. – JohnThePro Apr 23 '12 at 19:45
  • 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. – Joel E Salas Apr 23 '12 at 19:53
  • I never said it was accessible from the Internet. – Sean Apr 23 '12 at 20:05
  • @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) – Jim B Apr 23 '12 at 22:23
  • @JohnThePro Assume, for the purposes of this question, that both client and server are on the same local area network. – Ian Boyd Sep 16 '16 at 17:17

3 Answers3

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.

Jim B
  • 23,938
  • 4
  • 35
  • 58
  • 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 (http://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! – Sean Apr 23 '12 at 19:25
0

You don't. IPSEC is a network level encryption strategy that is handled by the operating system. It is not specific to an application, such as SQL.

Encrypting Connections to SQL Server
http://technet.microsoft.com/en-us/library/ms189067.aspx

"...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."

Greg Askew
  • 34,339
  • 3
  • 52
  • 81
  • 1
    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. – Sean Apr 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: http://www.microsoft.com/download/en/details.aspx?id=18254 . Also relevant? http://support.microsoft.com/kb/816514 – fencepost Apr 23 '12 at 18:52
  • fencepost - not correct. IPSec does not only operate as or via tunnels. – mfinni Apr 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." – mfinni Apr 23 '12 at 19:29
0

IP Sec needs to be configured on both the client and the server. Using SSL only requires configuring the SQL Server.

mrdenny
  • 27,074
  • 4
  • 40
  • 68