SQL Server 2008 - Intermittent error 18456 with sa

0

I have an application that uses a group of SQL 2008 databases. For some reason, the main database intermittently throws a series of 18456 errors (Login failed - failed to open the explicitly specified database). This causes the application to wig out and often crash. In the SQL server logs, the server throws dozens of events in a short period of time, typically like this:

Login succeeded for user 'sa'. Connection made using SQL Server authentication. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'sa'. Reason: Failed to open the explicitly specified database '[my_db]'. [CLIENT: <local machine>]

Whenever this happens, it happens repeatedly. I count 73 total events over a 4 minute period in the last incident.

Here is what the application is logging:

Cannot open database "[my_db]" requested by the login. The login failed.

Login failed for user 'sa'.

System.Data.SqlClient.SqlException (0x80131904): Cannot open database "[my_db]" requested by the login. The login failed.

Login failed for user 'sa'.

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open()

The connection string is as follows:

Data Source=localhost\SQLEXPRESS;Initial Catalog=[my_db];Persist Security Info=True;User ID=sa;Password=[password]

The sa login is mapped to dbo, of course, and its default database is set to master. I've checked that the dbo login has the db_owner role assigned on this database. The permissions on the ldf/mdf files are default and the logged in user has access to them. Those files are also exempted in the anti-virus software so they're not being locked up by that.

What the heck am I missing here?

c#
sql-server
sql-server-2008
windows-7
asked on Stack Overflow Feb 28, 2020 by Olylo

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0