Logon Triggers

When I asked a friend from the SQL Server development team “what’s the story behind Logon Triggers?” the answer was brief “Common Criteria compliance for SQL Server and nothing more”. That is, indeed, the short story but if you work with SQL Server 2005 and need the long story, read on.

A little bit of Common Criteria mumbo jumbo

Today, for most businesses that involve IT, security is a must and not a luxury (as it was just a few years back). But what does security for a computer product really mean? It is not so easy to give an answer that would satisfy everyone. Fortunately for us IT folks, there are several standards that define security.

One of these standards is the Common Criteria (CC) certification, which specifies the security functionalities of a product, as well as the methods to evaluate them. The CC evolved from three other standards: ITSEC (the European standard), CTCPEC (the Canadian standard) and TCSEC (The United States Department of Defense Standard).

What you need to know about the Common Criteria certification is that it is recognized by more than twenty-four nations and that it specifies seven levels of assurance for a computer product, EAL1 to EAL7.

For Windows Server, SQL Server and other software products designed for commercial distribution, an certification level of EAL4 is considered to be adequate.

SQL Server 2005 was evaluated at Common Criteria certification EAL1 for Service Pack 1 and EAL4+ for Service Pack 2. The “+” from EAL4 represents “flaw remediation” that is not part of EAL4. It means that Microsoft will provide SQL Server customers with security patches or corrective actions for any security flaw found in the product.

One of the features introduced by Service Pack 2, in order to achieve EAL4+, was Logon Triggers. They allow SQL Server to comply with the following requirements:

The ability to restrict the maximum number of concurrent sessions that belong to the same user.
The ability to configure a default maximum number of sessions per user.
The ability to deny session establishment based on user identity and/or group identity, time of day, and day of week.

Do I really need Logon Triggers to implement these security functionalities? Is there any work-around? I encountered these questions more than a few times, from curious or service pack resistant people, so I decided to try to find a work-around, knowing that discovering one would mean outsmarting Microsoft developers (a very hard thing to do but not an impossible one).

A Logon Triggers work-around in SQL Server 2005 pre SP2

Have you ever seen the following screen?

It’s a screenshot of the Active Directory Users and Computers snap-in that allows you to customize the logon hours for a user (among other things).

Don’t search for something similar in SQL Server 2005 pre SP2, because it doesn’t exist. However, its provision is a requirement of the EAL4 evaluation, as listed above (the ability to deny session establishment based on time of day, and day of week).

For this requirement I found an easy work-around, using SQL Server Agent jobs. Let’s suppose that one of the users in my organization, named Thomas Anderson, should be allowed to connect to the SQL Server box, every day of the week from 9AM to 5PM except Saturday and Sunday.

All I have to do is to disable his login each day at 5PM and enable it each day at 9AM. The following code does just that:

USE msdb;

-- Create a job

EXEC sp_add_job @job_name=N'LogonHours';

-- Add a T-SQL step to the job

EXEC sp_add_jobstep

@job_name = N'LogonHours',

@step_name=N'Enable/Disable login',

@subsystem=N'TSQL',

@command=N'IF DATEPART(hour,GETDATE())=9

ALTER LOGIN thomas_anderson ENABLE

ELSE

ALTER LOGIN thomas_anderson DISABLE',

@database_name=N'master';

-- Associate a schedule to the job

-- to run the job a 5PM

EXEC sp_add_jobschedule

@job_name = N'LogonHours',

@name=N'LogonHoursScheduleForDisable',

@enabled=1,

@freq_type=8,

@freq_interval=62,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_time=170000;

-- Associate a second schedule to the job

-- to run the job at 9AM

EXEC sp_add_jobschedule

@job_name = N'LogonHours',

@name=N'LogonHoursScheduleForEnable',

@enabled=1,

@freq_type=8,

@freq_interval=62,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_time=90000;

Let me explain the code just a little bit. We create a job and associate to with two schedules. One schedule that will execute the job each working day at 9AM and a similar schedule that will execute the job at 5PM. The job itself runs T-SQL code that enables the thomas_anderson login if it’s 9AM and disables it otherwise.

So far so good, the work-around works like a charm. What about the other requirements? Can I restrict the number of connections for a user using the same method? No!

A possible scenario for restricting the number of connections is the next one: first the user establishes a connection, then we check the number of already existing connections from this user and if the maximum number of connections that we allow is already attained we kill the new connection.

