Configuring SQL Server 2005 Dev Edition for Remote Connections

Configuring SQL Server 2005 for Remote Connections has become far more challenging than it should be for a server product. Microsoft's intentions of protecting us have instead cost developers enourmous amounts of time and money in trying to figure out what was before, quite simple and efficient to install and configure. Based on my experience, hopefully I can help.

Before I get started I want to recommend two very good blog posts that helped me figure out how to solve the issue I was having getting SQL Server 2005 to accept Remote Connections.

Both are very thorough at explaining the various errors you might experience, but neither had my exact solution.

First one is Charlie Arehart's blog. http://carehart.org/blog/client/index.cfm/2006/7/8/sql2k5_Error_establishing_socket

Second one is Sarge at Sargeway, LLC. http://www.sargeway.com/blog/index.cfm/2006/10/12/SQL-Server-troubleshooting

Ok, this post is going to simply explain the steps from beginning to end how to get it working, and it shouldn't take more than five minutes to complete these steps.

If you get an error when starting any of the services for SQL Server 2005, I recommend re-installing it. That helped me.

These steps should take place on your Windows Server, mine is Windows Server 2003 SP2.

After installation, the first step is Start>All Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Surface Area Configuration

Click the link that says: Surface Area Configuration for Services and Connections

In the left pane click Remote Connections, under Database Engine. In the radio buttons check Local and remote connections. You can select Using TCP/IP only OR TCP/IP and named pipes, your choice. (Just not named pipes only).

Then in the left pane select Service under Database Engine, and restart the service.

While you're here, in the left pane, make sure the SQL Server Browser service is started. Select SQL Server Browser, and then Service under it.

Click OK when finished. Close the Configurator.

Now its time to open the SQL Server Configuration Manager. Start>All Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager.

In the left pane, expand SQL Server 2005 Network Configuration, and select Protocols for [SERVERNAME].

Notice how TCP/IP and Named Pipes are disabled. Double click TCP/IP - this is the one we're concerned with for remote connections. A dialog box pops up.

Change the drop-down next to Enabled to Yes. Then click the IP Address tab.

This is the place I was having trouble. I will explain later how I discovered which Port SQL Server was actually using, but while you're there, change the TCP Dynamic Ports in the IPAll section to the port number you want to use. (Also make sure that port number is open in your firewall.)

Click OK to close the dialog box. You'll get a warning to restart the service so select SQL Server 2005 Services in the left pane. Restart SQL Server([SERVERNAME]). Make sure SQL Server Browser is Running too. That's all of the configs for the server.

The following steps should take place on your workstation or notebook, where you will be doing development work; thus the reason for the remote connection.

Ok, open up SQL Server Management Studio. Let's setup an Alias to make connecting a whole lot easier. And actually this is the ONLY way I've found to declare the port number I want to connect with, when it's other than 1433(default). So if you know of another way to connect and declare the port number, leave a comment please.

Start>All Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager on your client machine. Expand SQL Native Client Configuration and select Aliases. Right-click in the right pane, and select New Alias.... Name it, declare the port number, protocol is TCP/IP and put in the server using a FQDN syntax (www.whatever.com).

[It is assumed that your domain name is hosted on a DNS server and pointed to the correct static IP which resolves to your router; then the router will direct traffic on that particular port to the correct internal IP. That's how I'm doing it.]

Click Ok to close the Alias dialog box. And let's register the server. In SQL Server Management Studio, click View>Registered Servers to display the pane along the left side. Right click in the white space in that pane, and choose New>Server Registration.... For Server name, put the name of your Alias. Choose SQL Server Registration. Login. Test it. It should work. Save it.

Now in Object Explorer connect to the remote server for the first time, the with the same login as registering the server.

That's it - if it works, congrats. That was easy right? Microsoft is really stupid for burying that tidbid of knowledge of setting the port with Dynamic TCP in the IPAll box. That was the one setting that set me free. So how did I find that?

On Charlie Arehart's blog, mentioned at the beginning of this entry, Sarge left a comment where he shared a command to use in a command-prompt box that lists all of the ports your computer is listening on. That command is

Then in Sarge's blog, also mentioned at the top of this entry, he sparked my interest in viewing the SQL Server ERRORLOG, which lists the ports SQL Server is listening to.

You can find the ERRORLOG in the Management Studio, in the Object Explorer, expand Management, expand SQL Server Log, and double-click to open the Current log. Within the log, under the Source column it'll say Server, and in the Message column it'll say

Where 1373 is the port setting. Or it'll say Which means localhost IP is using port 1984. So if you go back into the Configuration Manager and double-click TCP/IP, click on the IP Addresses tab, you'll see where those port numbers are set. Change them to what you desire, if they are not already.

That's it. SQL Server connects remotely now, even in the CF Administrator.

I hope this entry has helped save you the 12 or so hours it took me to figure this out on my own. I want to thank Charlie and Sarge to leading down the right path.