SQL Server - How to Implement Audit and Login Control (Login Trigger)

Views: 3.440
This post is the 5 part of 15 in the series. Security and Audit
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, as you can stop the instance if you activate a trigger without first testing it very well. Before enabling a logon trigger, I always recommend enabling the DAC connection to ensure that you can connect to the instance if the trigger has problems. Another tip is always to leave a user in the exception list (like the example "sa") in case the trigger has any errors, it is easier to connect in 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:
trgAudit_Login

Remember to review the name of the tables of this trigger very well when deploying to your environment. Otherwise, you will probably create a “bugged” trigger and prevent users from logging in to your instance, as per 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 around this is to use the EXECUTE AS clause 'login_com_permission', so that the trigger will be executed with the permission of that EXECUTE AS user, but will record the actual user data being counted, avoiding the need to have to create all users in the database and release permissions, 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 will show you how to limit the access of some specific users in the database. This can also be applied to specific IPs or Hostnames by creating a whitelist or deny list.

Implementation:
View source

Results:
trgAudit_Login5

trgAudit_Login2

Preventing login at a certain time

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

Implementation:
View source

Results:
trgAudit_Login5

trgAudit_Login3

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.

Note 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 on other triggers as well, if you find it interesting.

Implementation:
View source

Results:
trgAudit_Login5

trgAudit_Login6

Preventing Login for SQL Users with SSMS

Now I will show you how to prevent SQL Server authenticated users from connecting to the environment using SQL Server Management Studio or SQLCMD. This is very useful in environments where developers and DBAs access the database using Windows authenticated users and applications use SQL Server authenticated users.

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!