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.

"surface area" ? Whose brainchild was this?

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

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?! :>

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!

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.

One word... Unbelievable

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

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

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

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

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

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

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...

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.

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.

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 ?

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

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

Any answer???

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

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

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

Mike:)

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

THANKSSSSS !!!

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

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

This was really helpful. Thanks a million!

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

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 ?"

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

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....

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

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 :)

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

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

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.

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

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 :-)

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

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

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.

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.

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

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?

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!

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

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.

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

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.

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!

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.

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.....

Thanks. This was very helpful!!

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

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.

Thanx it helped a lot!

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

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

Thanks Steve it helped me alot.

HI,

Thanks..It worked for me too.

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

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

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...

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

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 :)

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?

Still useful in 2012 - thanks Rick!

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 ;-)