How to configure SQL Server 2005 to allow remote connections

When you try to connect to an instance of Microsoft SQL Server 2005 from a remote computer, you may receive an error message. This problem may occur when you use any program to connect to SQL Server. For example, you receive the following error message when you use the SQLCMD utility to connect to SQL Server:

Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred 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.

This problem may occur when SQL Server 2005 is not configured to accept remote connections. By default, SQL Server 2005 Express Edition and SQL Server 2005 Developer Edition do not allow remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.


Follow the following link for more detail by microsoft support

http://support.microsoft.com/kb/914277

Some time same error occur because of your configuration in web.config.

if your error is some thing like

"An error has occurred 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 connection. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

Cause: ASP.Net 2.0 Providers are trying to pull from the server's (nonexistent) Providers database.
By default the machine.config file is trying to pull the Provider information from a SQLExpress database using an invalid connection string named "LocalSQLServer". Many web servers will not have SQLExpress enabled, and will not have this value set to a valid SQL Server database that is of use to you. In a shared hosting environment, this is especially true, as it would be expected that you would want your Provider information stored in your database and not some single database shared by the other users of that web server.

Fix:
Since you probably cannot access the machine.config file, you need to override the Provider settings in your web.config file, and set the connection string name to your connection string name. The following code comes from the machine.config file and has been modified to first remove each provider before adding the provider.

Add the following code to your web.config file just under the "" tag.
Make sure to replace the 3 occurrences of connectionStringName="LocalSQLServer" with your connection string name.

This goes in the system.web section of web.config.



















Your database must be configured for the ASP.Net 2.0 Providers. This article assumes that you have already configured your database to use ASP.Net 2.0 Providers. If you haven't, there is an article at http://www.aquesthosting.com/HowTo/Sql2005/Providers.aspx.