Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

SQL Server 2005 doesn't take remote Connections by default


:P
On this page:

I'm playing around with IIS7 today and I have a separate machine set up to check out a few of my existing applications both ASP.NET and a number of Web Connection ISAPI applications. It took a bit to get the server up and running but all went well after some tweaks.

 

The real head scratcher occurred though because I couldn't connect to SQL Server on my development laptop. I've recently installed SQL 2005 on this machine and I've been happily plugging away with it on my local machine for a few weeks now. While it works fine locally today I couldn't get it to connect remotely from the IIS7 box.

 

Eventually after a timeout delay I ended up with a connection error message that says:

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)

 

A couple of searches through the Properties of the server didn't show anything refusing connections – the connection count is set to 0 (unlimited connections) and this would be the logical spot to find an option that refuses connections, right?

 

Well, it turns out that this is controlled by an EXTERNAL utility, the Sql Server Surface Area Configuration Tool which can be found in Start Menu Sql Server Configuration section. By default Sql 2005 indeed refuses remote connections and you need to run this utility to enable remote connections. Intuitive isn't it?

 

Is this really necessary? Disabling remote connections on a SQL Server by default seems a little odd anyway. I mean it's a SERVER isn't it? Who's not going to want to have remote connections on this thing? At least add the option to the server itself in a logical place and even if you can't set it there give an indication that it's disabled elsewhere…

 

But no – we're too paranoid. If connections were disabled in the server itself somebody could hack that – yeah right...

 

This security stuff is really going a few steps too far. In Vista EVERYTHING pops up security permission dialogs because practically nothing including the native Windows Applications are signed. It looks like everything will have to be signed in order to not pop up permissions dialogs. This is a small vendor's nightmare – getting a verified signature is a pain in the ass and if that ends up being a similar process as it was with ActiveX controls we can look forward to the same mess where nothing is signed and everybody blindly clicks Ok… doesn't that sound like fun ?

 

Well the upside is my app is running with remote data now on IIS 7 which is nice. Next is hooking up some of the optional modules and see how they fare in the integrated pipeline...

 


The Voices of Reason


 

Randy Jean
February 16, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Sure glad I read this. I was just getting ready to set SQL 2005 up on a server. I've just been playing with it locally myself.

steve
February 16, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

"surface area" ? Whose brainchild was this?

And people wonder why I still use W2K and SQL Server 2000...

Flip
February 16, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Yup, I ran SMACK head first into this too! I was fortunate though to poke around the Start menu and the new menu options SQL Server created for me. I saw the surface config tool and poked around in there and tried a few things and luckily I got it to work.

But ya, it does seem like we're going from one extreme (Win 3.1 = wide open) to the other extreme of wicked lock down.

re Vista
It'll be interesting to see how VisualStudio acts on Vista?! :>

Dimitris Kavakopoulos
March 15, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Well, it is totally stupid, however it can be turned on/off quite easily.

I would hate to see such a similar feature in an ahem... open-source project that would require editing a massive config file...

This security paranoia is utterly stupid and annoying. And now, with vista and HD media, it's getting even worse!
Hey MS: Secure your damned products in first place, don't postpone bug fixing!

Ryan Riehle
March 15, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Well... mine is set to accept connections in the surface area config and everywhere else and I'm *still* getting this error. grrr... Not a firewall problem either and tcp/ip AND named pipes are enabled.


Reid K
March 15, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

One word... Unbelievable

You can't even install the sample OLAP dB due to
this issue. Give me a break!
Thanks for the info

nick
March 24, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Not work for me either! Can any other setting help?

Jason
March 27, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

I can see this being a valid default option in a corporate environment, especially when setting up many db's for development purposes.

Charlie
March 29, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Well I wasted about 30 minutes with this... until I read this. So thanks.

Jaypee
April 10, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

totally unbelievable... set the setting on one tool and restart the service on another tool to make the setting kick in.


