RSS
 

Configure Firewall for MS SQL 2008 R2/Windows 2008 R2

25 Jun

This is a nice batch file to automatically configure Windows 2008 and Windows 2008 R2 firewall for MS SQL 2008 R2. Thanks to MrMMills1

@echo off
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 ***

 
6 Comments

Posted in IT

 

Tags: , , , , ,

Leave a Reply

 

 
 
 

  1. Mc.Sim

    October 5, 2010 at 1:12 am

    Hi.
    now that seems to be a typo:)
    ================
    netsh advfirewall firewall add rule name=”SQL Server (TCP 1432)” dir=in action=allow protocol=TCP localport=1433 profile=domain
    ================
    port 1432 -> 1433
    or so conceived?
    thanks for the script!

     

  2. wattsup

    October 5, 2010 at 12:57 pm

    Correct! 1433, I’ve updated the script. Thanks!

     

  3. Ken

    July 14, 2011 at 1:34 pm

    Is there a link to download script? I can’t seem to cut and paste.

     

  4. Jeremy Griffith

    November 2, 2011 at 8:39 pm

    Thanks for the script. If you want to copy directly from the post, you’ll need to replace the smart quotes with normal quotes.

     

  5. Steven Anderson

    January 9, 2012 at 7:15 pm

    Greetings Fellow hiker! I found your SQL server firewall script. What a great job. Unfortunately it still does not satisfy the requirements of SQL server cluster. You wouldnt be in the mood to extend it to include SQL server clusters would you. If I get to it before you I will be sure to cross post it to your site.

    hikenboot

     

  6. Steven Anderson

    January 10, 2012 at 1:19 pm

    This batch file as it stands does not work right. One needs to replace each quotation mark with a quotation mark from a normal keyboard. I think something is lost in the blog format. FYI