Recently I’ve been challenged at work and asked to use Microsoft SQL Server as the database, using Windows Server 2008 R2. I decided to take the standard install options, doing nothing special. During the install there was a warning about the need for additional Firewall configuration. Making note of this, I proceeded with the install without incident. Once the install was done, I ensured the server was running by verifying all processes were “Started” with the exception of a few that were not needed such as SQL Active Directory Helper, SQL Full-text Filter (beyond the scope of this document), SQL Server Agent (not going to do any scheduled jobs yet), and SQL Server Browser (no need to announce to the world I’m running a database).
My next test to make sure the MS SQL server was up and running as expected I logged into the server using the client tools installed with the server, Microsoft SQL Server Management Studio. I know you can install without them but it sure makes life easier on a development server when you have client tools set up locally when troubleshooting. To ensure the SQL Server was running I logged in using localhost as the Server name.
Once connected you will be able to brows your database objects such as Databases, Security, ect…
The server was up and running so the next logical step would be to enable remote connectivity by opening the MS SQL port to incoming traffic. In Windows 2008 the Windows Firewall with Advanced Security tool is fairly straight forward. To open this tool click on Start, Administrative Tools, Windows Firewall with Advanced Security. You could also take the shortcut and type “fire” in the Search tool and then click on the Windows Firewall program icon.
The tool is basically broken down into two main areas, Incoming and Outgoing Rules. For basic MS SQL connectivity we only need to add one Incoming rule for port 1433.
- On the left side, click on Inbound Rules.
- On the right side, click on New Rule…
- In the wizard, choose Port, click Next.
- Choose TCP and provide the value of 1433 after choosing the Specific local ports option then click Next.
- Click the Allow the connection option and click Next.
- Check all three boxes Domain, Private, and Public, depending on your desired network configuration.
- Provide a descriptive name like MS SQL Port 1433 Incoming and click Finish.
When your done the Windows Firewall should have a new Inbound rule.
This is where I ran into a few moments of hair pulling. Just because you’ve opened the port, I couldn’t get any remote clients to connect. I continually revived Connection Timeout issues. After a few port scans from both the localhost as well as a remote client I discovered that despite the Firewall rule, I still couldn’t access the server. At one point I completely turned off the Windows Firewall with the same frustrating results.
Evidently when you take the defaults (unless I missed it) the more recent installs of Microsoft SQL disable both Named Pipes and TCP/IP. To get remote connections working the TCP/IP is required to be enabled.
- Open the SQL Server Configuration Manager
- Provided you installed the client admin tools this program can be found by clicking on Start -> Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager. Its a standard snap-in so I bet you can open it just like you would any of your favorite snap-ins.
- Once the Configuration Manager is open, expand the SQL Server network Configuration and click on Protocols for MSSQLSERVER.
- On the right side you should see several protocols listed. The one we are interested in is the one named TCP/IP. If this protocol is set to Disabled, all attempts to connect to your SQL server will fail miserably.
- Double click on TCP/IP and change the Enable option from No to Yes.
- Click Apply, then click OK.
To make life easier, I also enabled the Named Pipes to prevent having to troubleshoot other client connectivity problems.
For these changes to take effect you will then have to restart the SQL Server (MSSQLSERVER) process. After a quick restart, all of your connectivity concerns should have evaporated.
To test this you can now open the SQL Server Client tool, using an IP and connect to the server.