Claudio de Biasio
April 18, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Wow, that is just amazing. I was testing an app I developed to send emails scanning a Web server talking with another SQL Server. No problems on my local SQL 2005 - and an exception while released. Gosh I was preparing myself for the worst nightmares! Great post, thank you so much!

Claudio de Biasio

AMS
May 04, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Actually it makes sense....esp from an IT environment---think of the following general viewpoint:

You have a group of engineers that end up running <insert favorite app here>. (I for one have installed programs to try them out, and then promptly forgot about them)...

Fast Forward...a bug is discovered that exploits said app by connecting to it and using it to propogate

Would you rather be in the position of having to lock down the machines with the software on it, or have it locked down by default...

Tom
May 11, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

What is the equivalent to the Surface Area utility in SQL 2000. I can't get SQL 2000 to accept remote connections. I was able to get SQL 2005 Express to work, but found some limitations that are forcing me to go back to my full version of SQL 2000.

Dave
May 17, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

If you are running mssql 2000 msde, by default tcpip is not turned on. You must run the "Server Network Utility" to turn it on.

Deepesh
May 31, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

I have visual studio 2005 application attempting to connect to a remote MS sql server 2000. And i get this 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) "

It is sql server 2000 and not 2005. but due to my Studio 2005 some setting is changed. Any solutions ?

Adam
June 13, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Im getting the same problem as Deepesh, im using VS 2005 but SQL Server 2000 but getting a SQL 2005 error message?

me
June 13, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Same problem as well I have SQL Server 2000 installed and get the same error message when connecting with Visual Studio 2005.

Any answer???

melvin
July 07, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

same problem, with sql2005. When I go to <Surface Area Config for Services and Connections> I get the following error: No SQL Server 2005 components were found on teh specified machine. Either no components are installed, or you are not an administrator on this computer (SQLSAC).

I've installed everything I could, and am an admin on the localhost. All client protocols are enabled in the config manager.

Help appreciated
melvin

Raphael
July 19, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

I have the same problem, when I tried to connect a software to the database server, but even if I allowed the remote connection in the utility Sql Server Surface Area Configuration Tool, this doesn't work. Anyone have an idea to solve this ?

--
Raphael

Mike Irwin
July 26, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

many thanks for helping me avoid another skull vs brick wall episode

Mike:)

Dale Beutel
July 31, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

This is the second time you have saved me much pain, we'll see how they go together, Re: Web Farming with the Network Load Balancing Service in Windows Server 2003


Best Regards
Dale


diegoap
August 03, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

THANKSSSSS !!!

Brent
August 23, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks, helped me find that needed to add in named pipes in addition to tcpip.

hiren
August 29, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

I am similar problem to I am running windows web server and I installed sql 2005 and getting no were I get same error message but I installed on my laptop and everyting is fine ??/

any suggestion

ses
September 01, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

This was really helpful. Thanks a million!

shilpa
September 21, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

i am using SQL Server 2005 & still facing this problem, plz. help me.....

bazai
September 22, 2006

# re: SQL Server 2005 doesn't take remote Connections by default


Damn, I just got the same problem on my XP-VS05 trying to connect to SQL 2000 via ado.net/asp.net. I tried the SQL analyzer and it worked fine except in asp.net. But it also work a few days ago in my ASP.net all sudden it stopped. :(

Quoted:
"I have visual studio 2005 application attempting to connect to a remote MS sql server 2000. And i get this 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) "

It is sql server 2000 and not 2005. but due to my Studio 2005 some setting is changed. Any solutions ?"

Foad
September 23, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

I was going crazy, I got the same problem and now it's fix. thanks and god bless

Chris
September 26, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks for the Help!! I would have beat my head against the wall trying to look for it in the SQL Studio Enviroment. Who would have thought and external turn would control this. Chalk this one up to another confusing and "Just to be Different" idea from MS....

Jane
October 13, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

I'm using VS 2005 and SQL Server 2000. also got this problem. Is anyone has any solution. Please help!!!


October 20, 2006

# SourceForge.net: Help

The world's largest development and download repository of Open Source code and applications

Steve
October 24, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

This one has kept me busy for days!!

I got this one from another site and it resolved EVERYTHING!!!

http://channel9.msdn.com/ShowPost.aspx?PostID=238075

My set up is Windows Server 2003, Visual Studio 2005 and SQL Server 2005.
NOTE WHAT I HAVE FOR THE SERVER "STEVE1\\STEVE"

SqlConnection cn = new SqlConnection("Server=STEVE1\\STEVE;Initial Catalog=AdventureWorks;UID=foo; password=foobarite;");


Hope this helps others :)