To do that we need something that will notify us when the user tries to connect. Something close to what we need is the AUDIT_LOGIN SQL Trace event, which fires when a user establishes a successful connection. We can use this event with SQL Profiler or with Event Notifications. The first option, SQL Profiler, is not actually an option, unless we plan to sit watching a screen 24/7, and manually ending connections.

Event Notifications allow the automation of our scenario.

Note: The Event Notifications use the Service Broker infrastructure by sending a message about an event to a Service Broker service.

To use Event Notifications, we need a queue that will hold the events, a service, a route, an event notification and a stored procedure that will do the work. As there’s no need to reinvent the wheel we can adapt the code using the base samples from Books Online.

Here’s the code:

100

101

102

103

104

105

106

107

USE master;

-- Create demo database

CREATE DATABASE DemoDB;

-- Create demo login

CREATE LOGIN thomas_anderson

WITH PASSWORD = 'yukon9.0';

-- Enable Service Broker for DemoDB database if it's the case

IF EXISTS(

SELECT *

FROM sys.databases

WHERE [name]=N'DemoDB' AND is_broker_enabled=0

)

ALTER DATABASE DemoDB SET ENABLE_BROKER;

-- We will access from the activated stored procedure a view that is

-- located in a different database

-- the sys.dm_exec_sessions dynamic management view

-- The security context of the stored procedure would not allow us to do so

-- unless we set the the TRUSTWORTHY option to ON.

ALTER DATABASE DemoDB SET TRUSTWORTHY ON

USE DemoDB;

-- Create a queue

CREATE QUEUE Logon_Triggers_Queue;

-- Create a service

CREATE SERVICE Logon_Triggers_Service

ON QUEUE Logon_Triggers_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

-- Create a route

CREATE ROUTE Logon_Triggers_Route

WITH SERVICE_NAME = N'Logon_Triggers_Service',

ADDRESS = N'LOCAL';

-- Create the event notification at the server level for the AUDIT_LOGIN event

CREATE EVENT NOTIFICATION Successfull_Login_Notification

ON SERVER

FOR AUDIT_LOGIN

TO SERVICE 'Logon_Triggers_Service', 'current database';

-- Create the stored procedure that will handle the events

-- First set the options required to work with the XML data type

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

CREATE PROCEDURE usp_Logon_Triggers

BEGIN

SET NOCOUNT ON;

-- Use an endless loop to receive messages

WHILE (1 = 1)

BEGIN

DECLARE @messageBody VARBINARY(MAX);

DECLARE @messageTypeName NVARCHAR(256);

WAITFOR (

RECEIVE TOP(1)

@messageTypeName = message_type_name,

@messageBody = message_body

FROM Logon_Triggers_Queue

), TIMEOUT 500

-- If there is no message, exit

IF @@ROWCOUNT = 0

BEGIN

BREAK ;

END ;

-- If the message type is EventNotification do the actual work

IF (@messageTypeName =

'http://schemas.microsoft.com/SQL/Notifications/EventNotification')

BEGIN

DECLARE @data XML;

DECLARE @SPID VARCHAR(5);

DECLARE @LoginName NVARCHAR(128);

SET @data = CAST(@messageBody AS XML);

-- Get the SPID and the Login name using the value method

SET @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(5)');

SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)');

-- Check the login name

IF @LoginName=N'thomas_anderson' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1

AND original_login_name = 'thomas_anderson') > 1

BEGIN

-- Kill the current connection if there is already one session established

-- as thomas_anderson

EXECUTE ('KILL ' + @SPID);

END

END;

-- Link the stored procedure to the Logon_Triggers_Queue

ALTER QUEUE Logon_Triggers_Queue

WITH STATUS=ON,

ACTIVATION (

STATUS=ON,

PROCEDURE_NAME = usp_Logon_Triggers,

MAX_QUEUE_READERS = 1,

EXECUTE AS SELF) ;

The creation of the queue, service, event notification and route are pretty straightforward but if you need further details on setting up Service Broker, please refer to this article:

http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/

The stored procedure is activated when a message is posted to the service. If the message type is event notification, the code gets the login name and the SPID (Session ID) of the new connection from the event data.

The information posted to the Logon_Triggers_Service, about the AUDIT_LOGIN event, has the following structure:

<EVENT_INSTANCE>

<EventType>event_type</EventType>

<BinaryData>binary_data</BinaryData>

<DatabaseID>database_id</DatabaseID>

<NTDomainName>nt_domain_name</NTDomainName>

<ClientProcessID>client_process_id</ClientProcessID>

<ApplicationName>application_name</ApplicationName>

<LoginName>login_name</LoginName>

<StartTime>start_time</StartTime>

<EventSubClass>event_subclass</EventSubClass>

<Success>success</Success>

<IntegerData>integer_data</IntegerData>

<ServerName>server_name</ServerName>

<DatabaseName>database_name</DatabaseName>

<LoginSid>login_sid</LoginSid>

<RequestID>request_id</RequestID>

<EventSequence>event_sequence</EventSequence>

<IsSystem>is_system</IsSystem>

<SessionLoginName>session_login_name</SessionLoginName>

</EVENT_INSTANCE>

Most of the elements are self explanatory:

event_type is obviously AUDIT_LOGIN.
post_time contains the time when the event is posted to the service.
spid represents the ID of the session for the event.
text_data contains a semicolon-delimited list of all set options.
binary_data contains the session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers.
database_id is the ID of the default database or the ID of the database used by the USE database statement if any.
nt_user_name represents the Windows user name.
nt_domain_name represents the Windows domain to which the user belongs.
host_name contains the name of the computer on which the client is running.
client_process_id is the ID assigned by the host computer to the process where the client application is running.
application_name is the name of the client application.
login_name is the name of the login used.
start_time represents the time that the event started.
event_subclass indicates if the connection is pooled or non-pooled with values 1 for non-pooled and 2 for pooled.
success element indicates if the authentication succeeded and has the values 1 for success and 0 for failure. For the AUDIT_LOGIN event it will always contain value 1.
integer_data represents the network packet size.
server_name represents the name of the instance of SQL Server on which the event occurred.
database_name is the name of the database
login_sid contains the security identification number (SID) of the logged-in user.
request_id represents the ID of the request.
event_sequence represents the sequence of the event within the request.
is_system indicates if the event occurred for a user or a system process. In this case it would always be a user process.

You can see a fragment of the event data on my computer:

<EVENT_INSTANCE>

<EventType>AUDIT_LOGIN</EventType>

<TextData>-- network protocol: LPC

set quoted_identifier on

...

</TextData>

<BinaryData>IAAAKDj0AQAAAAAA</BinaryData>

<NTUserName>Cristi</NTUserName>

<NTDomainName>MICROTRAINING</NTDomainName>

<HostName>MICROTRAINING</HostName>

<ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>

<LoginName>MICROTRAINING\Cristi</LoginName>

<ServerName>MICROTRAINING</ServerName>

<DatabaseName>master</DatabaseName>

<LoginSid>AQUAAAAAAAUVAAAAFSWvRwfplC0jX2Nr6wMAAA==</LoginSid>

</EVENT_INSTANCE>

We need only the SPID (session ID) of the connection that fired the event and the login name. We obtain them by using value method of the XML data type.

Once we have the SPID and the login name, we can check the number of existing connections for the user (in this case our own Mr. Anderson) and if the maximum number is already attained, we can kill the new connection. The maximum is set to 1 in this case to avoid opening too many query windows for testing the code.

Let’s see it at work!

In SQL Server Management Studio, open a query window and login as thomas_anderson. Next, open a new query and login again as thomas_anderson. Don’t be fooled by the status message “Connected from SSMS”. As soon as you execute something in the new window (like for example SELECT GETDATE();), the connection status will change to Disconnected and you will get an error:

"Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when sending the request to the server.

