SQL 2012 Failover Cluster Pt. 8: Firewall

9-22-2013 9-10-20 PMI’m a big proponent of using the Windows firewall on all computers, be they clients or servers. It may not be perfect, but it’s better than nothing. Layered defenses are key in the enterprise, so protecting each host may save your bacon some day. Many programs automatically add Windows firewall rules, so it’s not a huge burden to enable.

If you have the Windows firewall enabled (which I strongly recommend), then you will need to allow both SQL servers to communicate with each other so that you can launch SQL Server Studio on either node and connect to the active node. If you don’t do this, you may get a timeout error. I like to make firewall rules as specific as possible, so I include the path to the program and require specific remote IP(s) that needs access. No Any-Any here!

For ease of installation, I’ve also added a line to the script that configures the SQL large pages trace flag. If you wish to use large pages and your VM has 8GB or more of RAM, you can leave the script as is. If you don’t want to use large pages, then just comment out the line. The SQL services will need to be restarted to take advantage of the added trace flag.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

1. Run the script on both nodes, using the IP address of the opposite node as the argument.

# Configures Windows Server firewall for SQL 2012
# Requires a single argument, the IP address of the other cluster node
# Usage: SQL-Firewall.ps1 10.10.10.10
$RemoteIP = $args[0]

New-NetFirewallRule -DisplayName “SQL Server (TCP-in)” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow -profile Domain -Program "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress $RemoteIP -description "Allows inbound Microsoft SQL connections."

New-NetFirewallRule -DisplayName “SQL Server Browser (UDP-in)” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow -profile Domain -Program "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress $RemoteIP -Description "Allows inbound Microsoft SQL browser connections."

# Configures trace flag for enabling SQL large pages
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg3 -value "-T834"

9-22-2013 8-44-13 AM
2. If all went well you will now have two new rules in the Windows Advanced Firewall, as shown below. Since the rule only allowed the partner node access, you will need to modify the Remote IP Address on the Scope tab to allow your application servers (such as vCenter) to access the SQL service. Again, I don’t like wide open firewall rules, so try and use IPs vice whole subnets or “any”.

9-22-2013 8-29-52 AM
9-22-2013 8-30-52 AM

3. Now on both nodes fire up the SQL Server Studio and connect to the cluster name (e.g. D001SQL03-DB) and verify it can connect. If you get a timeout error then your firewall rules are botched up. They take effect immediately, so no need to reboot.

Summary

Now that the firewall is configured on both nodes, next up we need to do things like configure TempDB, max memory, email alerting, and DB maintenance plans. So check out Part 9 here.

Scripting SQL Server Firewall Rules

On servers that are running Windows server 2008 or later, you can take advantage of the built-in two-way firewall. SQL server firewall rules can be pretty easily created through the GUI, but it can be a bit tedious. So I wrote a little batch file that asks you for an IP address and then opens the SQL port such that it only accepts connections from that IP address.

You can of course extend this to any other program just by modifying the switches, protocols, IPs, etc. It will error out if you don’t supply an IP address, so that you don’t get a meaningless rule. A new rule is created each time you run the command..it doesn’t update an existing rule. That is possible with the netsh command though.

@echo off
:: Configures Windows Server 2008/R2 firewall for SQL.
:: Requires a single argument, the IP address of the remote application server that requires SQL access.
:: Usage: SQL-Firewall.cmd

if [%1]==[] ; GOTO :ERROR
Echo Configuring Windows Advanced Firewall for SQL to listen on IP %1
netsh advfirewall firewall add rule name="SQL Server (TCP-in)" dir=in action=allow protocol=TCP Profile=domain localport=1433 program="D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" description="Allows inbound Microsoft SQL connections." remoteip=%1
Exit /B
:ERROR
Echo Please specify IP address.