Steve
October 24, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Further to the above
My machine is called STEVE1
My SQL Server instance is called STEVE

Sorry that I did not get this into the original message.
regards Steve

Bob Mazzo
October 26, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks for that post Rick. More than 7 months later a guy like myself is struggling to add a remote subscriber to my Replication configuration. I ended up finding this post via google.com. What a godsend, dude !
Peace,
Bob

MF
November 09, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Sometimes the anti-virus program (Norton, ZoneAlarm..) will block the programs from accessing the server. Even though u might have clicked "Allow access" when the prompts appear, the program may already return with a TimeOut Exception or Failed to Connect Exception. So I think the best way is to add the application you are running into the list of Allowed Application to Access Internet list, and set it to something like Always Allow Access.

reload
November 18, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

Argh server 2003 with sql 2000 and i gett he same problem! oh for an answer!!!

geee
November 29, 2006

# Impossibly remote SQL 2005 express connections

I have spent the best part of 4 days trying to connect to SQL 2005 express from an winforms app! Yes , I have checked surface config and TCP/IP blah blah ...

I have trawled the net and spent countless hours on MSDN and many have tried to help. I have changed the passwords to strongly typed... I have recreated the login a zillion times,mapped to different databases , checked firewall settings, switched off firewall...tried different database roles, run every possible sp I can find.....attached the database using the connection string, attached using management studio and changing the connection string back, tried every possible connection string I can find, pored through the logs at evry turn ...

Then i did it all again and again and again.

Has anybody managed to connect from an application ever.. (excuse the cynicysm but I am at wits end)

The best error has to be this one proclaiming success and failure all at the same time:

2006-11-28 22:00:24.46 Logon Login succeeded for user 'FJUser'. Connection: non-trusted. [CLIENT: 192.xxx.x.x]
2006-11-28 22:00:24.46 Logon Error: 18456, Severity: 14, State: 16.
2006-11-28 22:00:24.46 Logon Login failed for user 'FJUser'. [CLIENT: 192.xxx.x.x]

Makes my confusion a little more bearable :-)

KD
December 13, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

What is/was the solution to get around this problem ?

Lucrecia
December 19, 2006

# re: SQL Server 2005 doesn't take remote Connections by default

THANK YOU Steve!!! I've been fighting against this for 2 days now, and thanks to you, it works now!!!

Colin
January 15, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Hi,

I've followed all of the advice posted on this site and many others; however I cannot get my server to allow remote connections. When installing, I created a named instance called SQL2005, instead of allowing SQL to just create a default instance. I don't know if this is correct or not?

My error message is: "An error 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 open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

In any case, please, if anyone out there knows how to configure SQL Server 2005 to allow remote connections it'll be greatly appreciated.

Thanks,
Colin.

Izzat
January 22, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

I am running SQL 2005 on Vista locally and it can not connect. I tried local connection only or local and remote. I tried the SQLPing and for some reason it does not find my SQL server, eventhough I have static IP address.

Reluctant to Change SQL DBA
January 23, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks, this helped cut down my time trouble shooting my connection issue. I agree what has been said in the article.

Sam
January 31, 2007

# Running MS SQL Server 2005 Express on Windows XP

I have IIS running on my Windows XP Pro workstation. I installed MS SQL Server 2005 Express. I want to create an asp file which connects to a database I created. I also created a login to the database. What would the connection string look like if you are simply connecting from you local workstation?

