I’ve been asked the question many times, and you probably have seen it before …
The “warning” message that displays during a SQL Server installation and warns you about “Firewall” configurations.
To optimize for a minimal footprint and maximum security, configuring the right firewall settings might be a bit hard for some of us,
or even more might take some “time” to configure using Windows Advanced Firewall on Windows Server 2008 R2.
While there is quet some detailed descriptive information in the Configuring the Windows Firewall for SQL Server Access Article,
sometimes it might be as simple as “get me a script that I can modify and does it for me”.
Well here it is .. create a .CMD file with good old notepad and add the following batch to it.
Note that when executing the script you need to run it under administrative privileges.
@echo This scripts sets the default firewall configurations for SQL Server components
echo Setting the core components for a database instance
echo Default Instance
echo Dedicated Admin Connection
echo SQL Browser Service
echo Setting the core firewall rules for database mirroring, service broker, TSQL Debugger, Analysis services, Reporting Services
echo Mirroring EndPoint – CHANGE PORT NUMBER AS NEEDED depending on which role
echo Service Broker
echo Enable TSQL Debugger (uses RPC)
echo Browser service for Analysis Services
echo Analysis services Default Instance
echo HTTP/HTTPS for reporting services
Of course as security best practices you would only enable the ports that you really need, which could be as simple as putting a single REM statement.