SQLStarters

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…

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.

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

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.

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.

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.

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.

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.

Finally give a good description and name to your fancy new rule before clicking 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.

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

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.

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.

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.

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.

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

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.

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.