SQL Server logon audit

Question

SQL Server logon audit

Yogeshwar Phull

Default port

Points: 1434
More actions
January 29, 2018 at 6:02 am

#326628

Hello everyone,
We're looking to setup monitoring for all the logins that log on to a SQL server instance. The information we need to capture is application name, login name, client host name, client host ip address. We had used logon trigger to get this information in the past but we no longer want to use logon trigger for this. I looked for options like SQL Server audits, extended events, server side traces, event notifications etc. but I am yet to decide on the best approach for this. Amongst the ones specified above, Can anyone suggest what the best approach would be? Is there any other approach apart from these? We're using SQL Server 2016.
Thanks in advance!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Third-party tool is another option. Only you know your system, and only you know why you rejected logon triggers, so it's difficult for us to advise you on what's best given the information we have.

John

Yogeshwar Phull Default port Points: 1434 More actions · Answer 2

Thanks for replying, John. Basically we wanted to stop using logon trigger because a minor mistake with the configuration of logon trigger can lead to locking down the whole instance. So we did not want such a high risk option for achieving something simpler. I was looking for suggestions on what could be better amongst the approaches i specified in my question and if there could be something else as well.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

If you test properly, you should have nothing to fear. But if it's a case of once bitten, twice shy, why not just capture logins in the errorlog? It doesn't tell you the application name, but that can be spoofed anyway. You'd need to make sure you've got enough space on disk to accommodate the extra rows in the errorlog, and make sure you recycle the log frequently. The other things you mentioned are also possibilities, but, as I said before, we really don't have enough information to make any recommendations. Each approach has its own level of complexity, performance hits and network and disk space requirements.

John

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 4

What about capturing via xp_Readerrorlog and keep pushing it to a table or Configure Audit log .

Boils down to if you want notification for login ( Success or Failure ) or you want to store all the information on the login .

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 5

Don't use a logon trigger. Go with an extended event:

https://voiceofthedba.com/2015/12/16/tracking-logins-with-extended-events/

K. Brian Kelley
@kbriankelley