One of the best things Microsoft did with Windows Server 2008 and later is the built-in firewall. Unlike previous OS releases where the firewall was pretty much a joke, Microsoft started from scratch and came up with a very robust two-way firewall. SQL is one of the prime targets for hackers as databases can contain a plethora of juicy data like credit card numbers, social security numbers, and other personal data.
As part of my standard SQL 2008 R2 installation I run a script which only allows inbound SQL requests from specific remote IP addresses. Requests from any other machine in the world will be dropped. Depending on what SQL services and features you are using, it is likely the script will need some tweaking. But the script below opens the basic SQL port (1433) and SQL browser port (1434). Reporting services, analysis services, etc. will need unique rules to allow them to function. Be sure to change the path to point to where your SQL binaries are installed.
—–
@echo off
:: Configures Windows Server 2008/R2 firewall for SQL 2008 R2.
:: Version 1.1, 5 July, 2010
:: Requires one argument, the IP address of the remote server that requires SQL access.
:: Usage: SQL-Firewall.cmd 10.10.10.10
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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnsqlservr.exe” description=”Allows inbound Microsoft SQL connections.” remoteip=%1
netsh advfirewall firewall add rule name=”SQL Server Browser (TCP-in)” dir=in action=allow protocol=TCP Profile=domain localport=1434 program=”D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnBinnsqlservr.exe” description=”Allows inbound Microsoft SQL browser connections.” remoteip=%1
Exit /B
:ERROR
Echo Please specify IP address.
——
