Redgate Hub

  • Product articles
  • University
  • Events
  • Forums
  • Community
  • Simple Talk
  • Home
  • Cloud
  • DevOps
  • Sysadmin
  • Development
  • Databases
  • Opinion
  • Books
  • Blogs
  • Log in
  • Sign up
Cristian Lefter

10 October 2007

  • 67
  • 67956 views

  • 0
    • Printer friendly version
  • Home
  • Databases
  • SQL Server
  • T-SQL Programming
  • Logon Triggers
Cristian Lefter

10 October 2007

67956 views

67
0

Logon Triggers

Login Triggers were quietly introduced in SP2 to tighten up the security features of SQL Server to comply with the latest industry standards for security. But you can meet a lot of the security requirements even without them!

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:

  1. The ability to restrict the maximum number of concurrent sessions that belong to the same user.
  2. The ability to configure a default maximum number of sessions per user.
  3. 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?

442-Image1.gif

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
USE msdb;
GO
-- Create a job
EXEC sp_add_job @job_name=N'LogonHours';
GO
-- 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';
GO
-- 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;
GO  
-- 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;
GO

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
USE master;
GO
-- Create demo database
CREATE DATABASE DemoDB;
GO
-- Create demo login
CREATE LOGIN thomas_anderson
WITH PASSWORD = 'yukon9.0';
GO   
-- 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;
GO
-- 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
GO
USE DemoDB;
GO
-- Create a queue
CREATE QUEUE Logon_Triggers_Queue;
GO
-- Create a service
CREATE SERVICE Logon_Triggers_Service
ON QUEUE Logon_Triggers_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- Create a route
CREATE ROUTE Logon_Triggers_Route
WITH SERVICE_NAME = N'Logon_Triggers_Service',
ADDRESS = N'LOCAL';
GO
-- 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';
GO
 
-- 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;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE usp_Logon_Triggers
AS
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;
 
END;
END;
GO
 
-- 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) ;
GO

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<EVENT_INSTANCE>
  <EventType>event_type</EventType>
  <PostTime>post_time</PostTime>
  <SPID>spid</SPID>
  <TextData>text_data</TextData>
  <BinaryData>binary_data</BinaryData>
  <DatabaseID>database_id</DatabaseID>
  <NTUserName>nt_user_name</NTUserName>
  <NTDomainName>nt_domain_name</NTDomainName>
  <HostName>host_name</HostName>
  <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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<EVENT_INSTANCE>
  <EventType>AUDIT_LOGIN</EventType>
  <PostTime>...</PostTime>
  <SPID>56</SPID>
  <TextData>-- network protocol: LPC
set quoted_identifier on
...
</TextData>
  <BinaryData>IAAAKDj0AQAAAAAA</BinaryData>
  <DatabaseID>1</DatabaseID>
  <NTUserName>Cristi</NTUserName>
  <NTDomainName>MICROTRAINING</NTDomainName>
  <HostName>MICROTRAINING</HostName>
  <ClientProcessID>2456</ClientProcessID>
  <ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
  <LoginName>MICROTRAINING\Cristi</LoginName>
  <StartTime>...</StartTime>
  <EventSubClass>1</EventSubClass>
  <Success>1</Success>
  <IntegerData>4096</IntegerData>
  <ServerName>MICROTRAINING</ServerName>
  <DatabaseName>master</DatabaseName>
  <LoginSid>AQUAAAAAAAUVAAAAFSWvRwfplC0jX2Nr6wMAAA==</LoginSid>
  <RequestID>0</RequestID>
  <EventSequence>290</EventSequence>
  <IsSystem />
  <SessionLoginName />
</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:

1
2
3
4
"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:

1
2
3
4
5
6
7
8
9
10
11
12
13
USE DemoDB;
GO
-- Create demo table
CREATE TABLE tblDemo(
   ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
   VAL VARCHAR(128));
GO
-- Create a database user for Mr. Anderson
CREATE USER thomas_anderson FOR LOGIN thomas_anderson;
GO
-- Grant INSERT permission to the user on the new table
GRANT INSERT ON tblDemo TO thomas_anderson;
GO

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.

442-Image2.gif

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE DemoDB;
GO
-- 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;
GO
-- Drop the service
DROP SERVICE Logon_Triggers_Service;
GO
-- Drop the route
DROP ROUTE Logon_Triggers_Route;
GO
-- Drop the queue
DROP QUEUE Logon_Triggers_Queue;
GO
-- Drop the stored procedure
DROP PROCEDURE usp_Logon_Triggers
GO

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE master;
GO
CREATE LOGIN security_login WITH PASSWORD = 'yukon9.0';
GO
GRANT VIEW SERVER STATE TO security_login;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'security_login'
FOR LOGON
AS
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:

  1. 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.
  2. 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.
  3. 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.

442-Image3.gif

Quite simple isn’t it?

Creating logon triggers

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER <em>trigger_name </em>
ON ALL SERVER
[ WITH <logon_trigger_option> [ <strong>,</strong><em>...n </em>] ]
{ FOR<em> </em>| AFTER } LOGON 
AS { <em>sql_statement</em>  [ ; ] [ <strong>,</strong>...<em>n </em>] | EXTERNAL NAME < method specifier >  [ ; ] }
<strong><logon_trigger_option> ::=</strong>
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]
 
