How to encrypt connections to SQL Server with IPSec?

Question

The SQL Server OLEDB provider can use SSL encrypted connections to SQL Server with an option in the connection string:

Use Encryption for Data
Specifies whether data should be encrypted before sending it over the network.
The valid values are "true" and "false". The default value is "false".

As Microsoft notes, that has a number of issues:

• it requires obtaining a valid SSL certificate
• it requires installing the SSL certificate on the server
• it requires altering the connection string
• it's not what i'm asking

IPSec

Fortunately, Microsoft suggests that IPSec can be used as an alternative:

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.

Because even though both the client and server are on the same Local Area Network:

We don't want anyone with WireShark, a hub, a PC in permiscuious mode, or a switch that can monitor traffic able to see the traffic.

The question is: how do you do it?

Research Effort is Immaterial

On the client machine, we want to configure a policy that requires IPSec connection to an SQL Server (e.g. port 1433). From within Windows Firewall with Advanced Security:

• Create a new outbound firewall rule

  

• for an IP port:

  

• for destination TCP port 1433

  

• Allow the connection, if it is secure

  

• Finish

  

The downside is that the client now cannot connect to the server:

Bonus Reading

• Superuser - Windows 7 - How to use IPSec (refers to tunnel mode)
• Technet: Encrypting Connections to SQL Server (refers users to use IPSec)
• MS Forums: IPSec to secure SQL Server Connection (accepted answer says he gave up trying to use IPsec)
• How do I encrypt SQL Server traffic with IPSEC? (accepted answer doesn't indicate how to encrypt SQL Server traffic with IPSec)
• Serverfault user Greg says you can't use IPSec to encrypt network traffic
• IPSec for LAN traffic: Basic considerations? (general discussion of encrypting LAN traffic)

Answer 1

You can't use 'allow this connection if it is secure' until you have a connection security rule in place. This needs to be defined on both systems and have matching settings, just like setting up a standard ipsec VPN tunnel.

Here is how to create one for this use case, step by step.

1. Open Windows Defender Firewall snapin (wf.msc)
2. Open the "Connection Security Rules" tab
3. Right Click on the center panel and select "New Rule"
4. Select the 'Server to Server' template.
5. Enter the local computer IP address in endpoint 1
6. Enter the remote computer IP address in endpoint 2.
7. As a general rule set the connection security rule to require authentication in both directions.
8. Select a method to authenticate this connection.
   • If both of your systems have access to a shared internal CA / kerberos that is usually the best option.
   • Failing that - especially true for DMZ to internal machines - a preshared key is the next best option. This is set under the advanced authentication settings.
   • Anything other then computer certificates is in the advanced dialog.
   • You probably only need to use the "first authentication" method here.

This process needs to be repeated on both of your hosts.

Answer 2

The only way I can see this as an option is to have a IPSec VPN server setup on your SQL server and have your client create the tunnel to that first then the traffic can flow. This is a messy solution but is what the documentation is referring to when it says that IPSec is provided by the client and server operating systems.

You would need to have a secondary network setup with IP's being issued by the VPN system and rebind your SQL server to that IP range, that way only people connected to the VPN can connect the SQL server.