(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"

At first look, it seems that we’ve succeeded in emulating a logon trigger, but let’s take a step back. Did we manage to connect? Yes! And that violates the Common Criteria requirement to be able to limit the number of connections for a user. Eventually, our mechanism worked but the key word here is “eventually”. That means that Mr. Anderson could hypothetically run a script before being disconnected. And it should be like that. Event Notifications are an asynchronous mechanism.

Disabling our own mechanism

Just for educational purposes I will illustrate how we can make the Event Notifications workaround totally useless.

As you probably know SQL Server 2005 does its own scheduling. Without spending too much time with this: a task (an execution request) is executed by a worker which is mapped to either an OS thread or a logical thread (a fiber).

On my computer, a one proc x86 machine, there are, by default, a maximum of 256 worker threads. I obtained this value from the max_worker_count column of the sys.dm_os_sys_info dynamic management view.

What do you think would happen if SQL Server receives more than 256 requests at the same time? Some of those requests will run before the usp_Logon_Triggers stored procedure. Ergo, if Mr. Anderson sends 256 requests, the Event Notifications-based mechanism won’t be able to kill them all.

To run multiple requests at the same time we can use the OSTRESS command line utility, which you can download from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&DisplayLang=en.

Have you ever noticed that before you do anything you have to do something else first? This case is no exception to this rule. Before we get to run the OSTRESS tool, we first need to create first a table and allow Mr. Anderson to write to this table, in order to determine how many times he can avoid our Logon Triggers substitute.

So, open SQL Server Management Studio and run the following code:

USE DemoDB;

-- Create demo table

CREATE TABLE tblDemo(

ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

VAL VARCHAR(128));

-- Create a database user for Mr. Anderson

CREATE USER thomas_anderson FOR LOGIN thomas_anderson;

-- Grant INSERT permission to the user on the new table

GRANT INSERT ON tblDemo TO thomas_anderson;

Then open a command line and navigate to the folder that contains the ostress.exe executable. Next type the following text and hit ENTER:

ostress.exe -l600 -Uthomas_anderson -Pyukon9.0 -Slpc:%COMPUTERNAME% -n256 -Q"INSERT INTO DemoDB..tblDemo SELECT 'I am in!';" -q

The l parameter specifies the timeout, and the others are: the login name (U) the password (P), the server and the protocol used (Slpc), the query text (Q) and the request to discard the output of the query, if any (q). You can read more about the parameters in the help of OSTRESS utility.

After running the above statement, switch back to SQL Server Management Studio and let’s see what happened by running:

1	SELECT COUNT(*) FROM DemoDB..tblDemo;

On my computer the result is 253, and that means that out of 256 statements sent to the server, 253 succeeded. The general network error message is generated when the Logon Triggers surrogate manages to kill a request.

Besides this, if our user has enough rights to send an ALTER QUEUE statement to the server he can disable the whole mechanism.

To conclude, the Event Notifications method didn’t make me smarter than Microsoft guys. However, you can use this approach if you need only to audit login information. The asynchronous nature of Event Notifications makes them ideal for auditing.

Before moving to the next section, please run the clean-up code:

USE DemoDB;

-- Drop the event notification

-- Can be done from any database context as it is defined

-- at the server level

DROP EVENT NOTIFICATION Successfull_Login_Notification

ON SERVER;

-- Drop the service

DROP SERVICE Logon_Triggers_Service;

-- Drop the route

DROP ROUTE Logon_Triggers_Route;

-- Drop the queue

DROP QUEUE Logon_Triggers_Queue;

-- Drop the stored procedure

DROP PROCEDURE usp_Logon_Triggers

As we’ve just seen, an asynchronous method does not fulfill the Common Criteria requirements. So the solution from Microsoft is a new type of trigger, called the logon trigger.

A logon trigger fires when a session is established. At that point the LOGON event is raised. The Logon event corresponds to the AUDIT_LOGIN SQL Trace event used in the previous section.

The life-cycle of a logon trigger is very simple: a user connects to Sql Server, the trigger fires, an implicit transaction is opened and then…then it’s up to you! If, for any reason, you want to deny the attempt to log in to Sql Server, just issue a ROLLBACK statement and you’re done.

Let’s see the previous example written using a logon trigger instead of Event Notifications:

USE master;

CREATE LOGIN security_login WITH PASSWORD = 'yukon9.0';

GRANT VIEW SERVER STATE TO security_login;

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'security_login'

FOR LOGON

BEGIN

IF ORIGINAL_LOGIN()= 'thomas_anderson' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'thomas_anderson') > 1

ROLLBACK;

END;

The sys.dm_exec_sessions dynamic management view allows you to see all sessions on a SQL Server instance, provided that you have the VIEW SERVER STATE permission (or you belong to the sysadmin server role). Otherwise you will see only your current session.

To prevent granting unnecessary rights to Mr. Anderson, we create a new login and grant it the VIEW SERVER STATE permission. Then we create the trigger to run under its security context. The rest of the code needs no explanation. However I want to make some important observations:

The ROLLBACK statement rolls back all data modification and will close the connection, but if there are any statements after the ROLLBACK, they will be executed. Any data modification that occurs after the ROLLBACK statement is not rolled back to allow you eventually to log the connection attempt in a table.
Returning results from triggers is not a good practice as the client application may not expect a result set. The capability to return result sets in triggers will be removed from future versions of SQL Server. In logon triggers, returning result sets is prevented. So don’t try to return a result set or your trigger will fail.
Any PRINT messages, messages issued using RAISERROR statement or other error messages would be diverted to the SQL Server log.

To test it, open SQL Server Management Studio and then open two query windows as thomas_anderson. The second log on process will fail and an error message will be displayed.

Quite simple isn’t it?

Creating logon triggers

To create a logon trigger, you can use the CREATE TRIGGER statement with the following syntax:

CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH <logon_trigger_option> [ ,...n ] ]

{ FOR | AFTER } LOGON

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

<logon_trigger_option> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

<method_specifier> ::=

assembly_name.class_name.method_name

The named trigger is registered at the server level and resides in the master database. The code of the trigger could be either T-SQL or CLR code.

The WITH ENCRYPTION clause allows you to encrypt the definition of the trigger.

If you do not specify an execution context for the trigger, using the EXECUTE AS clause, the default behavior is to use the context of the caller of the module.

Once created, you can modify or drop logon triggers using the ALTER TRIGGER or DROP TRIGGER statements, respectively.

Getting information inside logon triggers

To get at information inside a logon trigger you can use the EVENTDATA() function. In this particular case, this will return xml information having the following schema:

<EVENT_INSTANCE>

<EventType>event_type</EventType>

<ServerName>server_name</ServerName>

<LoginName>login_name</LoginName>

<LoginType>login_type</LoginType>

<ClientHost>client_host</ClientHost>

<IsPooled>is_pooled</IsPooled>

</EVENT_INSTANCE>

The LOGON event schema is somewhat similar to the AUDIT_LOGIN event schema, only simpler:

event_type is LOGON.
post_time contains the time for session request.
The is_pooled element indicates if the connection is using connection pooling (value 1) or not (value 0).

The rest of the elements are as described for the AUDIT_LOGIN event.

Besides the EVENTDATA() function, you can also use system functions such as ORIGINAL_LOGIN() used in the previous example if you need additional information.

CLR Logon Triggers

In most everyday scenarios, you probably would not need CLR code to implement a Logon Trigger. However, to demonstrate that there’s nothing too complicated about it, here’s an example. We will create a CLR Logon Trigger to audit successful logons in a table:

Open SQL Server Management Studio and in a new query window run the following code to create the audit table:

USE DemoDB;

-- Create the audit table

CREATE TABLE tblAudit(

[EventID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

[PostTime] DATETIME,

[SPID] SMALLINT,

[ServerName] NVARCHAR(128),

[LoginName] NVARCHAR(128),

[LoginType] NVARCHAR(64),

[SID] VARBINARY(85),

[ClientHost] NVARCHAR(128),

[IsPooled] BIT);

USE master;

-- Create a login to use for the execution context

-- of the new trigger

CREATE LOGIN audit_login WITH PASSWORD = 'yukon9.0';

-- Create a database user for the new login

USE DemoDB;

CREATE USER audit_user FOR LOGIN audit_login;

-- Grant INSERT permission to the new user

GRANT INSERT ON tblAudit TO audit_user;

Then open Visual Studio 2005.
On the File menu select New Project.
Expand Database projects and select SQL-CLR as the project type.
In the same window SQL Server Project C# template, enter CLRLogonTrigger in the Name textbox, C:\Projects for location and click OK.
On the Add Database Reference window, click Cancel.
In the Solution Explorer, right click the solution name and select Add – Trigger.
In the Add New Item window – enter for the name DemoTrigger.
Replace the auto-generated code with the following:

100

using System;

using System.Data;

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Xml;

using System.Transactions;

public partial class Triggers

{

[Microsoft.SqlServer.Server.SqlTrigger(Name = "DemoTrigger", Target = "ALL SERVER", Event = "LOGON")]

public static void DemoTrigger()

{

try

{

using (SqlConnection connection = new SqlConnection(@"context connection=true"))

{

// Get the trigger context

SqlTriggerContext triggContext = SqlContext.TriggerContext;

// Create a new SqlCommand

SqlCommand command = new SqlCommand();

// Get event's data

XmlDocument doc = new XmlDocument();

doc.LoadXml(triggContext.EventData.Value);

// Open the connection.

connection.Open();

// Build the INSERT statement using parameters

command.Connection = connection;

command.CommandText = @"INSERT INTO DemoDB..tblAudit(

[PostTime],

[SPID],

[ServerName],

[LoginName],

[LoginType],

[SID],

[ClientHost],

[IsPooled]

)

VALUES (

@PostTime,

@SPID,

@ServerName,

@LoginName,

@LoginType,

@SID,

@ClientHost,

@IsPooled

)";

// Add parameters

// PostTime

SqlParameter parameter = new SqlParameter("@PostTime", SqlDbType.DateTime);

parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/PostTime").InnerText;