Home > Microsoft > Microsoft SQL on Windows Server 2008 and Firewall Settings

Microsoft SQL on Windows Server 2008 and Firewall Settings

When installing SQL on windows Server 2008, the firewall is not automatically setup to allow access to the default SQL ports which are as follows:

SQL Server Ports
  1. Default instance port TCP 1433
  2. Dedicated Admin Connection port TCP 1434
  3. SQL Server Service Broker port TCP 4022
  4. Transact-SQL Debugger/RPC port TCP 135
Analysis Service Ports
  1. SSAS Default Instance port TCP 2383
  2. SQL Server Browser Service port TCP 2382
Miscellaneous Ports
  1. HTTP port TCP 80
  2. SSL port TCP 443
  3. SQL Server Browser Service‘s Port TCP 1434
  4. You also need to Allowing multicast broadcast response on UDP

Instead of adding each of these rules one by one you can create a batch file and run it in a cmd prompt.

To create the batch file, open notepad and copy the code below  and then past it into the notepad. (You can remove the lines for the ports you do not want to open.)

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

 

Save the notepad as OpenSqlServerPort.bat ensuring you select “All files” in the “Save as Type” selection box.

Now you can open cmd prompt. “Remember to run the command prompt as administrator if UAC is enabled.”

To do this, on Windows Server 2008 you can click the start button then type “cmd” in the search box and you should get cmd listed. Right click the cmd in the list and chose “Run as administrator”.

CMD Run as for windows 2008

When the cmd prompt is open, change directory to where you saved the file and then type OpenSqlServerPort.bat and press enter.

 

That’s it. Now you should be able to access the SQL server through the firewall.

Please see Microsoft KB968872 for more information.

http://support.microsoft.com/kb/968872

  1. No comments yet.
  1. No trackbacks yet.