Post reply

Auditing Missing Records

  • January 24, 2022 at 5:03 pm

    #3976615

    Dear Friends,

    I have configured auditing to track successful logins on my SQL Server. However, I'm observing that I'm only getting records from 24hrs before disappearing. So I've configured retention for seven days, giving the information I'm looking for about who is doing log in.

    Is it possible that a heavily loaded server overwrites the auditing too much quickly ??

    Thank you very much for your ideas

    Best Regards

  • January 25, 2022 at 5:10 pm

    #3977067

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

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

  • January 25, 2022 at 7:10 pm

    #3977115

    With our system, we had turned on successful login logging on one of our systems and had a HUGE performance hit to the database.  The problem was caused by how the application worked with the database.  It would login to the database, execute the query, get the results and log out.  Then repeat for each query on the database.  A single operation could fire off 10 of these connect/disconnect situations and if we had 100's of users in the system at the same time, that is 1000's of logins per minute which was hurting performance to the point where end users were complaining.

    Now we just track unsuccessful logins (failed logins) and everyone seems happy on that system.

    Now, we have other systems that are hit less often and we could have successful login logging turned on, but we didn't see the benefit.  If someone can successfully log into a system, they can do what they need to do.  We care more when someone is not successful with logging in as it is an actionable item.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • January 25, 2022 at 7:56 pm

    #3977132

    Hi Mr. Brian,

    Thank you very much for your ideas

    I have found my auditing records redirected to files per day.

    The problem will be querying those files for users in charge of this.

    Thank you very much again.

     

     

  • January 26, 2022 at 10:30 pm

    #3977673

    Lou wrote:

    The problem will be querying those files for users in charge of this.

    BULK INSERT may be your friend here.

    --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)

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

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