Friday, June 1, 2012

Setup windows firewall for SQL Server

On Windows 2008 R2, the windows firewall will turn on by default which causes connection issure for the remote client, here is a script which can turn off the firewall for all SQL Server default port. if there is a named instance, the script should be modified based on the correct setting.

==============================================================
@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 Service
netsh advfirewall firewall add rule name="SQL Browser for Analysis Service" dir=in action=allow protocol=TCP localport=2382 profile=DOMAIN
echo Browser service for Analysis Service
netsh advfirewall firewall add rule name="SQL Browser for Analysis Service" dir=in action=allow protocol=TCP localport=2382 profile=DOMAIN
echo Analysis Service Default
netsh advfirewall firewall add rule name="Analysis Service" dir=in action=allow protocol=TCP localport=2383 profile=DOMAIN
echo HTTP/HTTPS for the 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

====================================================================




1 comment:

  1. Looking for help on the Windows firewall settings, but I don't know what should be changed for a named instance, based on the statement above, "if there is a named instance,
    the script should be modified based on the correct setting." I'm a beginner and any help would be appreciated.

    ReplyDelete