SQL Server Error Log and Failed Logins

Question

We recently disabled a SQL account on one of our production servers at the request of the application owner, account no longer used apparently. Since disabling we have seen login failures in the SQL error log. See image below.

enter image description here

The app team cannot locate the process using it and have pushed back to me, I cannot see anything in sys.sysprocesses sys.dm_exec_sessions sys.dm_exec_connections

sp_whosisactive doesn't capture any code running either. I feel its almost a heartbeat process from the app server that checks every two minutes to make sure the server is up.

Is there any other ways to capture the activity, profiler isn't a quick and dirty option as this server is very temperamental. We are digging into the logs on the app side as well.

• Stockburn: App is still working even with the account disabled. Account has very basic permissions and know the server that it's coming from but the app team cannot work out what process is using the account. Nothing in the app servers event logs either from what they have said. Will try removing the permissions on the account see if that changes the error.
• Andreas Wolter: If there is no concern, why not temporarily re-enable it, and trace the exact principal with? You can capture all statements with Extended Events and a filter on principal_name. Roughly like so: CREATE EVENT SESSION [Trace_Principal] ON SERVER ADD EVENT sqlserver.sp_statement_completed( WHERE ([sqlserver].[server_principal_name]=N'LoginName')) GO - you can remove all permissions also and just trace statements_starting instead of completed. Lot's of option here.
• Stockburn: Thanks Andreas, I am doing just that now. Appreciate the code, will give it a crack now.

Answer 1

Since the account (login) has been disabled - you won't see anything from that login at sys.sysprocesses sys.dm_exec_sessions sys.dm_exec_connections naturally, and in sp_whoisactive as well.

You have evidence at your hand - records at error log which indicate Client IP of the server from which connection attempts (login failures) are being made.

Is there any other ways to capture the activity, profiler isn't a quick and dirty option as this server is very temperamental

Try running below query in your database server, it shows unsuccessful logins with details. It can help to discover a ClientProcessID of the process on your app server (10.x.x.x), which can point to app/website that is making login attempts:

SELECT trc.*
FROM fn_trace_getinfo(default) AS inf
CROSS APPLY fn_trace_gettable (convert(nvarchar(255), inf.value),default ) AS trc
WHERE inf.property = 2 AND inf.value IS NOT NULL
AND trc.EventClass= 20 
ORDER BY trc.StartTime DESC

More on failed logins here: https://eitanblumin.com/2020/03/09/finding-details-missing-sql-server-failed-logins-audit/

Also you can scan all files (configs, code) on your app server (10.x.x.x) using tools such as GrepWin to find out which files contain connection string with disabled account.

• Stockburn: Thanks @Aleksey You scanning of the files on the server has nailed it down, I had to push but they found a process that was still using the admin account.