none
Login failed for user 'sqlserviceaccount@abcd.com'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

    Question

  • Message

    Login failed for user 'sqlserviceaccount@abcd.com'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

    Error: 18456, Severity: 14, State: 5.

    I am getting multiple login failed errors for every 5 minutes in a server (in sql server error logs).

    The user 'sqlserviceaccount@abcd.com' is belongs to windows admin group and it is a service account for SQL Server Service and SQL Server Agent service.

    It is also added as a SA login under security in the SSMS (windows authentication). Default Database is Master.

    I ran the profiler, selected counter is audit failed logins, and I found that the connection is coming from SQLCMD (“ApplicationName” column in the Profiler trace).

    Can some suggest/explain me why this error is occurring and suggest me how to stop this error.

    (I hope disabling SQLCMD may not the only the solution).

    Thanks in advance for your valuable suggestion and responses.

    With Regards

    HYDBA

    Wednesday, November 13, 2013 3:21 PM

All replies

  • Several questions. You said 'sqlserviceaccount@abcd.com' was added as a SA. I assume you meant added as a member of the sysadmin fixed server role, as you can't add an SA.

    Error 18456 state 5 indicate that the Windows User ID is not valid. Has the Windows User account for sqlserviceaccount changed (such as being deleted and recreated)? If so, you would need to drop the login (with the old SID) and create the login again (with the new SID).

    You might want to use profiler and determine the computer that is submitting the request. Perhaps it is not authenticating the sqlserviceaccount properly.

    Disabling the sqlcmd account would have to be on the client that is submitting the request. That would probably mess up the other client tasks.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, November 13, 2013 5:08 PM
  • A name on the form 'sqlserviceaccount@abcd.com' looks like an SQL account. But since you say that it is a service accont what you have in SQL Server sounds like a Windows login. Have you check what the command line for the invocation of SQLCMD is?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, November 13, 2013 10:20 PM
  • Hi HYDBA,

    Looks like your windows application/service account is trying to access the sql server. Create the login with the user for this account on sql server to fix the issue.

    Regards,

    Kccrga

    Wednesday, November 13, 2013 11:09 PM
  • Check the password of the service account 'sqlserviceaccount@abcd.com' and try to connect with SQL server from the 'sqlserviceaccount@abcd.com' with password.

    Check if any linked server trying to connect with the SQL server may be i.e. throwing the alerts

    As, you discussed from SQL CMD (application) few audit failures are coming, in this case check login credentials used in that application that might be inappropriate

    Any error you found in event viewer?


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, November 14, 2013 1:33 AM
  • Can it connect using Windows authentication as abcd\sqlserviceaccount ?

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, November 14, 2013 4:31 PM
  • Hi All,

    I have checked again the below mentioned points.

    I can successfully connecting to SQL Server using the user 'sqlserviceaccount@abcd.com'(By login to server and connecting sql server from SSMS)

    It added as a member of the sysadmin fixed server role.

    The user is belongs to windows admin group.

    This same user (sqlserviceaccount@abcd.com) is also SQL Server Service Account. Stopped SQL Server services and started successfully.

    From the profiler I found that these failed login errors are coming from SQLCMD. And also found that the computer
    that is submitting the request is same server (Hostname in profiler showing my server name)

    It is clear that we also found from where the Failed Login errors are coming from. My question is
    where/How can I find this connection string/connecting app source to check the user and password.
    I want to stop these errors as I am receiving more than 4000 per day in SQL Server error logs.

    Thanks in advance for your valuable suggestion

    With Regards

    HYDBA



    • Edited by HYDBA Tuesday, December 03, 2013 3:08 PM
    Tuesday, December 03, 2013 3:06 PM
  • I would start by checking scheduled jobs in SQL Server Agent and Windows Task Scheduler.

    There is also a possibility that there is a stored procedure that performs xp_cmdshell to run SQLCMD as a loopback. That's not a very good thing to do, but bad practice is common in the SQL Server world. Finding this can be a bit like look for the proverbial needle in the haystack, but you could try:

    SELECT object_name(object_id)
    FROM   sys.sql_modules
    WHERE  definition LIKE '%SQLCMD%'

    in all users databases.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 03, 2013 10:47 PM
  • I ran the given query in all the Databases, no records were selected.(zero records found).

    Do you think any windows level patches or SQL related security patches will cause these errors… if any?

    With Regards

    HYDBA

    Wednesday, December 04, 2013 3:46 PM
  • I ran the given query in all the Databases, no records were selected.(zero records found).

    Of course, that was just a stab in the dark.

    Do you think any windows level patches or SQL related security patches will cause these errors? if any?

    No! It is a job or an application you have on your server. Since you say that it happens every five minutes, it appears to be someting that is scheduled.

    Here are a couple of ideas:

    o   If you only want to get rid of the error messages in the log, move or renamd SQLCMD - and hope that it is not used for legit reasons on the server.

    o   Grant login right to the account, set up a logon trigger which performs a WAITFOR for some minutes. Then use Process Explorer (www.sysinternals.com) and use the Process Tree view. Then you should be able to find the parent process to SQLCMD.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 04, 2013 7:41 PM