2

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:

enter image description here

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

    enter image description here

  • for an IP port:

    enter image description here

  • for destination TCP port 1433

    enter image description here

  • Allow the connection, if it is secure

    enter image description here

  • Finish

    enter image description here

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

enter image description here

Bonus Reading

  • What have you tried? The QA you linked to has pointers to vendor docs, and a web search for "Windows Server 2012 IPsec" returns many results with useful information. – EEAA Sep 16 '16 at 15:42
  • After you require the secure connection you also have to,actually set up a IPSec association. And btw. I doubt it is much less work than tls self signed cert. – eckes Oct 13 '17 at 0:10
2
+25

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.

  1. Create a connection security rule using the 'Server to Server' template.
  2. As a general rule set the connection security rule to require authentication in both directions.
  3. Select a method to authenticate this connection. If both of your systems have access to a shared internal CA it is usually the best option. Failing that - especially DMZ to internal machines - a preshared key is the next best option. This is set under the advanced authentication settings.
  4. Be sure to include the IP addresses of both servers in the connection settings.
0

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.

  • Windows can do peer to peer IPSec just fine (Server to Server Template), not much to set up besides the IPSec config – eckes Oct 13 '17 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.