35

I am seeing this in several situations and it is intermittent in our web based application connecting to SQL Server 2008 R2 serve back end. Users are coming across a point 2 point connection and seeing this on and off. Thought it was bandwidth issues until I started seeing it on terminal servers that are on the same core switch as this SQL server. I have checked remote connection enabled, Port 1433 is set correctly in Configuration for TCP/IP and the only thing I see that could be a cause is the timeout setting is set to 100000 in the remote connections rather than unlimited.

The error is

System.Data.SqlClient.SqlException (0x80131904):
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.ComponentModel.Win32Exception (0x80004005): The network path was not found

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.b__0()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
Dai
  • 141,631
  • 28
  • 261
  • 374
Sean Borner
  • 351
  • 1
  • 3
  • 3
  • 1
    This usually means you have network issues. I'd start by checking with your network engineer (assuming you have one). – Guy Lowe Oct 01 '14 at 06:37
  • I doubt it is a config issue, otherwise you wouldn't be `seeing this on and off`. – Eren Ersönmez Oct 01 '14 at 06:37
  • may be helpful: http://blogs.msdn.com/b/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx also does the situation gets better (temporarily) by restarting the server? if so, what is the max number of user connections allowed http://technet.microsoft.com/en-us/library/ms187030(v=sql.105).aspx ? – Arie Oct 01 '14 at 06:55
  • 3
    FYI: the 0x80131904 is not specific to this Sql error. You get for other SqlExceptions too (eg. Cannot insert duplicate key row in object..) – Simon_Weaver Mar 24 '17 at 00:01
  • Check SQL Server Configuration Manager to make sure you have all the required client protocols you need enabled. In my case I needed TCP/IP enabled.. – eyegropram Aug 18 '17 at 18:04

10 Answers10

5

i had the same issue. go to Sql Server Configuration management->SQL Server network config->protocols for 'servername' and check named pipes is enabled.

Spider man
  • 3,224
  • 4
  • 30
  • 42
3

I had the same issue.

Make sure that In SQL Server configuration --> SQL Server Services --> SQL Server Agent is enabled in SQL Server Configuration Manager.

This solved my problem.

Laura White
  • 188
  • 2
  • 9
Vijay Dodamani
  • 264
  • 1
  • 9
  • How do you get to the SQL Server Configuration page? I tried but could not access it. –  May 11 '21 at 04:50
2

In my case I needed to change the Server Properties-->Security-->Server authentication from

Windows Authentication mode

to

SQL Server and Windows Authentication mode

enter image description here

Amir M
  • 508
  • 1
  • 8
  • 28
1

Anyone who has this error, especially on Azure, try adding "tcp:" to the db-server-name in your connection string in your application. This forces the sql client to communicate with the db using tcp. I'm assuming the connection is UDP by default and there can be intermittent connection issues

Srivathsa Harish Venkataramana
  • 1,705
  • 21
  • 19
1

I meet the same exception, it throws message like "Database 'YYYY' cannot be opened. It is in the middle of a restore." This is expected behavior for me, the sql server is actually in the middle of restore.

The error number is 927. For all the error numbers, refer to https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645603(v=sql.105)?redirectedfrom=MSDN

how to catch this type of sql exception:

try
{
    await db.ExecuteProcAsync(...);
}
catch (SqlException e) when (e.Number == 927)
{
    ...
}
Diri Jianwei Guo
  • 51
  • 3
  • 1
    Nice one. Building on this idea one can build a handler to generate user friendly error messages. The list of error numbers in the link is quite comprehensive & helpful. Thank you. – Timothy Macharia Nov 24 '21 at 20:23
0

Check these steps.

  1. go to Sql Server Configuration management->SQL Server network config->protocols for 'servername' and check TCP/IP is enabled.
  2. Open SSMS in run, and check you are able to login to server using specfied username/password and/or using windows authentication.
  3. repeat step 1 for SQL native client config also
Chethan
  • 298
  • 1
  • 4
  • 17
0

Check you routes, the update on 9/28/2014 impacted us. We had to adjust our older servers and add new routes. Here is the article http://www.rackspace.com/knowledge_center/article/updating-servicenet-routes-on-cloud-servers-created-before-june-3-2013

Jason Soderberg
  • 1
0

See my post here

How are you? I had the same problem while i was trying connect to MSSQL Server remotely using jdbc (dbeaver on debian).

After a while, i found out that my firewall configuration was not correctly. So maybe it could help you!

Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Four exceptions must be configured in Windows Firewall to allow access to SQL Server:

A port exception for TCP Port 1433. In the New Inbound Rule Wizard dialog, use the following information to create a port exception: Select Port Select TCP and specify port 1433 Allow the connection Choose all three profiles (Domain, Private & Public) Name the rule “SQL – TCP 1433″ A port exception for UDP Port 1434. Click New Rule again and use the following information to create another port exception: Select Port Select UDP and specify port 1434 Allow the connection Choose all three profiles (Domain, Private & Public) Name the rule “SQL – UDP 1434 A program exception for sqlservr.exe. Click New Rule again and use the following information to create a program exception: Select Program Click Browse to select ‘sqlservr.exe’ at this location: [C:\Program Files\Microsoft SQL Server\MSSQL11.\MSSQL\Binn\sqlservr.exe] where is the name of your SQL instance. Allow the connection Choose all three profiles (Domain, Private & Public) Name the rule SQL – sqlservr.exe A program exception for sqlbrowser.exe Click New Rule again and use the following information to create another program exception: Select Program Click Browse to select sqlbrowser.exe at this location: [C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe]. Allow the connection Choose all three profiles (Domain, Private & Public) Name the rule SQL - sqlbrowser.exe

Source: http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/

Community
  • 1
  • 1
lmoglia
  • 494
  • 5
  • 13
0

I have had this same issue several times. The answer has been, use "::1" or "127.0.0.1" not "localhost". We often use ssh-tunnels to connect to remote databases.

Resturp
  • 51
  • 4
0

I had the same issue.

You should change DbContext: DbContext to DbContext: IdentityDbContext<YourEntity> in

Like this: enter image description here

Abbos
  • 1
  • 1
  • 2
    Please don't post code as images. Code should be pasted as text, and formatted using Markdown. (The toolbar in the Stack Overflow editor can help you with this.) Code within images is harder to read, less accessible, can't be copied, and doesn't show up in relevant searches. Please [edit] your post to include the code as text; this will help you get better responses, and help prevent your answer from getting deleted. – Jeremy Caney Aug 26 '22 at 00:23