IPSEC settings for the client

After setting up the SQL Server ready for encrypted connections, it’s time to do the same for the clients. This is basically the same process that we already did at the server end, but let’s go through it once more. Instead of inbound firewall rule, we’ll create an outbound rule (surprise!) and connection security rule.

Firewall outbound rule

We’ll create the outbound rule by editing the proper Group Policy Object, the one that’s reserved for your clients. Then we’ll browse down to Computer Configuration / Policies / Windows Settings / Security Settings and Windows Firewall with Advanced Security from where you’ll find a folder called the Outbound Rules, which you should right-click and choose New Rule…

Outbound rule

In the next dialog you’ll be asked to choose the type of the rule you wish to create. You could pick one from the pre-defined list but I tend to prefer using Custom rules. Choose it and click Next.

Client rule type

At the Program dialog choose All programs and click Next to proceed.

Choose client programs

The next dialog called Protocols and Ports, at the upper section for Protocol type and Local port you can choose the port and protocol which should be affected by the rule. I find it bit easier to just set the protocol , leave Local port on default (All ports) and then add the Remote port (which is the one on your server). This can be useful if you’re not sure about what port your clients use but know which one the SQL Server is listening. Finally click Next.

Client port and protocol

At the Scope dialog you can add the client IP range, or just leave it on default (Any IP address) which makes it apply to all clients that have your Group Policy. On the remote part add the IP address of your SQL Server(s). Then click Next.

Client IP addresses

The next dialog is called the Action, at the early implementation stages you might go with Allow the connection as this will allow both connections, secure and un-secure. This is useful as you can monitor connections to see if any clients have slipped past your notice and are not affected by the Group Policy and/or your IPSEC settings. If you choose option Allow the connection if it is secure, you’ll definitely hear about it if you’ve missed anything 🙂 After you’ve set the actions, click Next.

Client action

At the Computers dialog you can only allow connections to certain computers or create exceptions to your rule. You can also leave them empty and then click Next.

Client exceptions

Then we’ll get to choose the profiles to which this rules apply to. I’ll usually pick all of them, just to be certain and it shouldn’t really have any adverse effects one way or another. Then click Next.

Client profiles

Finally give a good description and name to your fancy new rule before clicking Finish!

Client outbound rule finish

After this, you’ll be ready to continue by creating the connection security rule.

Connection security rule

We’ll start by going back to Security Settings / Windows Firewall with Advanced Security and Connection Security Rules. Right-click it and choose New Rule.

Client connection security

For the sake of consistency, we’ll go with the Custom rule and then click Next.

Connection security rule type

Next we need to set up the endpoints, as we want every client to be affected we’ll leave the Endpoint 1 to default setting which is Any IP address and then add the IP address of our server(s) into Endpoint 2. Then click Next to proceed.

Client security connection rules

For setting up the requirements I’d go with Request authentication for inbound and outbound connections. This is again the first step in tightening the security of your network traffic, when you’re certain that all workstations are working as they should you can kick it up to Require…. Once you’re satisfied with your choise, click Next.

Connection security requirements

The next step is to determine what authentication method you’ll be using. Choose your preferred option, or options by going to Advanced settings. If you’re interested about the Advanced settings, check here. Then click Next to proceed.

Client connection security authentication

Next you need to choose Protocols and Ports. We’ll choose TCP as the protocol and for Endpoint 1 Port we choose All Ports. For Endpoint 2 we’ll set the port our SQL Server is listening. Then click Next to proceed.

Client connection security ports and protocols

Next we’ll choose the Profile which is affected by these settings. With the click of the Next button, we’re almost done!

Client profiles

Now all that is left, is to give a name and description to our shiny new policy! You also need to click Finish for the magic to actually happen.

Client connection security rule is now done!

The final steps

As both your servers and clients are now ready to use IPSEC, we can tighten the security even further. To do this you should first set up your firewall logging and monitor security associations to see if any clients are not affected by your policies. Once everything is golden, feel free to change authentication requests into requirements and only allow traffic that is secure.

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s