<strong><method_specifier> ::=</strong>
<em>    assembly_name</em><strong>.</strong><em>class_name</em><strong>.</strong><em>method_name</em>

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:

1
2
3
4
5
6
7
8
9
10
11
<EVENT_INSTANCE>
    <EventType>event_type</EventType>
    <PostTime>post_time</PostTime>
    <SPID>spid</SPID>
    <ServerName>server_name</ServerName>
<LoginName>login_name</LoginName>
<LoginType>login_type</LoginType>
<SID>sid</SID>
<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:

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    USE DemoDB;
    GO
    -- 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);
    GO
    USE master;
    GO
    -- Create a login to use for the execution context
    -- of the new trigger
    CREATE LOGIN audit_login WITH PASSWORD = 'yukon9.0';
    GO
    -- Create a database user for the new login
    USE DemoDB;
    CREATE USER audit_user FOR LOGIN audit_login;
    GO
    -- Grant INSERT permission to the new user
    GRANT INSERT ON tblAudit TO audit_user;
    GO

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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;
            command.Parameters.Add(parameter);
            // SPID
            parameter = new SqlParameter("@SPID", SqlDbType.SmallInt);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/SPID").InnerText;
            command.Parameters.Add(parameter);
            // ServerName
            parameter = new SqlParameter("@ServerName", SqlDbType.NVarChar, 128);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/ServerName").InnerText;
            command.Parameters.Add(parameter);
            // LoginName
            parameter = new SqlParameter("@LoginName", SqlDbType.NVarChar, 128);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/LoginName").InnerText;
            command.Parameters.Add(parameter);
            // LoginType
            parameter = new SqlParameter("@LoginType", SqlDbType.NVarChar, 64);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/LoginType").InnerText;
            command.Parameters.Add(parameter);
            // SID
            parameter = new SqlParameter("@SID", SqlDbType.VarBinary, 85);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/SID").InnerText;
            command.Parameters.Add(parameter);
            // ClientHost
            parameter = new SqlParameter("@ClientHost", SqlDbType.NVarChar, 128);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/ClientHost").InnerText;
            command.Parameters.Add(parameter);
            // IsPooled
            parameter = new SqlParameter("@IsPooled", SqlDbType.SmallInt);
            parameter.Value = doc.DocumentElement.SelectSingleNode("/EVENT_INSTANCE/IsPooled").InnerText;
            command.Parameters.Add(parameter);
 
            // Execute the statement
            command.ExecuteNonQuery();
            }
        }           
        catch (Exception ex)
        {
            //If the audit failed prevent the connection
            // Get the current transaction and roll it back.
            Transaction trans = Transaction.Current;
            trans.Rollback();
        }
    }
}

10. Build the project and then close Visual Studio.

11. Back in SQL Server Management Studio run the following script to create the trigger:

1
2
3
4
5
6
7
8
9
10
11
12
13
USE [master]
GO
-- Import the assembly
CREATE ASSEMBLY [LogonTriggers]
FROM
'C:\Projects\CLRLogonTrigger\CLRLogonTrigger\bin\Debug\CLRLogonTrigger.dll';
GO
-- Create the trigger
CREATE TRIGGER [CLRLogonTrigger]
ON ALL SERVER WITH EXECUTE AS 'audit_login'
 FOR LOGON AS
 EXTERNAL NAME [LogonTriggers].[Triggers].[DemoTrigger]
GO

12. To test the trigger open a new Query Window and run the following:

1
2
3
SELECT
EventID,SPID,ServerName,LoginName,LoginType
FROM DemoDB..tblAudit;

You can see the output on my computer (and it should be similar on yours):

1
2
3
EventID SPID ServerName    LoginName            LoginType
------- ---- ----------    -------------------- ------------------
1       54   MICROTRAINING MICROTRAINING\Cristi Windows (NT) Login

What did we just do? We used the attributes of the CLR module (Name, Target and Event) to specify that the DemoTrigger method should be registered as a trigger and also specify the name, the target and the event that activates the trigger.

The information about the event (LOGON event in this case) is exposed in the EventData property of the SqlTriggerContext class.

In T-SQL, we would use XQuery to parse the XML data but in our .NET code we can use an XmlDocument. The rest of the code uses ADO .NET to insert the data into the table. If anything fails we rollback the current transaction, and consequently the trigger.

After you’ve tested the CRL trigger, you can clean-up by running the following T-SQL code:

1
2
3
4
5
6
7
8
9
USE master;
GO
-- Drop the trigger
DROP TRIGGER [CLRLogonTrigger]
ON ALL SERVER;
GO
-- Drop the assembly
DROP ASSEMBLY LogonTriggers;
GO

Additionally, you can drop the DemoDB database and the logins created:

1
2
3
4
5
6
7
8
USE master;
GO
DROP DATABASE DemoDB;
GO
DROP LOGIN security_login;
GO
DROP LOGIN thomas_anderson;
GO

Special circumstances

