Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'dbName'. [CLIENT: <local machine>]

Question

I'm getting this error every night at 8:33PM. The NT AUTHORITY\SYSTEM is also added to the SQL Server Security/Login with Sysadmin permission.

Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'dbName'. [CLIENT: <local machine>]

This error shows up in the default SQL Server instance; however, the dbName database is in the sqlServer/2ndInstance. So this also puzzles me why NT AUTHORITY\SYSTEM failed login shows up trying to login into a second instance from the default instance.

Answer 1

Someone is trying to login to the default instance with the wrong default database name.

Answer 2

Assigning sysadmin to NT AUTHORITY/SYSTEM sounds like a terrible idea to me. That is the user that will be used when someone is trying to use a linked server and Kerberos does not work. Big security hole!

And, yes, the login error you see, could be someone or something that attempts to access the database in question through an incorrectly configured linked server.

Answer 3

I've looked at the 2ndInstance and it has the exact same failed login for user "NT AUTHORITY\SYSTEM". I don't think it's a person. The failed login is at the same time every day.

Answer 4

I found this thread but I already assigned sysadmin to "NT AUTHORITY/SYSTEM" and it still shows the same thing.

Answer 5

Hi CharlieLor-4693,
In addition, you can use try to SQL Server audit to collect failed login to see which app is trying to connect to the databases and failed to login.

For example:

CREATE SERVER AUDIT [test]  
TO FILE   
(	FILEPATH = N'C:\temp\'  
	,MAXSIZE = 0 MB  
	,MAX_ROLLOVER_FILES = 2147483647  
	,RESERVE_DISK_SPACE = OFF  
)  
WITH  
(	QUEUE_DELAY = 1000  
	,ON_FAILURE = CONTINUE  
	,AUDIT_GUID = '965fab9d-f65e-46cf-99e9-ad66e915146d'  
)  

ALTER SERVER AUDIT [test] WITH (STATE = ON)  
    GO  
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]  
FOR SERVER AUDIT [test]  
ADD (FAILED_LOGIN_GROUP)  
WITH (STATE = ON)  
GO  

You can view audit log by right click on the Audit -> “View Audit Logs”.
Please refer to How to Capture Failed Logins using SQL Server Audit and SQL Server Audit which might help.
Best Regards,
Amelia