Question

sudhakar.mandava on Thu, 17 Jul 2014 16:48:07


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.


Sponsored



Replies

Shanky_621 on Thu, 17 Jul 2014 17:41:46


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/

sudhakar.mandava on Thu, 17 Jul 2014 17:49:20


I already did that and found it is trying to access master database.

Shanky_621 on Thu, 17 Jul 2014 19:07:48


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

Ashwin Menon on Thu, 17 Jul 2014 19:23:01


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

sudhakar.mandava on Thu, 17 Jul 2014 19:50:58


I am sorry i didn't mention the SQl server version earlier, it is  SQl server 2008R2 standard SP2

Ashwin Menon on Thu, 17 Jul 2014 19:55:39


Sudhakar I think the SQL Version dont really matter. Its more of to SCOM/SCCM monitoring tools trying to access the databases to monitor.

RAJU RG on Fri, 18 Jul 2014 00:27:56


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

sudhakar.mandava on Fri, 18 Jul 2014 14:46:15


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.

Shanky_621 on Fri, 18 Jul 2014 19:41:37


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 .

tracycai on Tue, 22 Jul 2014 01:50:13


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.