SQL 2012 Failover Cluster Pt. 8: Firewall

9-22-2013 9-10-20 PM I’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.

Print Friendly, PDF & Email

Related Posts

Subscribe
Notify of
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mark
September 6, 2014 2:50 pm

FYI, SQL Browser listens on UDP 1434, not TCP. Ran into that issue while configuring Windows Firewall for a named instance. Apart from that, great series, thank you.

June 16, 2018 2:51 am

The work of dynamic data masking is to protect personally identifiable data. Dynamic data masking does not require any additional server resources.