I need to trace and monitor client login connections (successful and failure) to our important SQL servers (20 MSSQL Server). What method do you recommend?

Note: I think enabling "both failed and successful logins" to windows event logs may create large log files so in my mind it is last option. Also i think auditing may affect database performance so this is last option too.

  • Aside from the point about database performance, this looks like a duplicate of Audit login and logout – Max Vernon Jan 9 at 18:18
  • Stop using the UI to review your ERRORLOG and instead start taking advantage of the sp_readerrorlog undocumented stored procedure which makes searching through large ERRORLOG files significantly quicker. – John Eisbrener Jan 9 at 18:46
  • @MaxVernon I'm searching for other ways than SQL auditing for monitoring client logins, a software perhaps. – hamedb71 Jan 10 at 6:32

Anything that you do here will impact performance somewhere. But if it's a requirement then it's a requirement. The methods you've laid out in your question are the accepted, low impact methods of getting that.

You can mitigate the large log files by rolling over to a new file more frequently. By default it does that on every server restart, but you can add an agent job to run sp_cycle_errorlog every day, week or whatever. Then change the number of files to retain (limited to a max of 99).

--May need to adjust if running an instance.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 90

But that just tells you that the user in question logged on. If you need to do more with that information such as track what they did, then you will need to turn on Auditing (recommended if it's a legal requirement such as HIPAA/SOX; although only available in Enterprise) or you can just use Extended Events. Extended Events are as lightweight as you can get with tracking statements against the database.

There is one other option and that's a logon trigger, but I would strongly advise NOT to follow that route. If the logon trigger messes up for whatever reason then you could prevent anyone from logging into the database. But it only gives you slightly more information than you would get from turning on successful and failed login monitoring.

Not the answer you're looking for? Browse other questions tagged or ask your own question.