IPSEC settings for the SQL Server

To set up IPSEC for a box running SQL Server starts with a simple step, by turning on your Windows Firewall with Advanced Security, if it’s not on already (which it definitely should be!).

After that you need to create two rules for your firewall. First the inbound rule, which allows the clients to connect to your server. And secondly a security rule, in which you define how the connections are authenticated and secured. You could do this directly from the firewall settings, but I prefer using Group Policies myself.

Firewall inbound rule

Once you have started editing your Group Policy for SQL Servers, you should head down to section called Computer Configuration / Policies / Windows Settings / Security Settings and Windows Firewall with Advanced Security in the tree. There you’ll find a Inbound Rules, which you should right-click and choose New Rule…

Creating inbound rule

In the next dialog called Rule Type you can choose the type of the rule you wish to create. I prefer using Custom rule myself, though you could just as easily go with Program or Port rule if you wish to. Click Next again.

Inbound rule type

In the following Program dialog choose All programs and click Next.

Inbound rule programs

Clicking next takes you to a dialog called Protocol and Ports. For SQL Server that’s running in default port, we’ll choose TCP and Specific Port 1433 as the Local port. We’ll leave Remote Ports to its default setting All Ports, meaning that connection from any port to our 1433 will be affected by this rule. Click Next again.

Inbound rule protocols and ports

For IP addresses, if you wish you can choose the one you’re SQL Server is using. I leave it to default setting (Any IP address), so in case I have to change the IP address at some point I don’t need to worry about forgetting to change my rules. Not that it’d happen, if you’ve got your setup fully documented, which I of course have 🙂 I also leave Any IP address chosen for the remote addresses. Click Next.

Inbound rule scope

The next dialog, Action, presents us with some choices we need to carefully consider. If you’re rolling out IPSEC for your whole organization and it’s more than few dozen computers, I’d go with Allow the connection in the transition phase. This allows both, secure and un-secure connections to your SQL Server. If you choose Allow the connection if it is secure, only clients that have secured connections are allowed into your SQL Server.

By allowing both types of connections you can monitor your environment to see if you have forgotten anyone without preventing them access to possibly business critical system. Click Next again, we’re almost done!

Inbound rule action

In the Profile dialog, choose to which profiles you wish to apply this rule. I tend to choose all three, just in case. Click Next.

Inbound rule profiles

And finally you get to name your new rule and give it a good description. After that, click Finish and you’re done with the first part!

Security connection rule

Continue editing your Group Policy, from Computer Configuration / Policies / Windows Settings / Security Settings and Windows Firewall with Advanced Security and choose right-click on the Connection Security Rules.

Server connection security

Once again, I prefer using Custom rule. Choose it and click Next to proceed.

Connection security rule type

For Endpoint 1, give the IP address of your SQL Server if you wish to. It’s not necessary as mentioned previously when setting up the inbound rule. Same principles also apply to Endpoint 2. After setting up your endpoint settings, click Next.

Connection security endpoints

At the Requirements dialog you’re once more presented with a choise that can either allow all users or just users who are authenticated. Like with the inbound rule, if you have hundreds of workstations connecting to your SQL Server it might be good idea to go with Request authentication option first to make sure that you’re not missing any clients. If you’re really bold about it, choose Require authentication for inbound and outbound, then click Next.

Connection security requirements

At the Authentication Method, you can choose the one you wish to use. I choose Advanced though, just so we can get to look at some more dialogs 🙂

Connection security authentication method

At the Customize Advanced Authentication Methods you can press Add at the First authentication methods (dialog on the left) to set up as many as you want. This is very handy, for example in the situation where you wish to use Computer or User account for authentication, but need to have connections from outside the domain.

For connections outside your domain, you could pick certificate option or even preshared key one (while it’s not recommended, really). For the sake of simplicity, I choose Computer, click Add and then OK to return to Authentication Method dialog. Here you can press Next to proceed.

Connection security advanced method 1
Security rule advanced methods 2

Next we’re asked to set protocols and ports to which the rule should apply. Here we’ll pick default SQL Server TCP port for the Endpoint 1. For Endpoint 2 we’ll leave it at default setting (All Ports). Then we’ll click Next to proceed

Connection security protocols and ports

We’ll need to choose the profiles we’d like to apply the rule to, once more I choose them all to be sure. Click Next.

Connection security profiles

Then, once more we give a proper name and description for our rule and click Finish.

Connection security finished!

And that’s it, now your box(es) running SQL Server are properly configured to take advantage of IPSEC. At my next post, we’ll be looking at how to set it up to the clients connecting to your SQL Server instances.

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.

1 thought on “IPSEC settings for the SQL Server”

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