When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not a

Question

My environment:

 

Home PC

Vista Ultimate

SQL Server Developer 2005

 

I'm about to go absolutely nuts.  I've tried so many things including:

 

1) Of course making sure Remote connections and both TCP/IP and named pipes are enabled for SQL 2005

2) Verified my connection string works.  I even tested it in Enterprise Library using the tool and it connects successfully via windows authentication

3) Made sure of course my logged in user is a member of the administrator group and that my user in SQL 2005 logins has db_owner and full rights to the DB I am connecting to.  I also made sure the NT AUTHORITY as well as the 3 sql service users have db_owner rights as well as the adminstrator in the SQL Server logins all have db_owner to the DB

4) Started the SQL Server Browser service, SQL Server DB engine, and SQL Server Agent

5) Made sure my site is setup right in IIS 7.  I checked the connection string in there as well as it shows up in IIS7 in one of the sections.  Using Windows authentication and it is enabled for my site

6) Tried running the .net site using both localhost (IIS) and also tried using the Visual Studio built-in web server.  Both give this message still

 

Error Details:

 

Server Error in '/' Application.

 

---

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) +800131 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +357 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +494 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.Web.SessionState.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) +79 [HttpException (0x80004005): Unable to connect to SQL Server session database.] System.Web.SessionState.SqlSessionStateStore.ThrowSqlConnectionException(SqlConnection conn, Exception e) +227 System.Web.SessionState.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) +349 System.Web.SessionState.SqlSessionStateStore.GetConnection(String id, Boolean& usePooling) +285 System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem) +178 System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +476 System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

---

Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

Answer 1

Check if you have done the following,

1. Ensure that you have enabled remote connections
2. Ensure that Sql Browser is started
3. Try connecting using <<Servername, port number>>
   
4. Ensure that you have created exception in firewall for Sqlservr.exe

Refer these links for more info regarding this issue,
http://support.microsoft.com/kb/914277
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

- Deepak

Answer 2

Pardon me (and possibly my snide reply), but did you actually read my post?  I told you that 1 & 2 have been taken care of.  Obviously I've enabled remote connections.  I started the browser.  Please re-read.  I'll check out the links as well that you posted. 

 

Also, I don't even have my firewall turned, I never use that.  It's off.

 

I've tried the links.  The first 2 I have already gone through extensively and tried the suggestions with no luck in the past.  In fact I even posted my problem in the second link you gave me with no help.

 

Here's my connection string created from Enterprise Library 3.1.  When I test this from the Enterprise library tool, it shows successful test connection.

 

<add name="Connection String" connectionString="Data Source=(local);Initial Catalog=Elbalazo;Integrated Security=True"

providerName="System.Data.SqlClient" />

 

 

Answer 3

That certainly could have been perceived as snide.  Everybody misses details when answering a post now and again.  I think I did it twice this weekend.  Not trying to sound preachy...but in posting an answer to your question he was doing his best to help you out.

 

A few suggestions:
  - Fiddle with the connection string a bit (if you haven't already).  Try specifying a servername instead of (local).  Also try explicitly specifying "Network Library=DBMSSOCN" (tcp/ip).

  - Have you tried posting it into the ASP.net data access forum?  It strikes me that if the connection string is working fine from outside of ASP, but not from within ASP, then the issue isn't SQL Server itself, but something in the way that the ASP page is connecting.

 

Hope this helps.

Answer 4

>>Everybody misses details when answering a post now and again

 

Fair enough, just looked though like a blanket post which is the way I felt.  I am trying to be as detailed and effective as possible which is why I stated what I've tried already so I could get much more useful responses rather than a general blanketed try this or that which I already have.

 

Thanks, I'll try your two suggestions.

Answer 5

I got that error too.  Then I fixed the problem in the web.config file... the problem was the connection string, just like you mentioned.  I changed the data source to be just a period (".") and it worked.  Here's the whole connection string that now works fine for me:

 

<add name="BayerCMT2DB" connectionString="data source=.;initial catalog=BayerCMT2;integrated security=true" providerName="System.Data.SqlClient"/>

 

 

Donna

Answer 6

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

Answer 7

It works for me too in sql2000.