Wednesday, July 15, 2009

An error has occurred while establishing a connection to the server - asp.net,sqlconnection,sql server 2005,sql,remote,connection,c#.net,vb.net

The Following is a usual error when trying to connect sql server 2005 from a remote computer.

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 happened because the sql server 2005 instance is not configured to accept remote connections. To configure sql server 2005 to accept remote connections follow the following steps.

1. You have to enble remote connections on the instance of sql server that you want to connect from remote computer.

2.You have to check sql server browser service is on.

3. Make sure the configuration of firewall is allowing network traffic related to sql server and sql server browser service.

Now we can look in detail each of the above steps.

Enable remote connection for sql server express or sql server 2005 developer edition

Click on start->Programs->Microsoft Sql server 2005->Sql server configuration tools-> Sql server Surface area connection as below


When clicking on sql server 2005 surface area configuration the form will be
displayed like this


On this sql server 2005 surface area configuration click on the surface area configuration for services and connections. On the surface area configuration for services and connections click on the remote connections.



When clicking on remote connections




If the local connection only radio button is checked then change it to local and remote connections. When clicking apply then a message will appear as changes will apply only when you restart the service. Click Ok then restart the system

2.Enable the SQL Server Browser service
When connecting sql server 2005 using instance name you have to enable sql server browser service to allow remote connections. For example if sql server is connecting using computername/sqlexpress you have to enable sql server browser service to enable remote connections. To enable sql server browser service follow the following steps.

1. Click start-> Programs-> sql server 2005-> Configuration tools->Sql server surface area connections

2. On the Sql server surface area configuration page click surface area configuration for services and connections.

3. In the surface area configuration for services and connections page click sql server browser. Make startup automatic and then click apply.

4 Click start then OK

17 comments:

Anonymous said...

Hi

i was also facing the issue when i was trying to connect connect to sql server 2005 using sqlconnection then the error message comming as

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.

When i was trying to open sqlconnection

And pls look at this also

http://aspnetmembershipprovider.blogspot.com/2009/02/error-has-occurred-while-establishing.html

Anonymous said...

Hi

I had the issue when trying to open sqlconnection and i understood that the error is comming because i had to enable remote connection in the sql server. It is explainned in the following link

"How to enable remote connection in sql server 2005"

http://aspnetmembershipprovider.blogspot.com/2009/02/eanble-remote-connections-on-instance.html

Chev B said...

Excellent piece you put together SHIJO BABY...
For all users that still experience issues subsequent to following these steps... ensure that your SQL Instance Name is explicitly stated in the connection string. e.g.

add connectionstring="Data Source=MYSERVER\MYSQLINSTANCE;Initial Catalog=DBNAME;Persist Security Info=True;User ID=USERNAME;Password=PASSWORD" name="sConn" providername="System.Data.SqlClient"

Anonymous said...

Chev B. Where would I find this connection string? When I did the "start" in step 4, I received "
An error occured while performing this operation" Thanks in advance for the help.

ciscus said...

Hi

I have a problem with MS SQL 2005, MS SQL that I can not be accessed and the remote.
While I already activate Allow Remote Access, but still can not be accessed.
Please help!

Anonymous said...

Hi,

I have a problem with MS SQL 2005.
I`ve already activated Remote Access, but in vain.
I`m making a connection using integrated authentication.
Any ideas what`s wrong?

Remote Desktop said...

Its so highly informative things are posted in your blog. I was seeking for this type of blog only and also that you have a fresh and interesting content.
Web Conferencing

Proferis said...

In SQL Server Configuration Manager -> SQL Server 2005 Network Configuration -> Protocols for SQL 2005 -> Right clik on TCP/IP -> Properties:
Tab: Protocol
ENABLED - SET TO YES

TAB: IP Adresses
IP1: ENABLED - SET TO YES
IP2: ENABLED - SET TO YES

Anonymous said...

One thing to check if you are still getting the error - ensure that when you opened the command prompt that it is not running in restricted mode.

This can be done by running the cmd prompt with runas and unticking the run as restricted user (Note: By default on Windows Server 2003/2008 - command prompts run in restricted mode - and will generate this error.)

Anonymous said...

Dont ask anything.....this is microsft bullshit... i want to connect 5 system with one sql server 2005 having windows server 2003.... 3 of the systems are working fine and 2 systems are not connected to the server the same error 40 comes up.....thats gonna ruined my life.... FMS

Anonymous said...

Hi,
In SQL Server 2005 start up, it is asking for Server Name, when i clicked browse, i do not see any server component in the window. Can you help me to resolve this issue?

Thanks.

Mohamed Shaker said...

Hi all,

I was having the same problem here, and I couldn't find a solution until I turned off the firewall state of all profile of windows 2008 server. After that you will be able to ping your windows 2008 server and to connect to your SQL server. This link describes what I'm trying to say here:
http://blog.benhall.me.uk/2009/01/unable-to-access-or-ping-windows-2008.html

Thanks,,

Anonymous said...

Eng.Sufian

I try every things, not worked for me. But
when i turned Server firewall off and all it's configration sett off from controlpanel. then the error stop appers to me.
sorry for my language.

best luck for you guys.

Anonymous said...

Chev B
when I host my website @ that time i got this problem. my connection string is like

please ans me where i have made a mistake.

Anonymous said...

name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\avec.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

Praveen said...

Hello can anybody pls tell me. I ran the application by Visualstudio2008 its work fine. but i ran on localhost(http://localhost/mywebappname) it shows "(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" ERROR.
Here I have only SQL2000 in Server2003 and i have VS2008,SQL2000 in Client PC(XP).
Still(from 25 days) im facing this problem. totally its nightmare :-((
praveen_asley@yahoo.com

Jagdeep Singh said...

Thanks buddy...for the help..