Writing successful logins to a database table

Question

Writing successful logins to a database table

pfousek

SSC Enthusiast

Points: 175
More actions
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

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

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

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 1

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.

pfousek SSC Enthusiast Points: 175 More actions · Answer 2

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!

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 3

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.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

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.

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

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

John

pfousek SSC Enthusiast Points: 175 More actions · Answer 6

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

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

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

Ovvi Grasshopper Points: 17 More actions · Answer 8

Ovvi

Grasshopper

Points: 17

January 31, 2021 at 11:32 am

#3839287

Perhaps it will be a good solution.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

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)

irgp SSC Journeyman Points: 80 More actions · Answer 10

Jeff Moden wrote:

Ovvi wrote:
Perhaps it will be a good solution.
Perhaps WHAT "will be a good solution"?

Options:

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