Post reply

Audit Login Usage

  • January 3, 2020 at 5:19 pm

    #3712186

    I'm wondering what methods others have used to trace for logons being used.  Some of the thoughts I had and saw were:

    1. Turn on success and failure auditing for logons -- and try to leverage something like this post: https://www.sqlservercentral.com/scripts/failed-and-successful-login-auditing-using-xp_readerrorlog
    2. Attempt to use a logon trigger to capture logons as they come in, but really only looking to capture them once a day
    3. Common criteria compliance enabled -- supposedly, that enables the column last_successful_logon of the sys.dm_exec_sessions dmv, but that seems like something that would need to be queried regularly since that is not kept for historical purposes.

    The pro and con of #1 above would be that we could scan the log for successful logons without having to do much else configuration-wise, but the issue I see with that is we would have to somehow keep tabs on the event date to try and prevent over-scanning the logs, or overwriting if something wasn't found.

    The logon trigger (#2 above) method seems like it would create a lot of overhead executing with every logon, and a former colleague of mine attempted to do this method, but ran into a blocking scenario resulting from an application seemingly not using connection pooling correctly, and the crazy amount of blocking resulted in issues for the application, so we dropped that logon trigger to troubleshoot further, but then that solution felt wrong.  Then again, having logon triggers enabled

    In all cases, individual accounts that are part of Active Directory groups seem like they would pose a challenge in that we would have to then track down the individual account, see if said account was part of an AD group that is present on the instance, and then ultimately determine that the AD group is being used.

    Can anyone reference any articles and/or scripts that provides what I'm looking for before I try to develop something?

    Thanks in advance!

  • January 4, 2020 at 6:10 pm

    #3712390

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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