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:

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?

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

Quite simple isn’t it?

Creating logon triggers

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

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:

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.
   
6. On the Add Database Reference window, click Cancel.
7. In the Solution Explorer, right click the solution name and select Add – Trigger.
   
8. In the Add New Item window – enter for the name DemoTrigger.
   
9. Replace the auto-generated code with the following:

10. Build the project and then close Visual Studio.

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

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

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

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:

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

Special circumstances

Pop quiz: You run the following statement

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!