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…
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.
In the following Program dialog choose All programs and click Next.
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.
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.
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!
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.
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.
Once again, I prefer using Custom rule. Choose it and click Next to proceed.
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.
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.
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 🙂
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.
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
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.
Then, once more we give a proper name and description for our rule and click Finish.
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.
Leave a Reply