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
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.
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TRIGGER tr_LimitMaximumLogins ON ALL SERVER FOR LOGON AS BEGIN IF ( SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 ) > 3 BEGIN PRINT 'This SQL instance allows maximum than three, Connection is Failed'; ROLLBACK; END; END; |
Once the trigger is created, make three user connections and verify it using DMV.
|
1 2 |
SELECT COUNT(*) as [CurrentConnected] FROM sys.dm_exec_sessions WHERE is_user_process = 1 |
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.
Before we move forward, drop this logon trigger using the following query.
|
1 |
Drop trigger tr_LimitMaximumLogins ON ALL SERVER |
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.
|
1 2 3 4 |
SELECT is_user_process, original_login_name, * FROM sys.dm_exec_sessions where is_user_process=1 ORDER BY login_time DESC |
We can use ORIGINAL_LOGIN() to check the login from which the user is connected to SQL Server.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TRIGGER tr_LimitUserConnection ON ALL SERVER FOR LOGON AS BEGIN DECLARE @login NVARCHAR(100); SET @login = ORIGINAL_LOGIN(); IF ( SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = @login ) > 2 BEGIN PRINT 'You are not allowed to have more than two connections- Login by ' + @login + ' Failed'; ROLLBACK; END; END; |
We get the same error message once a user tries to make a third connection.
We can verify here that each user can have two simultaneous connections.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--Drop trigger tr_LimitUserApp ON ALL SERVER CREATE TRIGGER tr_LimitUserApp ON ALL SERVER FOR LOGON AS BEGIN DECLARE @app NVARCHAR(100); SET @app = APP_NAME(); IF(@App LIKE 'SQL Server Management Studio') BEGIN RETURN; END; ELSE PRINT 'You are not allowed to connect from ' + @app + 'Status:- Failed'; ROLLBACK; END; |
Here, we can see it disallowed connection from the SQLCMD because we only allowed SSMS to make a connection.
You can see the custom message in the error log. This custom message helps you to identify the connection issues quickly.
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.
|
1 2 3 4 |
CREATE LOGIN demo WITH PASSWORD = 'test@123'; GO ALTER SERVER ROLE [dbcreator] ADD MEMBER [demo]; GO |
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.
|
1 2 3 4 5 6 7 8 |
CREATE TRIGGER tr_LimitUserTime ON ALL SERVER FOR LOGON AS BEGIN IF((ORIGINAL_LOGIN() = 'Demo') AND (DATEPART(Hour, GETDATE()) BETWEEN 06 AND 18)) ROLLBACK; END; |
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.
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.
Once you enable login audit, it logs entries in the SQL Server error log for failed and successful login based on the configured option.
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.
|
1 2 3 4 5 |
CREATE TABLE Audit_Logins (Login_Name NVARCHAR(256), Login_Time DATETIME, Host_Name NVARCHAR(200) ); |
Next, create the logon trigger with the following script.
|
1 2 3 4 5 6 7 8 9 |
CREATE TRIGGER TR_Audit_logins ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO sqlshack.dbo.Audit_Logins SELECT ORIGINAL_LOGIN(), GETDATE(), EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(128)'); END; |
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.
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.
|
1 |
DISABLE TRIGGER ALL ON ALL SERVER |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023