Shanon Mulley on September 4, 2013
This article describes how to troubleshoot connecting to a server on another computer. As a multi-user application, it is common to connect to a database that is running on a different computer. For the most part, this is an easy and routine process. There are, however, a number of settings on the server machine which could be making the connection either difficult or impossible. These settings are described below, along with instructions on how to rectify them.
SQL Server Protocols
This is the most common cause of difficulties when trying to connect to the server from a different computer. When MS SQL Server 2008 Express is installed, by default the protocols required for remote connection are not enabled. To enable these, follow the steps outlined below.
Open the SQL Server Configuration Manager, by selecting from the start menu: Microsoft SQL Server 2008>Configuration Tools>SQL Server Configuration Manager, as shown below:
Once the SQL Server Configuration Manager is open, select SQL Server Network Configuration>Protocols for DECEZY. This should look like the screenshot below. Note that all the protocols are disabled, except for Shared Memory:
The TCP/IP protocol needs to be enabled. To do this, right click on the TCP/IP line, and select Enable:
You should receive this message after changing the protocol:
Windows Firewall
If you have a firewall enabled on the computer that the server is installed on, this can prevent other computers from connecting to it. This section will describe how to enable the server to work with windows firewall. If you have another firewall, you will need to consult the documentation from the firewalls vendor to do this. Also, if your firewall is not currently on, these instructions are not necessary.
Please note, the steps outlined below are for windows XP. The steps may be slightly different for Windows Visa, Windows 7 or Windows 8.
First we need to open the security center. To do this, firstly open the control panel by selecting Start>Control Panel:
In control panel, open the security center:
Now, we need to determine if the firewall is on, and if the firewall is the windows firewall is being used (as opposed to a 3rd party firewall). In the screenshot below, the firewall is on, and the firewall being used is windows firewall. If this is the case on your computer, these instructions are relevant for you:
In the screenshot below, the firewall is still on, but a 3rd party firewall is being used. If this is the case for your computer, these instructions are not relevant for you. You will need to follow the instructions provided by the makers of the firewall you are using:
In the screenshot below, the firewall is off. If this is the case for your computer, you do not need to take anymore action, as the firewall is not on, and will not be interfering with DecEzy:
If you have determined that these instructions are relevant for you, then continue. With security center is open, open the windows firewall settings by clicking in "Windows Firewall" (Note: If the firewall is currently off, you do not need to configure anything):
When the windows firewall settings opens, it should look a little like this. Ensure the option "Don’t allow exceptions" is not checked:
Switch to the Exceptions tab, and then click on the "Add Program…" button:
The "Add a Program" window will open. This will list a small list of programs that you can add. If the SQL Server is not there (It probably won’t be – two other programs starting with "SQL Server" may be though), you will need to select it manually. Do so by clicking on the "Browse…" button:
In the browse window, select the path of your SQL Server installation. If you installed via the DecEzy installation, using the default options, this should be c:\Program Files\Microsoft SQL Server\MSSQL10.DECEZY\MSSQL\Binn. (This may be slightly different if you have a 64 bit operating system).
Once in the correct folder, select the "sqlservr" file, and click on "Open":
This will take you back to the "Add a Program" window. The sqlservr file should now be an option. Select this, and click on OK:
This will take you back to the Windows Firewall settings window, where "sqlservr" is now added as an option. Ensure this option is ticked, and then click on OK:
That’s it. An exception has now been added to windows firewall, to allow other computers to connect to the SQL Server on this machine.