Hi there, i was just wondering if anybody knows how to detect what is calling the sa login on my sql server 2005. I have a stack of websites and i swear i configured these with a different user id and password but my event viewer logs tell me that something is constantly trying to login using the sa credentials. Any help would be appreciated.
views:
40answers:
2
+5
A:
run profiler, create a new trace, use event audit login and audit login failed you can filter for username sa if you want
see image below
See also this post by onpnt: To SA or not to SA
you could also use a Logon Trigger but that will only fire if the authentication is succesful
SQLMenace
2010-01-24 22:03:33
Just as an aside, the application name and host name you will see here are the same that sys.dm_exec_sessions uses, and are subject to the same warnings about spoofing.
Aaron Bertrand
2010-01-24 22:13:50
Aaron, you are of course absolutely right
SQLMenace
2010-01-24 22:20:23
+2
A:
If they are successfully connected then you can look in sys.dm_exec_connections, this will at least tell you the client_net_address (IP address as reported to Windows). You can also look at PROGRAM_NAME and HOST_NAME in sys.dm_exec_sessions, but be aware that these are not necessarily populated, and are easy to spoof.
Aaron Bertrand
2010-01-24 22:04:39
hi mate, thx for the help, however, doing a select using the "PROGRAM_NAME" and "HOST_NAME" properties of sys.dm_exec_connections tells me that these yield invalid column names in the query result
Johnny Lamho
2010-01-24 22:23:37