How To: Open Required Firewall Ports for SQL, for ConfigMgr 2012 Installation

During the installation of ConfigMgr 2012, you may receive a prerequisite check error stating that you need to open required SQL ports on the Windows firewall. In our environment, we have the Window Firewall service disabled. Unfortunately, the prerequisite checker still  requires these ports to be opened. So the below script will re-enable and start the Windows Firewall service, make the required Firewall port changes, and then stop and disable the firewall service. If you just want to make the required firewall changes, simply remove the lines that begin with “sc”.

Paste the following lines into a .bat file:

rem ******************code start******************************

@echo off
sc config MpsSvc start= demand
sc start MpsSvc

echo. *** OPENING SQL SERVER PORTS IN THE FIREWALL ***
echo. source “http://rperreaux.spaces.live.com/Blog/cns!5D7BD18D324CBEEF!729.entry?wa=wsignin1.0&;;sa=289774293″
echo.
echo. Note for Named Instances – SQL mirroring – Dynamic Ports and the firewall
echo. see http://msdn.microsoft.com/en-us/library/cc646023(v=SQL.100).aspx#BKMK_programs
echo. You will need to open firewall ports for your mirroring endpoints and possibly dynamic ports
echo.
echo.
echo. Opening SQL Server TCP 1433
netsh advfirewall firewall add rule name=”SQL Server (TCP 1433)” dir=in action=allow protocol=TCP localport=1433 profile=domain
echo.
echo. Opening SQL Admin Connection TCP 1434
netsh advfirewall firewall add rule name=”SQL Admin Connection (TCP 1434)” dir=in action=allow protocol=TCP localport=1434 profile=domain
echo.
echo. Opening SQL Service Broker TCP 4022
netsh advfirewall firewall add rule name=”SQL Service Broker (TCP 4022)” dir=in action=allow protocol=TCP localport=4022 profile=domain
echo.
echo. Port 135
netsh advfirewall firewall add rule name=”SQL Debugger/RPC (TCP 135)” dir=in action=allow protocol=TCP localport=135 profile=domain
echo.
echo. Opening SQL Browser UDP 1434
netsh advfirewall firewall add rule name=”SQL Browser (UDP 1434)” dir=in action=allow protocol=UDP localport=1434 profile=domain
echo.
echo. Opening Analysis Services TCP 2383
netsh advfirewall firewall add rule name=”Analysis Services (TCP 2383)” dir=in action=allow protocol=TCP localport=2383 profile=domain
echo.
echo. Opening SQL Browser TCP 2382
netsh advfirewall firewall add rule name=”SQL Browser (TCP 2382)” dir=in action=allow protocol=TCP localport=2382 profile=domain
echo.
echo. ***Done ***

sc stop MpsSvc
sc config MpsSvc start= disabled

rem ******************code end******************************

Greg

ramseyg@hotmail.com

About Greg Ramsey
Greg Ramsey is a systems engineer specializing in global systems management for Dell IT. He has a B.S. in Computer Sciences and Engineering from the Ohio State University and is a Microsoft Most Valuable Professional (MVP) for Microsoft System Center Configuration Manager. Greg coauthored SMS 2003 Recipes: A Problem-Solution Approach (Apress, 2006), Microsoft System Center Configuration Manager Unleashed (Sams, 2009), Microsoft System Center 2012 Configuration Manager Unleashed (Sams, 2012), and Microsoft System Center 2012 Configuration Manager: Administration Cookbook (Packt Publishing, 2012), and Microsoft System Center 2012 Configuration Manager R2 Unleashed (Sams, 2012). Greg is cofounder of the Ohio SMS Users Group, and the Central Texas Systems Management User Group.

3 Responses to How To: Open Required Firewall Ports for SQL, for ConfigMgr 2012 Installation

  1. kim Oppalfens says:

    Any particular reason you call your rule sql server tcp 1432, when you actually open port 1433 🙂
    netsh advfirewall firewall add rule name=”SQL Server (TCP 1432)” dir=in action=allow protocol=TCP localport=1433 profile=domain
    echo.

  2. Thank You, this is exactly what I’m looking for. Go Buckeyes!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: