When connecting to an instance of Microsoft SQL Server 2005 database, including SQL Server 2005 Express, Developer and Enterprise editions, for authentication or accessing and retrieving and updating the data, the the following error message may appear. The problem happens no matter the connection is originated from remote computer or on SQL Server local computer itself, and can be caused by any program, such as SQLCMD utility, used 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.
Or,
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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
This problem may occur when SQL Server 2005 is not configured to accept incoming local and remote connections, which is disabled by default in SQL Server 2005 Express Edition, SQL Server 2005 Developer Edition and also SQL Server 2005 Enterprise Edition. To solve the connection forbidden problem, SQL Server 2005 needs to configure to allow incoming local and remote connections.
Firstly, ensure that SQL Server 2005 is configured properly to allow incoming connections on the instance of database server, else enable and turn on the local and remote connections setting.
Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
On the “Surface Area Configuration for Services and Connections” page, expand Database Engine tree, click Remote Connections.
Select Local and remote connections, or Local connections only which applicable only if there is no remote system tries to connect to the SQL Server, useful when you just trying to connect and authenticate with the server after installing.
Select the appropriate protocol to enable to local and/or remote connections environment. To ensure maximum compatibility, select Using both TCP/IP and named pipes.
Click Apply button when done.
Click OK button when prompted with the message saying that “Changes to Connection Settings will not take effect until you restart the Database Engine service.”
On the “Surface Area Configuration for Services and Connections” page, expand Database Engine, then click Service.
Click Stop button to stop the SQL Server service.
Wait until the MSSQLSERVER service stops, and then click Start button to restart the MSSQLSERVER service.
Secondly, SQL Server Browser service has to be enabled to allow for local and remote connections if SQL Server 2005 is running by using an instance name and users are not using a specific TCP/IP port number in the connection string.
Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
On the “Surface Area Configuration for Services and Connections” page, click SQL Server Browser.
Select Automatic as the Startup type to start SQL Server Browser service automatically every time system starts.
Click Apply button.
Click on Start button to start the service immediately.
Click OK button.
Finally, if remote computer needs to connect and access SQL Server, an exceptions in Windows Firewall included in Windows XP SP2 (Service Pack 2), Windows Server 2003 and Windows Vista needs to be created. If you’re using third-party firewall system, the exception rules also needed to be created to allow external remote connections to the SQL Server 2005 and SQL Server Browser Service to communicate through the firewall, else connections will be blocked. Consult the firewall manual for more details. Each instance of SQL Server 2005 must have its own exception, together with an exclusion for SQL Server Browser service.
SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:
Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Configuration Manager.
In “SQL Server Configuration Manager”, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
On the “SQL Server Browser Properties” page, click the Advanced tab, locate the instance ID in the property list.
Click OK button.
Then create an exception for SQL Server 2005 in Windows Firewall.
Click on Start button, the click on Run and type firewall.cpl, and then click OK. For Windows Vista, type firewall.cpl in Start Search box and press Enter key, then click on Allow a program through Windows Firewall link on left tasks pane.
In “Windows Firewall”, click the Exceptions tab, and then click Add Program.
In the “Add a Program” window, click Browse button.
Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open button. MSSQL.1 with is a placeholder for the instance ID that is obtained from previous procedure. Note that the path may be different depending on where SQL Server 2005 is installed.
Click OK button.
Repeat steps 1 through 5 for each instance of SQL Server 2005 that needs an exception.
For SQL Server Browser service, locate the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, and click Open button.
Click OK button.
Basel said:
I was having SQL 2000 then I formatted my PC and then I Installed SQL 2005, and SQL 2000 then I uninstall them both, after than I installed only SQL 2000. when I tried to open a connection to this SQL server from my .NET code (which was working fine previoulsy before teh formatting) the below message appears.
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. (provider: Shared Memory Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
James said:
I had the same problem for quite a long time. This step solved my problem:
Set the “Data Source” property to “.” (By default mine was set to “.\SQLEXPRESS”)
____________________________________________________________________________________________
Not knowing how detailed your knowledge is, I ll explain step by step:
1) Open Visual C# as administrator (Right click on Visual C# and click on
“Run as administrator ”
2) Data -> Add New Data Source
3) In the “Data Source Configuration Wizard” window select “Database” and click Next
4) Click on “New Connection”
5) In the “Add Connection” window click on the “Change” Button
6) In the “Change Data Source” window select “Microsoft SQL Server Database File” and
click OK
7) (I “Use Windows Authentication”). If you “Use SQL Server Authentication” then enter you
your user name and password.
8) Click on the “Browse” button and select the database.
9) Click on the “Advanced” button.
10) Set the “Data Source” property to “.” (By default mine was set to “.\SQLEXPRESS”)
11) Click OK, then click Next and select the tables (etc) you want.
After doing so, I just copied the connection string to my code (in the Database Explorer right click on the database, click properties and copy the connection string from there):
SqlConnection NrthWSqlConnection;
NrthWSqlConnection.ConnectionString =
“Data Source=.;AttachDbFilename=\”C:\\SQL Server 2000 Sample Databases\\NORTHWND.MDF\”;Integrated Security=True;Connect Timeout=30;User Instance=True”;
NrthWSqlConnection.Open();
This worked for me. I hope it will for you as well
James
liedong said:
Does your code look for SQL 2005 or SQL 2000? You said you only SQL 2000 on your machine
Antoinette said:
*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. (provider: Shared Memory Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)*
This error also appeared when I’m trying to run my program in c#. I already tried the solution above but I still have this message. Is there any other solution?
by the way, I’m using SQL 2005 express edition…
liedong said:
Have you start the SQL Server? Open SQL Management Studio, and make sure the service is running.
Itachi21 said:
I did everything and everything is running, still dont work…
satheesh said:
thk u very much …. sql work’s perfectly
Rob said:
The problem for me was that I had done several installs leaving at least 6 instances under C:\Program Files (x86)\Microsoft SQL Server i.e. MSSQL.1, MSSQL.2 etc. MSSQL.1 was the express edition and I had its service off. Once I turned the SQL Express service on the app worked.
Rob said:
!!! The problem for me was that I had done several installs leaving at least 6 instances under C:\Program Files (x86)\Microsoft SQL Server i.e. MSSQL.1, MSSQL.2 etc. MSSQL.1 was the express edition and I had its service off. Once I turned the SQL Express service on the app worked.
Venkatesan said:
Thanks ever somuch The problem is solved
Ruchi said:
I m getting the same error and m unable to connect with database engine..but can do with integration services. With server type ‘Database Engine’ it doesn’t give me connect option.
Moreover, there is some problem in installation as reporting services cant be installed…
Dachi said:
Hey I followed this and did the steps closely, but the same error message still shows up. Is there another way to deal with this problem that i am missing?
merope said:
this helped me a lot!!
thank you soo much 🙂
sonu said:
Try restarting MS SQL Server after checking the above settings to allow remote connections
jagan said:
see here u wrote we need to stop and start the service in middile .but Iam getting error message like An error has occured while performing the operation .here what might be the reason?
ZiKaS said:
Thank you so much
Eyitayo said:
Thanks sooooooooo much.
I had almost lost my mind while changing my SQL server settings before i got to this site.
This is very wonderful , your the best!!!
Tim said:
This article helped me a lot….Thank you…
Monique said:
Hi there
I’ve tried this and I’m still getting the error.
Please help. I’m using SQL 2005 Express Edition
Monique said:
Ok, nevermind, my connection string was incorrect was being fusy.
problem solved
Shoaeb uddin said:
its helpful, thanx ton..
Shoaeb uddin said:
I appreciate this forum, cauz many of my runtime issues has been solved by this.. dont have time else i would have done more this site..
Shoaeb uddin said:
I would be better if v all answer pointwise, for eg.,
if need for installation, will write steps one by one,
Thanx,
Mihir said:
I hav installed SQL Server 2005 on my PC (XP SP2).
Earlier i had no problem in connecting to the server but suddenly
i hav started getting this error when i try to connect.
“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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)”
i tried everything that u hav mentioned above but my MSSQLSERVER service does not start;neither manually nor automatically.
i hav selected the local connection only option.
My Named pipes ,tcp/ip and other protocolas r also enabled.
Please help
Thanks.
MIHIR
pepsigrl said:
yar seriously dis help me alot i mean ma sql was suddenly giving di s mesage n m shocked couz m final year student and after 3 days ma endtdate is comming so ms hocked and very woorried couz i thought maybe system need to window reinstall and uffffffff dis is so much time consuming suddely idea came on ma mind i searches on google and find dis fabolous site and look ma prob is resolved
so thanku so much
A.Salam said:
Hi,
Please ensure that the server name you are giving should be like \SQLEXPRESS
Sajid said:
Hello
Am using SQL SERVER 2000 and am unable to connect to sql server.
the same error appear saying Sql server does not allow remote connections……………………………..
Where am I suppose to change my sql server settings.
Many thanks in advance.
regards
Sajid
deepak said:
thanx for solving my problem
ritesh said:
{“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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”}
Vision said:
Dear,
I had created a Test.mdf Databse in App_Data folder using Visual Web Developer 2008 Express Edition. When i tried to connect to Test.mdf DB with my StringConnection as:”Data Source=~/SQLEXPRESS;AttachDbFilename=D:/MYPROJECT/WEBSITE/LoginStatusControl/App_Data/Test.mdf;Integrated Security=True;User Instance=True”,
then i start debugging my Website, i logged in (using Login Control in WVD) i got the follow error:
” {“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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”} ”
I hope to recieve your advice via email (trunguc1089@gmail.com)
Thanks very much for any help.!
Trunguc.
Fawzy said:
Thanks for your nice article
Ramya Sharon said:
Hello..
I am Ramya Sharon.. I have tried to initialize tcp/ip and named pipes protocol according to your advise on the error specified here..
However, i get the following error.. though i am an administrator.. what do i do…?? please help..!!
“” The service did not respond to the start or control request in a timely fashion, you need administrator privileges to be able to start/stop this service. (SQLSAC)””
Mamoun J. said:
Thanks Alot, This Article was very useful.
neha said:
hieee
i have tried everything but its not working at all.please help me soon as my work is pending because of that..
reply me sooooon
Discount Codes said:
Actually my problem is not very straight forward. I am trying to transfer data from one database to another using INtegration service and on a scheduled job. But whenever I am trying to run it, I am getting this.. No idea how to resolve it… Now trying a trial and error method… No luck yet
rohit kandhal said:
hi all …
me too getting same error. i’ve tried everything posted above.
i’ve enabled sql server authentication, tell me about the connection string too..
conPubs = new SqlConnection(@”server=localhost;Integrated Security=SSPI;database=rohit”); is it correct ??
thx.
Yasin said:
Great post!!!
Thanks a lot 🙂
Cheers,
Yasin
Rizwan said:
i have tried all above mention but still i m not access the 2005 DB in 2000
but from 2005 i can access 2000 DB.
its shows connection fail error.from sql server 2000.
pls help me
gagan said:
thnx allot
student said:
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. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
student said:
sql server 2005 express ran well for the first time with visual studio but after tht it gave this problem…plz help….
Don Bose said:
Hi
I got the reasons for the following error. This error is comming because you have to enable remote connection for 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.
In the following url theire is ways to enable remote connections in sql server
http://sqlservererror-info.blogspot.com/2009/07/error-has-occurred-while-establishing.html
Regards
Lumina K Nair said:
If you re thinking how to enable remote connection on sql server
http://sqlservererror-info.blogspot.com/2009/07/error-has-occurred-while-establishing.html
rgds
Jomon said:
Pls look at this
http://aspnetmembershipprovider.blogspot.com/2009/02/error-has-occurred-while-establishing.html
Will solve pblm
Lipika said:
thanks a lot..I was able to solve the problem
Andrew said:
I got the error message when connected to sql 2005 server by RDP “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. (provider: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)”
After tried several times, the connection wkorks, then I can connect to sql 2005 with Managerment Studio”, but server settings are file because another computers can connect the server. Only happened sometime, not always. When connected, everything works fine. Please help.
rohan said:
still getting the error………….done these steps over millions time……wtf ………………
rohan said:
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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Ishwinder said:
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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Vamshi said:
Hi guys,
I work as a DBA and i configured replication from server A to B. but iam geting an error 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. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061).
snapshot agent is running on server A but server A is not able to connect to server B
when i check in replication monitor ican see the error can connect to server and the above error…can anyone help me on this issue..iam working on it since 2 ddays but no luck.
iam able to ping server B from server A using 1433 port. even tcp/ip is enabled on both servers and there is no windows firewall enabled on server B. i even enabled sql server browser. i dont understand what is the issue.
karthik said:
I got above mentioned error to access database from my server system.What are the possible ways to overcome this problem
Pingback: 2010 in review of My Blog « All About SharePoint
socket error 10061 said:
This information is really nice. This type of post is attracting people to read. Thank you for sharing…………….
Get More Info said:
It is used frequently by kitchen and bathroom designers to create
a ‘virtual’ view of how a prospective customers new design
would look also to this end plays an important part in the
conversion process for a lot of kitchen and bathroom companies today.
Cabinets are some of the home fixtures that add function and
wonder to your home. Ductwork may hang from the ceiling, fabric and bricks provide texture
and stability.
http://tinyurl.com/0577roach40859 said:
Thank you so much for spending time in order to compose
“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. (provider: Shared Memory Provider, error:
40 – Could not open a connection to SQL Server) (.Net SqlClient
Data Provider) All About SharePoint”.
Many thanks once again ,Isiah
Teksas poker chip satışı said:
Hello there! I could have sworn I’ve been to this website
before but after browsing through some of the post I realized it’s new to me.
Anyways, I’m definitely happy I found it and I’ll be bookmarking and checking back frequently!