Audting User Logins - How might you do it.

Question

Audting User Logins - How might you do it.

NicHopper

SSCrazy Eights

Points: 9090
More actions
January 11, 2018 at 5:19 am

#402830

Hi,
So as part of a GDPR requirement I need to audit logins to a database as follows;
1. A user tries to access the database but they dont have permissions (Failed Login).
2. A login attempt for the database that occurs out of normal office hours (8am-6pm) by any user.
I need to record, the user name, the host machine that made the request.To make things a bit more interesting when this happens I then need to trigger an email to be sent to a separate user.
So my question is what do you think the best way to do this is?
I could use SQL Server/Database Audit specifications but they don't write to tables directly so I'd have to have SQL read the file, this makes real time alerting trickier.
Alternatively I could use a server side profiler or XE events and have the results write to a table then a trigger fires and email when a row is inserted (or use a data driven subscription in SSRS)
Another option may be to use a TRIGGER on login to record what I need.
Perhaps there is another way that I've not thought of yet.
I'm not so focussed on the email alerting side of things just yet as for now I just need to record the data in table.
Any ideas on how you may do this. Initially I was all about the audit specification but now I'm thinking that the XE may be a better way.
Let me know what you think.
Thanks,
Nic
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 1

1. Security Audit - https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
2. Logon triggers - https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

Thank you for the links.

My question is not so much how to actually do it but instead which approach you may take, triggers, profiler, XE, or Database audit. Its highly advantageous to get the data in a table, AS XE and database audit write to files then I have to deal with how I get those into a table. Just seems to me like that becomes more complicated, when instead I could use a server side profiler to write the data to a table directly.

So what would you approach be? I don't think there is a right or wrong answer to be honest.

Thanks,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 3

NicHopper - Friday, January 12, 2018 4:38 AM
... instead I could use a server side profiler to write the data to a table directly.
So what would you approach be? I don't think there is a right or wrong answer to be honest.

Server side profiler is not that robust and have more performance impact on the database. Also that feature is deprecated. In this case you may consider the XE instead.
You can read security logs in the tabular format using something like this:
SELECT * FROM sys.fn_get_audit_file(@log_dir, default, default)

I still recommend using the security audit for the purpose you have mentioned. It is designed for that purpose ;).

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 4

Hi,

Thanks again for the reply. I think your correct, database audit is the way to do this.
I do have a question about it though if I may though please. So I created the server audit and configured it to write files locally to the database server, I then created the database audit (as follows);

CREATE DATABASE AUDIT SPECIFICATION [LoginActivity]
FOR SERVER AUDIT [SecurityAudit]
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_LOGOUT_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP)
WITH (STATE = ON)

Both start up successfully and a file gets created in the folder I specified.
My question is though am I monitoring the correct events above as when I run the command

SELECT * FROM sys.fn_get_audit_file('E:\MSSQL\SQLREPORTS\', default, default)

I get an empty result set back, despite me doing multiple logins (successful and failed to the database in question).

Am I monitoring the wrong events or missing something else. Opening the file in notepad shows only the columns which indicates the trace is not recording what I want it to;

Logins of any attempt (successful or failed).

So any idea where I may be going wrong?

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 5

NicHopper - Monday, January 15, 2018 2:56 AM
So any idea where I may be going wrong?

the path should be something like this - E:\MSSQL\SQLREPORTS\*.sqlaudit if you use the default extension... Check the official documentation - https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql