Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server - How to create a login audit using instance logs

Post Views 811 views
This post is part 21 of 21 in the series Security and Auditing
Reading time 4 minutes

Hey guys!
In this post, I would like to show you how to create a login audit using instance logs, which is a native SQL Server option and does not require the creation of any additional resources such as trigger, Extended Events, Audit, etc..

I already published the article SQL Server - How to implement login auditing and control (Logon Trigger) using a trigger and it turned out to be a very interesting solution, but which, like all login triggers, can pose some risks if it is not well implemented and prevent user logins.

I also showed a similar solution in the article SQL Server - How to avoid brute force attacks on your database, but the focus was only connection failures by nonexistent user or incorrect password.

How to enable auditing of logins in SQL Server

To activate auditing of SQL Server logins, first open the instance in the “Object Explorer”, right-click and select the “Properties” option

Click on the “Security” tab and then in the “Login Auditing” category, check the option “Both failed and successful logins”

Another way to enable this is using this T-SQL script:

After making this change, restart the SQL Server service through SQL Server Configuration Manager.

Important: In case the SQL Server Configuration Manager is not appearing for you in the start menu, read the article SQL Server Configuration Manager gone? Learn how to retrieve the shortcut.

How to read collected audit data

To read the collected audit data, we will use the internal procedure xp_readerrorlog, which reads data from SQL Server logs.

To learn more about this procedure and other internal SQL Server procedures, read the article The Undocumented SQL Server Extended Procedures.

Below is the script you can use to capture this data from SQL Server logs and store it in a physical table in your database, to keep history.

Resultado da auditoria:

With this data, you can now create various monitoring, analyses, follow the number of logins, check if you are having a brute force attack or know if a certain user is logging in or not in the database.

I hope you enjoyed this tip and until next time!