Chia sẻ qua

Repeated login failures on an Azure SQL Virtual Machine

  • Bài viết

Lately, a user called in for support because he saw repeated failures like below, flooding the Windows event logs and errorlogs on Azure Virtual machines they have in their environment.

[Error] CredentialManagementOtherExceptionThrown: Login failed for user 'CONTOSO\SQLSERVER-0$'.
   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.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.IsSqlSupportedVersion()
   at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.CheckIfCredentialManagementSupported()

2016-03-22 19:37:36.99 Logon       Error: 18456, Severity: 14, State: 5.
2016-03-22 19:37:36.99 Logon       Login failed for user 'CONTOSO\SQLSERVER-0$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

 

 

From looking at the error, we can conclude a couple of things.  First, the login attempt came from local machine on that computer.  Secondly, the login was in format of doamin\computername$.   This tell you that some service running under a Windows Virtual Account was trying to access SQL Server.   For more references about virtual accounts, see https://msdn.microsoft.com/en-us/library/ms143504.aspx and https://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx.

This user did do some security tightening and removed “unnecessary” local accounts, one of which was the service account for  SQLIaasAgent.   SQLIaasAgent is configured as a service called SQLIaaSExtension running with NT Service\SQLIaasExtension.  It needs to login regularly to check for automated backup.   This service account requires sysadmin rights.

After adding “NT Service\SQLIaasExtension” and grant it sysadmin rights, the issue went away.  if you don’t want automated backup and patching, you can choose to disable or remove SQLIaaSAgent to avoid the error as well.

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

  • Anonymous
    May 02, 2016
    Hi,Can you clarify why adding the service account (NT Service\SQLIaasExtension) resolves the issue if the agent was attempting to log in under the machine account (DOMAIN\COMPUTER$) ? Shouldn't the machine account have been added instead? Not clear.
    • Anonymous
      April 24, 2017
      Hi PeteJust from my experience, NT Service accounts will need there permissions added directly into SQL. Adding the computer account doesn't fix the issue as the error will remain after. the NT service accounts often mask there identities as a DOMAIN\COMPUTER$ account, which originally proved difficult for troubleshooting.