SQL Server - How to implement audit and control of logins (Logon Trigger)

Reading Time: 8 minutes

Hello people,
Good afternoon!

In this post I will demonstrate to you some cool features of logon triggers such as creating an audit log for each user who connects to your database, blocking connections from a user / IP / Hostname and blocking connections at a certain time.

NOTICE

First of all, I would like to warn you about DANGER when using logon triggering. Keep in mind that this trigger will be executed every time a new connection is opened in the database. In addition to slowing down the logon process according to your trigger code, if the user is not allowed to perform any trigger operation or even if the trigger has any errors, you can prevent ALL users from instance connect to SQL Server.

That is, BE VERY CAREFUL, because you can stop the instance if you activate a trigger without first testing it very well. Before enabling a logon trigger, I always recommend activating the DAC connection, to ensure that you can connect to the instance in case the trigger has problems. Another tip is to always leave a user on the list of exceptions (like the “sa” in the example) in case the trigger has an error, it is easier to connect to the instance to drop the trigger.

To know how to do this, visit my article. Enabling and using dedicated remote administrator connection (DAC) in SQL Server.

If you have already created the trigger, it is preventing users from connecting, you have not enabled the DAC connection, and are already having trouble logging in to the instance, an alternative is to add the -f parameter at startup of the SQL Server service to start SQL Server with minimal configuration, drop the trigger and restart the service without -f.

Auditing and logging Logins performed

In this code snippet I will demonstrate how to create a connection log for auditing users who connect to your database.

I added some filters to avoid logging in system users (Ex: SA), connections from software that are constantly connecting to the database (Ex: RedGate SQL Prompt and Managment Studio Intellisense).

I also added a feature to try to identify the AD user name that is logging into the database using an SQL user. I don't know a way to do this 100%, so I retrieve the last AD user who logged in on this hostname where the SQL user is.

Finally, I added another filter to avoid writing multiple repeating lines, making sure it was already written within a range of 1h, a connection to the same user, hostname, and SPID.

Implementation:
View source

Results:

Remember to review the name of the tables in this trigger very well when implementing in your environment. Otherwise, you will probably create a “buggy” trigger and prevent users from logging into your instance, as shown in the error message below:

Another note in this code is that this trigger writes data to some tables in the database, that is, the user connecting to the database will need permissions to write the data in this table, and the user created in the database of that table. For this reason, I added the grant command to the table for role public.

Another way to get around this is to use the EXECUTE AS clause 'login_com_permissao', so that the trigger will be executed with the permission of that EXECUTE AS user, but will record the data of the real user who is counting, avoiding the need to having to create all users in the database and release the permissions, looking like this:

PS: If you are following this approach, be sure to choose a user with all permissions required by the operations of this trigger (eg role member sysadmin). Otherwise your trigger will present errors and prevent new database connections (read CAOS)

Preventing Login for Certain Users

Now I’m going to demonstrate to you how to limit the access of some specific users to the database. This can also be applied to specific IP's or Hostnames, creating a list of allowed or denied.

Implementation:
View source

Results:

Preventing login at a certain time

In this excerpt below, I will demonstrate how to block out-of-business connections.

Implementation:
View source

Results:

Limiting the Number of Maximum User Connections

In the code snippet below, I will demonstrate how to limit the number of concurrent user connections. You can change the code to limit only to certain users, depending on your need.

Notice that in this trigger, I put an exception to not limit the connections of users that are sysadmin, which is the case of DBA's. This can be used in the other triggers as well, if you find it interesting.

Implementation:
View source

Results:

Preventing Login for SQL Users with SSMS

Now I will demonstrate to you how to prevent users with SQL Server authentication from connecting to the environment using SQL Server Management Studio or SQLCMD. This is very useful in environments where developers and DBA's access the bank using users with Windows authentication and applications use users with SQL Server authentication.

In this scenario, it is very common for developers to use the application user to apply commands in production environments where their user does not have write permissions. This trigger may be a good choice to prevent them from trying to utilize these changes using the application user rather than asking the DBA.

Implementation:
View source

That's it folks!
Until the next post!