I have a domain user account that I use to run a particular ASP.NET web application it is NETOPS\websvr. This domain account is setup in the SQL Server in question and is in the sysadmin role. However, I randomly get errors stating that the login failed for he user. Sometimes it is on this database and others it is on a different database on the same server.
I have been searching the web for the past several hours trying to find an answer for this and I am no closer to an answer than when I started so I thought I would ask here to see if anyone has any ideas. Below is a output from my instance of Stackexchange.Exceptional
Exceptions Log: WebSync Cannot open database "alsmi" requested by the login. The login failed. Login failed for user 'NETOPS\websvr'. System.Data.SqlClient.SqlException System.Data.SqlClient.SqlException (0x80131904): Cannot open database "alsmi" requested by the login. The login failed. Login failed for user 'NETOPS\websvr'. 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.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) 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 Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText) at GetPatchLevel.ProcessRequest(HttpContext context) ClientConnectionId:e33983c0-da22-4050-a894-55274b46f645 Full Trace: ProcessRequest at offset 424 in file:line:column :0:0 System.Web.HttpApplication.IExecutionStep.Execute at offset 342 in file:line:column :0:0 ExecuteStep at offset 70 in file:line:column :0:0 ResumeSteps at offset 1085 in file:line:column :0:0 BeginProcessRequestNotification at offset 96 in file:line:column :0:0 ProcessRequestNotificationPrivate at offset 187 in file:line:column :0:0 ProcessRequestNotificationHelper at offset 727 in file:line:column :0:0 ProcessRequestNotification at offset 31 in file:line:column :0:0 MgdIndicateCompletion at offset 0 in file:line:column :0:0 ProcessRequestNotificationHelper at offset 1110 in file:line:column :0:0 ProcessRequestNotification at offset 31 in file:line:column :0:0 occurred 2 hours ago on web1 (delete) Server Variables CONTENT_LENGTH 0 HTTP_X_FORWARDED_FOR 0.0.0.0 HTTP_X_SSL_CIPHER AES128-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA LOCAL_ADDR 192.168.1.117 REMOTE_ADDR 192.168.1.216 REMOTE_HOST 192.168.1.216 REQUEST_METHOD GET URL /Services/GetPatchLevel.ashx URL and Query http://website.net/Services/GetPatchLevel.ashx?dbname=alsmi&username=0000&pwd=0000&clientid=0000 Custom SQL-Server sqlserver1 SQL-ErrorNumber 4060 SQL-LineNumber 65536 QueryString clientid 0000 dbname alsmi pwd 0000 username 0000 RequestHeaders Host website.net X-Forwarded-For 0.0.0.0 X-SSL-cipher AES128-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA Exceptional 1.0.0.111 SQL Error Store Server time is 1/30/2015 5:50:04 PM
Here is the code that generates the connection string for the database.
public static string GetConnString(string DBName)
{
string retval = "";
WebSyncDatabase db = WebSyncDatabase.GetDatabase(DBName);
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = db.DBServer;
csb.InitialCatalog = db.DBName;
csb.IntegratedSecurity = true;
csb.ConnectTimeout = 25;
csb.MaxPoolSize = 150;
retval = csb.ConnectionString;
return retval;
}
Here is the data access code. The SqlHelper.ExecuteScalar is from Microsoft Data Access Application Block for .NET code unmodified. he below code literally works tens of millions of times per day for us with random exceptions.
string connString = WebSyncUtils.GetConnString(dbName);
switch (context.Request.HttpMethod.ToUpper())
{
case "GET":
int retval = 0;
try
{
retval = (int)SqlHelper.ExecuteScalar(connString, CommandType.Text, "SELECT PatchLevel FROM PatchStatus");
}
catch (Exception ex)
{
WebSyncUtils.LogMessage(string.Format("Get Next Patch Level for {0} {1} {2}", dbName, userName, pwd), ex);
}
context.Response.Write(retval);
break;
case "POST":
break;
default:
break;
}
more code sample for Methodman
/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="connectionString">A valid connection string for a SqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="connection">A valid SqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid SqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Execute the command & return the results
object retval;
try
{
retval = cmd.ExecuteScalar();
}
finally
{
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
}
return retval;
}
/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="connection">A valid SqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">A valid connection string for a SqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
Domainperhaps the Login is locked becasue of too many bad attempts which would make me wonder if the password expired or is not set tonever expires- MethodMan