Creating firewall rules for SQL server using Powershell

On Win2012/Powershell 3 there is a commandlet called “New-NetFirewallRule” that allows for scripted creation of firewall rules. This makes it a lot easier to get them rules right. I have previously used GPO to push this to my SQL servers, but sadly I have discovered that it does not always work. For some reason, servers don’t like to have their firewall rules pushed by GPO. This meant I had to check them every time anyway, so I just resorted to creating them manually. But now, thanks to the wonders of Powershell 3, maybe I won’t have to do that again Smilefjes

More information about the commandlet can be found here: http://technet.microsoft.com/en-us/library/jj554908.aspx

Sample code

This code creates rules to allow the SQL server browser (UDP 1434), the standard engine port for two instances (TCP 1433 and 1434) and the default port for AOAG endpoints (TCP 5022).

New-NetFirewallRule -DisplayName "MSSQL BROWSER UDP" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow
New-NetFirewallRule -DisplayName "MSSQL ENGINE TCP" -Direction Inbound -LocalPort 1433-1434 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "MSSQL AOAG EP TCP" -Direction Inbound -LocalPort 5022 -Protocol TCP -Action Allow

Author: DizzyBadger

SQL Server DBA, Cluster expert, Principal Analyst

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.