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.
Completely agree with the comments above, a glaring omission by Microsoft.
Worse still predefined SQL exceptions are still ominously absent in R2!!