ILIKESQL - Dandy Weyn's Blog

A query walked into a bar ...saw 2 tables and asked .. "Can I join you?"

Firewall Configurations for SQL Server

Firewall Configurations for SQL Server

  • Comments 3
  • Likes

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 off
@echo This scripts sets the default firewall configurations for SQL Server components
echo.
echo Setting the core components for a database instance

echo Default Instance
netsh advfirewall firewall add rule name="SQLServer" dir=in action=allow protocol=TCP localport=1433 profile=DOMAIN

echo Dedicated Admin Connection
netsh advfirewall firewall add rule name="SQL DAC" dir=in action=allow protocol=TCP localport=1434 profile=DOMAIN

echo SQL Browser Service
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434 profile=DOMAIN

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
netsh advfirewall firewall add rule name="Mirroring EndPoint" dir=in action=allow protocol=TCP localport=5022 profile=DOMAIN

echo Service Broker
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022 profile=DOMAIN

echo Enable TSQL Debugger (uses RPC)
netsh advfirewall firewall add rule name="T-SQL Debugger" dir=in action=allow protocol=TCP localport=135 profile=DOMAIN

echo Browser service for Analysis Services
netsh advfirewall firewall add rule name="SQL Browser for Analysis Services" dir=in action=allow protocol=TCP localport=2382 profile=DOMAIN

echo Analysis services Default Instance
netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383 profile=DOMAIN

echo HTTP/HTTPS for reporting services
netsh advfirewall firewall add rule name="HTTP Reporting Services" dir=in action=allow protocol=TCP localport=80 profile=DOMAIN
netsh advfirewall firewall add rule name="HTTPS Reporting Services" dir=in action=allow protocol=TCP localport=443 profile=DOMAIN


 

 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.

Enjoy!

 

Attachment: FirewallScriptforSQL.txt
Comments
  • Very nice! This will make it easier to convince my clients to leave their Windows Server Firewalls running!

  • muchisimas gracias lo buscaba mucho, lo malo que al instalar me sigue saliendo advertencia en el firewall de windows

  • si me ayudan mi correo es harry.orochi@gmail.com

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment