Guest Author: Joan Resnick Ehrlich

With the SQL Server Database Engine installed, I needed to open the necessary ports in the Windows Firewall and enable the TCPIP protocol in SQL Server in order to permit network communication to SQL Server.

Opening Ports

This can be done before or after installation, through the Windows Firewall UI (User Interface, also referred to as GUI or Graphical User Interface) or command prompt. I found a comprehensive script posted by various folks who referred to this Microsoft KB: http://support.microsoft.com/kb/968872/en-us. The commands completed successfully, albeit with a message that the “netsh firewall” syntax is deprecated in Windows Server 2008 R2, the new command being “netsh advfirewall firewall”. The command reference can be found on TechNet here: Netsh AdvFirewall Firewall Commands. Netsh stands for “Network Shell”.

To practice my command line skills, I adjusted the script to use the new command syntax. The only command in the original script I could not figure out is “allow multicast broadcast response on UDP”.

--- To enable SQL Server default instance Port 1433 ---
netsh advfirewall firewall add rule name=”SQL Server” dir=in action=allow protocol=TCP localport=1433
--- To enable Dedicate SQL Administration Connection Port 1434 ---
netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434 profile=domain
--- To enable SQL Service Broker Port 4022 ---
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022 profile=domain
--- To enable Transact-SQL Debugger/RPC Port 135 ---
netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135 profile=domain
-- To enable SQL Analysis Services Port 2383 ---
netsh advfirewall firewall add rule name="SQL Analysis Services" dir=in action=allow protocol=TCP localport=2383 profile=domain
-- To enable SQL Browser TCP Port 2382 ---
netsh advfirewall firewall add rule name="SQL Browser TCP" dir=in action=allow protocol=TCP localport=2382 profile=domain
-- To enable SQL Browser UDP Port 1434 ---
netsh advfirewall firewall add rule name="SQL Browser UDP" dir=in action=allow protocol=UDP localport=1434 profile=domain
-- To enable HTTP Port 80 ---
netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80 profile=domain
-- To enable SSL Port 443 ---
Netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443 profile=domain

Speaking of the command line, funny how everything old is new again. My IT career started with MS-DOS 5.0 thus the command prompt was all I had to work with. I certainly appreciated the Windows 3.1 GUI. Now we’re back to the future, and PowerShell is all the rage. Oh well, at least it’s not Edlin. I only had to learn a bit of Edlin retroactively but that was enough to scare me. So bring on Powershell; piece of cake.

Here is the Windows Firewall UI showing the ports added to the Inbound Rules: (HTTP is not shown)

Cont’d across:

Enabling Protocols

The TCPIP protocol needed to be enabled in SQL Server under both “SQL Native Client” (32bit and 64bit) and “SQL Server Network” configuration. This is done through the SQL Server Configuration Manager. (The SQL BOL is a good place to start for information on the SQL Server Configuration Manager and connecting to the SQL Server Database Engine.)

I also enabled Named Pipes. I don’t believe Named Pipes is necessary for SharePoint 2010 but it may be required for other applications using the SQL Server for back-end databases. For a brief description about each protocol shown, see Choosing a Network Protocol in the SQL BOL.

If TCPIP is not enabled, the SharePoint 2010 Setup Configuration Wizard (PSConfig UI) will throw an error when it tries to contact the SQL Server to create the SharePoint_config database:

This is what happened to us at work. We missed enabling TCPIP under the SQL Server Network Configuration setting. We did not realize we missed enabling TCPIP under the Server setting, so we spent about half an hour looking for other reasons until we finally did an Internet search on the error. Some others have run into this error and the suggested solutions indicated communication issues. Eliminating the network and firewall settings as the cause led us to the SQL network configuration settings.

That is all I needed to do for the test network. The next article will walk through installing SQL Server Reporting Services and configuring the RS instance for SharePoint Integrated Mode.

Guest Author: Joan Resnick Ehrlich

Joan Resnick Ehrlich has been in the IT industry for 15 years and is Corporate IT Administrator for a mid-sized company on Long Island, NY. Prior to entering the industry Joan was a business researcher, and she enjoys combining her research skills with IT work. In addition to SharePoint, her primary responsibilities include Windows Server, Active Directory, Exchange Server, and SQL Server.