SQL Server Remote Connection

The Situation

Ok, so you’ve installed SQL Server 2005/2008 on one of your development system. The installation went as smooth as you could hope for. Everything seems to be working perfectly. You are able to connect using SQL Server Management Studio locally and created a user database. You then decided to try to spice thing up a notch. You want to try to connect to your newly created user database that is on your newly installed SQL Server from another workstation within your Local Area Network (LAN).

To your surprise, you are unable to connect to the newly created user database. Gosh darn, what went wrong? You have checked that you are using the correct login credential with the appropriate permission. You also have checked your firewall setting, and even turned off the Windows Firewall that you have. But still, you could not make that connection. When you try to connect to the SQL Server using SQL Server Management Studio on the remote workstation, you got the following error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connection. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server).

Possible Solutions

One possible explanation for the problem that you have is that the newly installed SQL Server is only allowing local connection. Depending on what SQL Server edition that you’ve installed, by default SQL Server Express, Evaluations, and Developer edition is set to only allow local connection. But don’t worry, you can change this. There are two alternatives in which you can enable remote connection. They are:

Use SQL Server Surface Area Configuration

Notes: This only works for SQL Server 2005. There is no SQL Server Surface Area Configuration in SQL Server 2008. To allow remote connection, you can follow the following steps:

• Go to SQL Server 2005 Surface Area Configuration (It should be located on All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration)
• Once SQL Server 2005 Surface Area Configuration window is open, on the left navigation, there should be Remote Connection. Click on that option and you should see a screen similar to below:

• To enable remote connection, you need to choose Local and remote connections. You then will need to choose which protocol you are going to allow for the connection, either TCP/IP, named pipes or both.
• Once you make your choice, click on the Apply button and then Ok button.
• You then might want to restart your SQL Server service. Once it has been restarted, you should be able to connect from a remote workstation.

Use SQL Server Configuration Manager

You can use SQL Server Configuration Manager to enable remote connection in SQL Server 2005 and SQL Server 2008. To allow remote connection, you can follow the following steps:

• Go to SQL Server Configuration Manager (It should be located on All Programs > Microsoft SQL Server 2005 (or Microsoft SQL Server 2008) > Configuration Tools > SQL Server Configuration Manager).
• Once SQL Server Configuration Manager is open, go to Network Configuration, and then Protocols for [SQL Server Instance Name]. It should now display a screen similar to below:

• Here, depending on your network configuration, you might want to enable the appropriate network protocol. So if you want to enable TCP/IP protocol, you can do a right click on TCP/IP and select Enable.
• You then might want to restart your SQL Server service. Once it has been restarted, you should be able to connect from a remote workstation.

Some Additional Notes

• For SQL Server 2005, if you made your changes on SQL Server Surface Area Configuration, your changes will be automatically reflected on SQL Server Configuration Manager, and vice versa.
• If you are installing SQL Server as named instance (which is most likely case for SQL Server Express installation), you might want to make sure that your SQL Browser Service is running. If SQL Browser Service is not running, You will need to specify port number to connect to the SQL Server.
• Notice that by default Shared Memory protocol is enabled by default. This protocol is being used when you are connecting locally.