When trying to connection to SQL Server remotely, you may see the following error messages:

SQL Server does not allow remote connections

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (Provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))

Server does not exist or access denied

If you need to connect to SQL Server from a remote machine via SQL Server Manage Studio, this is a quick list of steps to take to configure SQL Server on the server you are connecting to.

Open the SQL Server Surface Arrea Configuration utility.

Click the link at the bottom of the utility called Surface Area Configuration for Services and Connections.

Tree open the SQLEXPRESS node, tree open the Database Engine node, and select Remote Connections.

Choose the radio button Local and remote connections. Then select the Using TCP/IP only radio button.

Tree open the SQL Server Browser node and select Service.

Make sure the Startup type is set to Automatic. If the Service status is not set to Running, click the Start button to start the SQL Server Browser.

Open the SQL Server Configuration Manager utility.

Tree open the SQL Server 2005 Network Configuration node and select Protocols for SQLEXPRESS.

Right-click the TCP/IP value and choose Enable from the context menu.

Double-click the TCP/IP value to open its properties. Choose the IP Addresses tab on the properties dialog.

Make sure in the IP1 section, it is set to Active = Yes and Enabled = Yes.

Tree open the SQL Server 2005 Services and right-click the SQL Server (SQLEXPRESS) value. Choose Restart from the context menu to restart SQL Server and apply all of these changes.

If you are running a windows firewall, you will need to configure it to allow remote connections to SQL Server. Open Windows Firewall in the Control Panel.

Click on the Exceptions tab. Click the Add Program button. What you are going to do is add the SQL Server browser and SQL Server executable to the allowed list so the firewall does not block the ports they need to connect with.

Browse to the sqlbrowser.exe executable. By default it is at: C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe. Click OK to add this program to the allowed firewall list.

Click the Add Program button to add another executable. Browse to the sqlservr.exe executable. By default it is at: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe. Click OK to add this program to the allowed firewall list.

Click OK to close the Windows Firewall dialog.

To connect to this remote SQL Server instance, open Microsoft SQL Server Management Studio. Click on the Connect button to connect to a new instance.

Select Server Type = Database Engine, Server name: [your IP address or hostname]\SQLEXPRESS (NOTE: This is very important, you must put “\SQLEXPRESS” at the end in order to connect to a SQLEXPRESS instance on a remote server. Choose Authentication = SQL Server Authentication, Login = [your login name], Password = [your password]. Click the Connect button to connect to this remote server.