Pop quiz: You run the following statement

1
2
3
4
5
6
7
CREATE TRIGGER DenyAnyConnection
ON ALL SERVER WITH EXECUTE AS 'security_login'
FOR LOGON
AS
BEGIN
    ROLLBACK;
END;

What happens after you hit F5?

A. You’ve succeeded to lock yourself out of the server.

B. You can still connect.

Most of you will tend to choose A, which is the wrong option and that’s because Logon Triggers do no fire if you connect using the DAC (Dedicated Administrator Connection) or if the server is started in the minimal configuration mode.

When I created my first CLR trigger, it crashed for every connection due to a conversion error. I already had a connection open so I ran the DISABLE TRIGGER statement to disable the trigger. Alternatively, I could have used the DAC to save the day.

Therefore, remember that you have those two options to disable a Logon Trigger that prevents any connection.

  1. Use DAC
  2. Stop your server and restart it using the minimal configuration mode

When you can, favor the DAC method instead of restarting your server.

Logon Triggers on SQL Server 2000

While doing the research for this article, I encountered a question on a forum:

“What complicated hacks can be used to implement logon triggers on SQL Server 2000?”

I wasn’t aware that such a thing was possible, so I forwarded the question to an MS guy. He answered: “Sure! We have a patch that makes a SQL Server 2000 behave like a 2005 server. There’s just one problem: it’s expensive. It costs almost as much as a 2005 box!”

I’ve worked with SQL Server 2005 since 2003 at that time and I didn’t know of such a patch. Shame on me! After two days of intense investigation I gave up and I asked the MS guy for a direct link to the patch. He gave me this one:

http://www.microsoft.com/sql/howtobuy/default.mspx.

I felt like a dummy for the second time. It was a link for buying SQL Server 2005. Anyway those two days weren’t lost. I learned that developers from Microsoft can have a great sense of humor.

To return to the question: my personal opinion is that you cannot emulate exactly the power of SQL Server 2005 SP2 logon triggers on previous versions of SQL Server.

Instead of a conclusion

The way I see logon triggers is that they are just a framework that allow you to implement lots of custom scenarios.

For example, you can restrict the application used to connect to the server, allow or deny connections from a specific host, stop regular users from connecting during maintenance periods, and so on.

Now I have some observations that you can call “best practices” if you like, but may more accurately be called “common sense”:

  • The logon trigger fires for everyone, so make its code as short and efficient as possible. Just imagine that someone opens a node in SQL Server Management Studio – Object Explorer and the trigger fires. Then he wants to see an object definition – the trigger fires again. You get my point.
  • If you have to use transactions inside logon triggers remember that if you issue a ROLLBACK TRANSACTION, the connection is gone.
  • Use error handling inside your triggers. Guess what happens if a transaction-abort error occurs? Not a hard question and the answer is “connection closed”.
  • If you implement multiple logon triggers, you can specify which one should be the first or the last by using the sp_settriggerorder system stored procedure. Keep in mind that any ROLLBACK TRANSACTION statement rolls back all data modifications done by the current trigger but also by the previous executed triggers, if any.

Leaving behind Logon Triggers, SQL Server 2008 introduces another type of DDL trigger that is fired by all types of server and database events. This trigger is used to support a new feature called the Declarative Management Framework, which allows you to manage your server using policies. But hey, that’s another story!

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

  • 67
  • 67956 views

    • Printer friendly version

Rate this article

Click to rate this post!
[Total: 2 Average: 3]

Cristian Lefter

One of the SQL Server MVPs, Cristian Lefter is a former developer, database administrator and currently CEO of MicroTraining a consulting and training company. In his spare time he is running two user groups ITBoard and Romanian SQL Server User Group.

View all articles by Cristian Lefter

Load comments

Related articles

Dennes Torres
07 March 2022
Dennes Torres
07 March 2022

Power BI: ETL or not ETL, that’s the question

0
30
  • Blogs
ETL stands for Extract, Transform, and Load. Dennes Torres explains what an ETL tool is and isn't.… Read more
0
30
  • Blogs
Dennes Torres
30 August 2022
Dennes Torres
30 August 2022

.NET App Services: Containers or not containers, that’s the question

0
2
  • Blogs
The app services in the title can be function apps, web apps or more. We can deploy the app services on the native app service environment provided by Microsoft or using containers. What’s the different between using the native environment or using containers? The differences aren’t many and it’s not easy to identify when it’s … Read more
0
2
  • Blogs
Dennes Torres
04 July 2022
Dennes Torres
04 July 2022

What’s new in T-SQL in SQL Server 2022

0
20
  • Blogs
There are many new features in SQL Server 2022. In relation to T-SQL querying, there are a few as well and they usually are left for last in face of many other new optimization features. Sample scenario These samples are built on the AdventureWorksDW2019 database installed in a SQL Server 2022 CTP 2. Date_Bucket Let’s … Read more
0
20
  • Blogs

Tags

ETL, Security, SQL, SQL Server, T-SQL Programming, TSQL LOGIN SQL SERVER login trigger security

Simple Talk

  • FAQ
  • Sitemap
  • About Simple Talk
  • Contact Us