When using SQL Server Management Studio Express I get the error "SQL Server does not allow remote connections". How to resolve this error?

Question:

In SQL Server 2005 when trying to connect to a remote database with SQL Server Manager Studio Express, I get this error: "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))" How to resolve this error?

Answer:

In addition to the above error the user might get the following errors too:

• SQL Server does not allow remote connections
• SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
• Server does not exist or access denied

The errors are self-explanatory for a Database Administrator, but for a beginner or Developer these errors will be more frustating.

It should be noted that SQL Server 2005 Express is not automatically configured for 'remote access' during the installation. This is not a bug merely the default configuration. The simple solution for this problem is to enable the network protocols for the SQL Server Express instance using Surfact Area Configuration (SAC) utility for SQL Server 2005 programs group on your machine.

Surface area reduction is a security measure that involves stopping or disabling unused components. Surface area reduction helps to improve security by providing fewer avenues for potential attacks on a system.

For new installations of Microsoft SQL Server 2005, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state."

SQL Server Surface Area Configuration is available on the SQL Server Start menu:

• On the Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and then click SQL Server Surface Area Configuration.
• Choose SQL Server 2005 Network Configuration and select the installed SQLEXPRESS instance.
• On right-hand pane select the required protocol either TCP/IP or Named-Pipes (or both), right-click with the mouse to set it to 'Enabled'.

In some special cases  it may be an additional requirement to start the SQL Browser service, the usage and benefit of having this service run is that users connecting  remotely do not have to specify the port in the connection string.  It is a Best Practice in Security aspects to not to run the SQL Browser service as it reduces the attack surface area by eliminating the need to list on to an UDP port from client.

If your network is tightened with relevant firewall and security access then (in special cases) you might need to enable SQL Server and SQL Server Browser to be exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall or any other firewall software application that is used.

Lastly, you might need to reboot the machine in order for these network configuration changes to take affect as simply restarting SQLServer and SQL Browser services may be insufficient.

For more information on usage of Surface Area Configuration tool refer to updated SQL Server Books OnLine.