SQL Server does not allow remote connections

yadianna

When using SQL Server Management Studio Express I get the error "SQLServer does not allow remote connections". How to resolve this error?
Question:InSQL Server 2005 when trying to connect to a remote database with SQLServer Manager Studio Express, I get this error: "An error has occuredwhile establishing a connection to the server. When connecting to SQLServer 2005,this failure may be caused by the fact that under thedefault settings SQL Server does not allow remoteconnections.(provider:Named Pipes Provider,error:40-Could not openconnection 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
  

Theerrors are self-explanatory for a Database Administrator, but for abeginner or Developer these errors will be more frustating.
It should benoted that SQL Server 2005 Express is not automatically configured for'remote access' during the installation. This is not a bug merely thedefault configuration. The simple solution for this problem is toenable the network protocols for the SQL Server Express instance usingSurfact Area Configuration (SAC) utility for SQL Server 2005 programsgroup on your machine.

Surfacearea reduction is a security measure that involves stopping ordisabling unused components. Surface area reduction helps to improvesecurity by providing fewer avenues for potential attacks on a system.
Fornew installations of Microsoft SQL Server 2005, some features,services, and connections are disabled or stopped to reduce the SQLServer 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:

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

Insome special cases  it may be an additional requirement to start theSQL Browser service, the usage and benefit of having this service runis that users connecting  remotely do not have to specify the port inthe connection string.  It is a Best Practice in Security aspects tonot to run the SQL Browser service as it reduces the attack surfacearea by eliminating the need to list on to an UDP port from client.
Ifyour network is tightened with relevant firewall and security accessthen (in special cases) you might need to enable SQL Server and SQLServer 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 networkconfiguration changes to take affect as simply restarting SQLServer andSQL Browser services may be insufficient.
For more information on usage of Surface Area Configuration tool refer to updated SQL Server Books OnLine.


转自:http://www.sql-server-performance.com/faq/sql_express_remote_connection_p1.aspx