none
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

    Sunday, March 02, 2008 6:21 AM

Answers

  • 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

    Friday, July 17, 2009 11:59 AM
  • 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

    Tuesday, September 23, 2008 5:26 PM

All replies

  • 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
    Sunday, March 02, 2008 9:32 AM
    Moderator
  • 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" />

     

     

    Sunday, March 02, 2008 8:33 PM
  • 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.

    Monday, March 03, 2008 2:45 AM
    Moderator
  • >>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.

    Monday, March 03, 2008 3:22 AM
  • 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

    Tuesday, September 23, 2008 5:26 PM
  • 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

    Friday, July 17, 2009 11:59 AM
  • It works for me too in sql2000.
    Saturday, July 02, 2011 11:35 AM