Todd Davison
May 03, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Loved your article and (minus the swearing) had said exactly the same things as you were mentioning prior to finding the solution. Adding all the security stuff tends to become counter productive because you just start blindly accepting everything. Something like like a network admin making people change thier passwords all the time, users will always forget a new password and end up just writing it down on piece of paper, just the opposite of what was trying to be accomplished.

This article was a HUGE help, I never would have thought to look in a different application for additional settings.

Thanks again!

Anil Choudary
May 14, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Folks. For those that are getting error while connecting from ASP.Net 2.0 to SQL 2000: reload, bazai etc

run "cliconfg"
click on "Alias" - make sure you do not have a named pipe alias created for the SQL server that you are connecting from the ASP app.

Happy coding..

ACN

Ryan
May 21, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

http://isc.sans.org/port.html?port=1433


Slammer worm anyone?

Still to this day there are port scans against sql server 2000 and 7. No company can have a perfect record with security, and it's dare I say intellegent to not have your server be able to be connected to any machine in the world on initial install. Installing a "new' install of sql 2000 "Gold" edition would get "hacked" nearly instantly if given a public IP and no firewall blocking incoming traffic in place (again causing the same thing this article complains about..no remote connection out of the box).

EVERY open source database that I've ever used follows this common sense approach of not allowing remote connections to every IP by default (btw searching that big massive config file for 127.0.0.1 usually brings you to the right line, for those of you who are scared of such things).

I commend Microsoft for doing the same, sure they could make it more intiutive to just find, but I welcome anyone using any server product with a lot of features (read: able to be exploited badly) to read deployment guides and get to know what they're doing first.

Microsoft has fabulous documentation about its core products, I think its a shame that people would rather poke around a UI for days on end instead of using well written resources such at these:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

There are several links in the above that show how to allow remote connections to SQL Server, some white papers are newer admittedly than this article but most of those are just new incarnations of a gold or sp1 paper with sp2 changes.

I'm glad for the new security focus from Microsoft..and I'm sure that all the people complaining about how things are now, were complaining about slammer, nimda, or code red (iis vunerabilities i know for the last two), are the ones who complain about the default behaviors now that help prevent such awful outbreaks.

zdena
July 21, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Hi guys, I have a problem with error: 40.

I made a Web page, and it works perfect on my local machine, everything is OK, until I uloaded page to the server, it reports this error:

ON MY LOCAL MACHINE EVERYTHING WORKS AS IT SHOLULD, BUT ONLINE WONT, PLEASE HELP

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)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): 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)]

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820

System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628

System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170

System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359

System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28

System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424

System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66

System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105

System.Data.SqlClient.SqlConnection.Open() +111

System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121

System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137

System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83

System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770

System.Web.UI.WebControls.Repeater.GetData() +50

System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232

System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53

System.Web.UI.WebControls.Repeater.DataBind() +72

System.Web.UI.WebControls.Repeater.EnsureDataBound() +55

System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12

System.Web.UI.Control.PreRenderRecursiveInternal() +77

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

I dont't Know what to do. Please HELP!

--------------------------------------------------------------------------------

Zdena

Flea#
August 30, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Steve thanks for the post; after going through Microsoft's knowledge base on how to fix the problem it would have been nice if they mentioned how to setup the Data Source! After doing this, it worked for me.

SqlConnection conn = new SqlConnection("Data Source=FLEA\\SQLEXPRESS;" +
               "Initial Catalog=AdventureWorks;Integrated Security=SSPI");

Mike Beadles
September 06, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

I am a Sr. SQL dba - I kind of like the new settings. MS has been blasted in the past for having too much open. This way, with a lock down, you have to make conscious decisions to open up your server.

So folks, document what you do and start writing up those SQL Server install checklists!

Shkokla
October 04, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

I had this problem, I fixed it this way.

Go to SQL server Configuration Manager under Configuration Tools, press once SQL Server Services, in the right pane you will get SQL services Right click on SQL Server (MSSQLSERVER) and Start.

