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.
Print Friendly, PDF & Email

Related Posts

Subscribe
Notify of
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Anonymous
October 24, 2011 8:40 am

Completely agree with the comments above, a glaring omission by Microsoft.

Worse still predefined SQL exceptions are still ominously absent in R2!!