How to use a script to programmatically open ports for SQL Server to use on systems that are running Windows XP Service Pack 2 (839980)

MORE INFORMATION

A script that you can use to open all ports

• This script should only be run on computers that are running Windows XP Service Pack 2.
• This script has additional options for enabling Named Pipes and for enabling Multiprotocol (RPC).
• This script enables Multiprotocol (RPC) only over Named Pipes and only opens port 445.
• This script does not provide functionality to specify scope.

1. Start Notepad.
2. Copy and paste the following code into Notepad:
   

   echo off
   
   if "%1"=="-np" goto HandleNp
   if "%1"=="-rpc" goto HandleRpc
   if "%1"=="-tcp" goto HandleTcp
   if "%1"=="-browser" goto HandleBrowser
   
   rem Usage
   :Usage
   
   echo "Usage: setupSqlServerPortAll -[np | rpc | tcp | browser] -port [portnum] -[enable | disable]
   echo "-np : Setup SQLServer to listen on Named Pipe connections for local subnet only"
   echo "-rpc : Setup SQLServer to listen on RPC multiprotocol for local subnet only"
   echo "-tcp : Setup SQLServer to listen on TCP connections for local subnet only"
   echo "       Must specify a port if -tcp option is chosen."
   echo "-browser :  Setup SQLServer to provide SSRP service to support named instances"
   echo "-port : Applies only for tcp"
   echo " One of the following options MUST be specified"
   echo "-enable: Enables a port"
   echo "-disable: Disables a port"
   
   goto Exit
   
   :HandleTcp
   echo %2
   if "%2"=="-port" goto cont
   goto Usage
   :cont
   if "%3"=="" goto Usage
   if "%4"=="-enable" goto EnableTcp
   if "%4"=="-disable" goto DisableTcp
   goto Usage
   
   :EnableTcp
   echo "Enabling SQLServer tcp access for port %3 local subnet only"
   netsh firewall set portopening tcp %3 SQL%3 ENABLE subnet
   goto Exit
   
   :DisableTcp
   echo Disabling SQLServer tcp access for port %3 local subnet only"
   netsh firewall set portopening tcp %3 SQL%3 disable subnet 
   goto Exit
   
   :HandleNp
   if "%2"=="-enable" goto EnableNp
   if "%2"=="-disable" goto DisableNp
   goto Usage
   
   :EnableNp
   echo "Enabling SQLServer named pipe access for local subnet only"
   netsh firewall set portopening tcp 445 SQLNP ENABLE subnet
   goto Exit
   
   :DisableNp
   echo Disabling SQLServer named pipe access for local subnet only"
   netsh firewall set portopening tcp 445 SQLNP DISABLE subnet
   goto Exit
   
   :HandleRpc
   if "%2"=="-enable" goto EnableRpc
   if "%2"=="-disable" goto DisableRpc
   goto Usage
   
   :EnableRpc
   echo "Enabling SQLServer multiprotocol access for local subnet only"
   netsh firewall set portopening tcp 445 SQLNP enable subnet
   goto Exit
   
   :DisableRpc
   echo Disabling SQLServer multiprotocol access for local subnet only"
   netsh firewall set portopening tcp 445 SQLNP disable subnet
   goto Exit
   
   :HandleBrowser
   if "%2"=="-enable" goto EnableBrowser
   if "%2"=="-disable" goto DisableBrowser
   goto Usage
   
   :EnableBrowser
   echo "Enabling SQLServer SSRP service for local subnet only"
   netsh firewall set portopening udp 1434 SQLBrowser enable subnet
   goto Exit
   
   :DisableBrowser
   echo "Enabling SQLServer SSRP service for local subnet only"
   netsh firewall set portopening udp 1434 SQLBrowser disable subnet
   goto Exit
   
   :Exit
   endlocal
   
   

3. Save the file as a .txt file, and name the file ConfigSQLPorts.txt.
4. Rename the ConfigSQLPorts.txt file to ConfigSQLPorts.bat.

1. Click Start, click Run, type cmd, and then click OK.
2. In the command window, use the "cd" command to change folders until you are in the same folder that the ConfigSQLPorts.bat file is saved in. For example, if the ConfigSQLPorts.bat file is saved in C:\Myfiles, you would type CD myfiles at the command prompt, and then press ENTER. This will change your folder to C:\Myfiles.
3. To run the ConfigSQLPorts.bat script, type ConfigSQLPorts.bat at the command prompt, and then press ENTER.

A script that you can use to open only TCP/IP ports

1. Start Notepad.
2. Copy and paste the following code into Notepad:
   
   

   echo off
   setlocal
   
   if "%1"=="-port" goto HandleTcp
   
   rem Usage
   :Usage
   
   echo "Usage: setupSqlServerPort -port [portnum] -[enable | disable] [ALL | SUBNET]"
   echo -port : Specifies the port to be enabled or disabled. Port is not optional.
   echo -enable: Enables a port
   echo -enable ALL:  enables access for ALL
   echo -enable SUBNET:  enables access for SUBNET
   echo -disable: Disables a port
   echo  one of -enable or -disable must be specified
   echo  the default scope is SUBNET only
   
   goto Exit
   
   :HandleTcp
   if "%2"=="" goto Usage
   if "%3"=="-enable" goto EnableTcp
   if "%3"=="-disable" goto DisableTcp
   goto Usage
   
   
   :EnableTcp
   set SCOPE="%4"
   if "%4"=="ALL" echo "Enabling SQLServer tcp access for port %2 ALL access" 
   if "%4"=="SUBNET" echo "Enabling SQLServer tcp access for port %2 subnet only access"
   if "%4"=="" set SCOPE="SUBNET"
   
   netsh firewall set portopening tcp %2 SQL_PORT_%2 ENABLE %SCOPE%
   goto Exit
   
   :DisableTcp
   echo Disabling SQLServer tcp access for port %2"
   netsh firewall set portopening tcp %2 SQL_PORT_%2 disable 
   goto Exit
   
   :Exit
   endlocal
   
   

3. Save the file as a .txt file, and name the file SetupSqlServerPort.txt.
4. Rename the SetupSqlServerPort.txt file to SetupSqlServerPort.bat.

1. Click Start, click Run, type cmd, and then click OK.
2. In the command window, use the "cd" command to change folders until you are in the same folder that the SetupSqlServerPort.bat file is saved in. For example, if the SetupSqlServerPort.bat file is saved in C:\Myfiles, you would type CD myfiles at the command prompt, and then press ENTER. This will change your folder to C:\Myfiles.
3. To run the SetupSqlServerPort.bat script, at the command prompt type setupSqlServerPort.bat, and then press ENTER.