Hope this helps to you to.

Ads
November 15, 2007

# re: SQL Server 2005 doesn't take remote Connections by default

Great!! New to SQL Server & didi not know what to do, found your webpage & followed your advice by using the SQL Server Surface Configuration Tool. Voila it's working again. Thanks.....

Jason Laury
January 15, 2008

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks. This was very helpful!!

VPT
March 03, 2008

# re: SQL Server 2005 doesn't take remote Connections by default

HI ,
I WAS GETTING FOLLOWING ERROR, WHILE TRANSFERRING TABLES FROM ONE SERVER TO ANOTHER

"Named Pipes Provider: Could not open a connection to SQL Server [53]. "

THANKS IT GOT FIXED UP WITH SURFACE CONFIGURATION MANAGER BY RESTARTING SERVICES

Samitha Nagahawatta
June 05, 2008

# re: SQL Server 2005 doesn't take remote Connections by default

i also faced same problem . finaly i was check whether SQl server service Runing ,,in my case
it was stopped.Restarting the service was give a solution for me.

tushar
June 12, 2008

# re: SQL Server 2005 doesn't take remote Connections by default

Thanx it helped a lot!

Ferreiro
July 14, 2008

# re: SQL Server 2005 doesn't take remote Connections by default

Thank you Steve for providing your connection string for the Windows Server 2003 and SQL Server 2005.

nagendra prasad
November 15, 2008

# re: SQL Server 2005 doesn't take remote Connections by default

weare using a static ip broadband line for running a remote server we are getting an error message"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. " . if we give proper port number we are getting a error message " a connecion was succesfully established with he server but an error occured during login process provider (TCP provider, error : 0 - an existing connecion was forcefully closed by the remote hos). if anybody know's a solution for the problem, ireques to get back to us.

thank you.

with regards

nagendra prasad

Sumeet T
February 04, 2009

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks Steve it helped me alot.

Akansh
July 09, 2009

# re: SQL Server 2005 doesn't take remote Connections by default

HI,

Thanks..It worked for me too.

ratheesh
July 16, 2009

# re: SQL Server 2005 doesn't take remote Connections by default

I am using windows server 2008, its a domain controller the client computers are not connecting with other sql server 2005

Tinu
July 17, 2009

# re: SQL Server 2005 doesn't take remote Connections by default

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://aspnetmembershipprovider.blogspot.com/2009/02/error-has-occurred-while-establishing.html

http://sqlservererror-info.blogspot.com/2009/07/error-has-occurred-while-establishing.html

Regards

Michos
January 19, 2010

# re: SQL Server 2005 doesn't take remote Connections by default

Hi,

This was very helpful.. Thanks 4 posting and yr right... MS just has way too many security issues... they need to ease up a bit...

shailesh
June 21, 2010

# re: SQL Server 2005 doesn't take remote Connections by default

is there any script through which we can check if remote connectioned are enabled or not on multiple servers.

Jen
July 27, 2010

# re: SQL Server 2005 doesn't take remote Connections by default

Thanks for the post! New to installing SQL Server so was told I had to create a shared dev server, and then couldn't connect to it. Thankfully found this post reasonably quickly :)

Sohag
November 03, 2010

# re: SQL Server 2005 doesn't take remote Connections by default

Hi,
i am a new user of SQL Server 2005. when i am trying to connect to server then the following
error message is shown.

TITLE: Connect to Server
------------------------------

Cannot connect to YBAI\SQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
How can i solve this?

Richard
July 06, 2012

# re: SQL Server 2005 doesn't take remote Connections by default

Still useful in 2012 - thanks Rick!

Slim!
November 29, 2012

# re: SQL Server 2005 doesn't take remote Connections by default

Hello Everybody. For those who tried everything, and nothnig works, please try to disable firewall :-)))))) I was trying to connect for 2 hours without luck and then I found this stupid (I am stupid :-)) reason. Hope it can helps somebody too. Have nice day it folks ;-)

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024