Rajendra Gupta
Logon Trigger integration

An overview of Logon triggers in SQL Server

June 19, 2020 by

This article gives you an overview of Logon triggers in SQL Server and its usage to control SQL Server authentication.

Introduction

SQL Server provides Windows and SQL authentication method to connect using SSMS or client applications. Security is always a primary focus for DBA, especially in the production environment. We can control database access by providing minimum and appropriate permissions for user work. We can use GRANT or DENY statements in SQL Server to give appropriate task permissions for the individual user.

SQL Server has the following triggers:

  • Data Definition language triggers or DDL riggers
  • Data Manipulation Language or DML triggers
  • Common language runtime or CLR triggers
  • Logon triggers

In this article, we will explore Logon triggers in SQL Server and its usage.

Logon triggers in SQL Server

Triggers is a database code that gets executed in case of a precisely defined event. We can use logon triggers to control the SQL login security. SQL Server automatically executes the logon triggers once a logon event occurs. It gets executed before a user session is established and authentication is successful.

We use two terms for a database successful connection in any database.

  • Authentication: We specify a username and password to connect using SQL authentication. It is a validation of the user’s credentials
  • Authorization: It is a permission that allows you to do specific tasks. You may be authorized to have full access to a specific database but not authorized to make any changes at the instance level

Logon Trigger integration

If any user is unable to authenticate to SQL Server (wrong credentials), SQL Server does not execute the logon triggers. SQL Server starts an implicit transaction before the logon triggers fires. It is independent of any user transaction. In the case of the logon trigger, the transaction count is 1. In case SQL returns an exception, its value sets to zero. It fails the implicit transaction, and the user session is not established. User connection also fails in case of the error having severity more significant than 20 inside the trigger.

Let’s explore the use of cases of logon triggers using examples.

Restrict the total number of SQL Server connections using Logon triggers in SQL Server

Sometimes, we require to restrict the total number of SQL Server connections. Too many connections might cause performance issues so we can use logon triggers to solve this purpose. By default, SQL Server allows an unlimited number of concurrent connections to SQL Server. You can verify it in the SQL Server properties.

Max number of connections

We can use the DMV sys.dm_exec_connections to differentiate the system and user process. In the below query, we create a logon trigger to allow a maximum of three connections. You can see, we use a clause FOR LOGON for the logon triggers.

  • Note: Do not execute the below code in the production environment. You should limit the maximum connection only if required and after checking your environment requirements

Once the trigger is created, make three user connections and verify it using DMV.

Connected user process

Now, if you try to make an additional connection, you get the Error:17892, and it gives you a message that the user cannot connect due to trigger execution.

Error due to trigger

Before we move forward, drop this logon trigger using the following query.

Put a Limit on the number of SQL Server connections from a user

We may face this issue frequently where the user opens many connections and execute the query in all of them. It increases the total number of connections as well as might cause performance issues. We do not want to limit the total number of connections. We require that a user should not be allowed to open more than two connections simultaneously.

SQL Server DMV sys.dm_exec_connections has a column to give the original login name.

DMV output

We can use ORIGINAL_LOGIN() to check the login from which the user is connected to SQL Server.

Origin login

In the below trigger definition, we put a check on the maximum connected connections for the user process. We can note that each user can have a maximum of two simultaneous connections using this logon trigger.

We get the same error message once a user tries to make a third connection.

Error due to trigger

We can verify here that each user can have two simultaneous connections.

Data output

You can also find an entry in SQL Server logs that depicts that you are not allowed to have more than two connections. It is the custom message we specified in the logon trigger.

SQL Server logs

Allow users with the specific client application to connect with SQL Server connections using Logon triggers in SQL Server

Suppose we do not want users to connect with SQL Server from other than SSMS. We can use the APP_NAME() function to check user client application and define logic in logon trigger to allow connection or not.

Here, we can see it disallowed connection from the SQLCMD because we only allowed SSMS to make a connection.

SQL Server connections using Logon triggers in SQL Server

You can see the custom message in the error log. This custom message helps you to identify the connection issues quickly.

View error logs

Disallow user connection at a specific time frame using Logon triggers in SQL Server

Suppose we do not want any user to connect to the SQL database during night hours. We might be doing database maintenance tasks in these hours. Logon triggers can help in fulfilling this requirement.

Suppose I have a Demo user with [dbcreator] server-level permissions.

For the demonstration purposes, let’s say this user works in after office hours. We want the user to login between 6 PM and 6 AM. Outside these hours, SQL Server should restrict access for this specific user.

In the below trigger script, we use ORIGINIAL_LOGIN() function to validate the user and SQL DATEPART function to check user login hours. If both the conditions met, the user [demo] can log in to SQL Server else he gets an error message.

Disallow user connection

In the above trigger code, we do not specify a PRINT statement to log a custom message in the error log. You can see it logs a generic message in the error log, so I recommend using a custom message to give you specific details.

View error logs SQL Server

Login Auditing using Logon Triggers

Sometimes, we require to audit SQL Server instances login events. We have the option to do login auditing in SQL Server properties.

Login Auditing

Once you enable login audit, it logs entries in the SQL Server error log for failed and successful login based on the configured option.

Logon messages

It may fill up the error logs quickly, and you might miss checking useful login events. You can also use logon triggers in SQL Server for this audit purpose.

For this purpose, create a SQL table to hold audit events.

Next, create the logon trigger with the following script.

In the above code, we use EVENTDATA() to capture the hostnames. It is an XML document in a DDL trigger to capture useful schema details. You can refer to Microsoft docs for more details on eventdata().

Now, query the Audit_Logins table, and you can see login event records getting stored.

Audit data

Disable all logon triggers in SQL Server

You can either plan to remove the logon triggers or disable them if no required. We can use the following script to disable all logon triggers in SQL instance.

Useful points about Logon triggers in SQL Server

  • Use the logon triggers with due intelligence. You can block all SQL connections if they are not configured correctly
  • Always perform the demonstration in your test environment
  • Do not create multiple logon triggers and configure only as per your requirement
  • We should always be ready with a rollback plan in case you accidentally block all SQL connections

Conclusion

In this article, we explored useful logon triggers in SQL Server. You can use it to restrict SQL logins as per your requirement. It can be useful for audit purposes as well. You should explore these triggers and implement them with proper testing if required.

Rajendra Gupta
198 Views