Post reply

Writing successful logins to a database table

  • January 31, 2020 at 7:45 pm

    #3722137

    Simply stated, I would like for every login attempt (successful or failed) to be written a database table.  It will need to include the HostName.  A Logon Trigger will do it - but I'm concerned about the overhead and accidently causing a logon failure due to a trigger issue.  Profiler will do it, but it has it's own noticeable footprint - and if the trace dies, it doesn't restart automatically.  The Audit feature doesn't write to a table (and it doesn't appear to capture HostName).  Extended Events don't seem to be able to write to a table without manual intervention.

    I have SQL Server 2008 R2 - SQL Server 2017 - but would be OK with something that worked on the latest versions.  can anyone suggest what they've done?

    TIA

  • January 31, 2020 at 8:03 pm

    #3722143

    We have been using logon triggers to detect the users trying to connect to a database from a specific application and server in production for more than 10 years.

    I don't think it has any overhead actually, ours work the next way: if the user isn't from X servers or X applications it will deny the connection for that specific user, we don't actually write in a table but we send an email to the DBA team.

    If it's only writing to a table, i dont think you'll have any overhead.

  • January 31, 2020 at 8:14 pm

    #3722145

    Thanks AS - I am likely to try Logon Triggers, since other options are lacking. I'm basically trying to verify all of the hosts that access my servers using SQL authentication (so I can change some passwords).  I'm really nervous that my trigger will fail for some reason and prevent a user from logging in!

  • February 3, 2020 at 12:57 pm

    #3722556

    Try in a testing environment before using it in production, as long as you follow procedures, create it on Development\QA environment, leave it there for a couple of days and see how it go.

     

  • February 3, 2020 at 1:05 pm

    #3722557

    You can also as alternatives, use extended events and capture the logon session event.

    You can also switch the SQL default login method from being failed logins, to successful and failed logins, this needs a service restart to take effect so will need a downtime.

    Another approach would be to poll connections every 5/10/15 seconds, done this in the past by using sp_whoisactive and recording the session in a table.

  • February 3, 2020 at 1:43 pm

    #3722591

    Please see this topic for how to do the manual intervention you mentioned as being necessary for Extended Events.

    John

  • February 3, 2020 at 2:47 pm

    #3722617

    Thanks John!  Will this (very nice) solution work for XE?  It seems to be tailored for SQL Audit.  I apologize if I'm missing something...

  • February 3, 2020 at 2:55 pm

    #3722619

    I can't see why not, but you may need to make a couple of tweaks.  Alternatively, just use SQL Audit instead of Extended Events.

    John

  • January 31, 2021 at 11:32 am

    #3839287

    Perhaps it will be a good solution.

  • January 31, 2021 at 2:58 pm

    #3839323

    Ovvi wrote:

    Perhaps it will be a good solution.

    Perhaps WHAT "will be a good solution"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 31, 2021 at 6:37 pm

    #3839383

    Jeff Moden wrote:

    Ovvi wrote:

    Perhaps it will be a good solution.

    Perhaps WHAT "will be a good solution"?

    Options:

    1. Spammer
    2. Person with too much time on their hands
    3. 1 and 2

Viewing 11 posts - 1 through 10 (of 10 total)

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