none
Login failed for user

    Question

  • I am getting the below error in SQl server error log for every 15 min. I ran profiler to  find out what database it is trying to access, found that it is trying to access master.

    I am getting this error because I removed NT AUTHORITY\SYSTEM from SYSADMIN group because all my SQl server services are running with domain account and i don't want to give SYSADMIN for that account, is there any way that we can eliminate the error.

    Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xx.xx.xx]

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

    Thursday, July 17, 2014 4:48 PM

Answers

All replies

  • Hi,

    Please set a profiler trace to actually find out which program or request was using that account

    http://bennettadelson.wordpress.com/2012/08/22/leveraging-sql-profiler-to-troubleshoot-18456-events/


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Thursday, July 17, 2014 5:41 PM
  • I already did that and found it is trying to access master database.
    Thursday, July 17, 2014 5:49 PM
  • Are you running any monitoring tool like SCOM . Master is default database to which Login is connecting what is application name which is using this Login You need to find more details

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Thursday, July 17, 2014 7:07 PM
  • Check this post as well. http://social.technet.microsoft.com/Forums/systemcenter/en-US/657b34ab-7b2f-40c2-8ac2-8105914d442f/sql-2012-login-failed-for-nt-authority-system-reason-failed-to-open-the-explicitly-specified?forum=operationsmanagergeneral

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, July 17, 2014 7:23 PM
  • I am sorry i didn't mention the SQl server version earlier, it is  SQl server 2008R2 standard SP2
    Thursday, July 17, 2014 7:50 PM
  • Sudhakar I think the SQL Version dont really matter. Its more of to SCOM/SCCM monitoring tools trying to access the databases to monitor.

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, July 17, 2014 7:55 PM
  • Check these similar posts for Error: 18456, Severity: 14, State: 38

    http://social.msdn.microsoft.com/Forums/en-US/0f735967-c6bd-4996-9e4d-0f1986dd05a3/how-to-resolve-this-error-18456-severity-14-state-38-sql-server-2008?forum=sqlexpress

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b26347c-308d-40a5-961b-a627f6cbe54c/error-18456-severity-14-state38

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ddb1e384-9a31-4fbb-bb0f-020eb02b0a2a/18456-error-state-38?forum=sqlsecurity


    Raju Rasagounder Sr MSSQL DBA

    Friday, July 18, 2014 12:27 AM
  • I can see these failures from SCOM, but when I try to change the Microsoft Monitoring Agent from local system to domain account the service is not coming up which was causing alerts with NT authority.
    Friday, July 18, 2014 2:46 PM
  • I can see these failures from SCOM, but when I try to change the Microsoft Monitoring Agent from local system to domain account the service is not coming up which was causing alerts with NT authority.
    So when its not coming online it must be throwing some error can you please post complete error here. Does domain account has necessary privileges .

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, July 18, 2014 7:41 PM
  • Hi,

    The error message is thrown out since the login has insufficient privilege. In this case, the NT AUTHORITY\SYSTEM has not sysadmin privileges because it is moved the permission.
    I agree with Shanky. You need to address Microsoft Monitoring Agent issue first. You can get the help from System Center Operations Manager forum:
    http://social.technet.microsoft.com/Forums/en-US/home?forum=operationsmanagergeneral

    Reference:
    http://social.technet.microsoft.com/Forums/en-US/23f6b6cb-ec41-4117-8613-26d24c948827/login-failed-for-user-username-reason-failed-to-open-the-explicitly-specified-database

    Hope it helps.


    Tracy Cai
    TechNet Community Support

    Tuesday, July 22, 2014 1:50 AM
    Moderator