{"id":314,"date":"2007-10-10T00:00:00","date_gmt":"2007-10-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/logon-triggers\/"},"modified":"2021-09-29T16:22:15","modified_gmt":"2021-09-29T16:22:15","slug":"logon-triggers","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/logon-triggers\/","title":{"rendered":"Logon Triggers"},"content":{"rendered":"<p class=\"start\">When I asked a friend from the SQL Server development team &#8220;what&#8217;s the story behind Logon Triggers?&#8221; the answer was brief &#8220;Common Criteria compliance for SQL Server and nothing more&#8221;. That is, indeed, the short story but if you work with SQL Server 2005 and need the long story, read on.<\/p>\n<h3>A little bit of Common Criteria mumbo jumbo<\/h3>\n<p class=\"start\">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.<\/p>\n<p>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).<\/p>\n<p>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.<\/p>\n<p>For Windows Server, SQL Server and other software products designed for commercial distribution, an certification level of EAL4 is considered to be adequate.<\/p>\n<p>SQL Server 2005 was evaluated at Common Criteria certification EAL1 for Service Pack 1 and EAL4+ for Service Pack 2. The &#8220;+&#8221; from EAL4 represents &#8220;flaw remediation&#8221; 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.<\/p>\n<p>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:<\/p>\n<ol>\n<li>The ability to restrict the maximum number of concurrent sessions that belong to the same user.<\/li>\n<li>The ability to configure a default maximum number of sessions per user.<\/li>\n<li>The ability to deny session establishment based on user identity and\/or group identity, time of day, and day of week.<\/li>\n<\/ol>\n<p>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).<\/p>\n<h3>A Logon Triggers work-around in SQL Server 2005 pre SP2<\/h3>\n<p class=\"start\">Have you ever seen the following screen?<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/442-Image1.gif\" alt=\"442-Image1.gif\" \/><\/p>\n<p>It&#8217;s a screenshot of the Active Directory <i>Users and Computers<\/i> snap-in that allows you to customize the logon hours for a user (among other things).<\/p>\n<p>Don&#8217;t search for something similar in SQL Server 2005 pre SP2, because it doesn&#8217;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).<\/p>\n<p>For this requirement I found an easy work-around, using SQL Server Agent jobs. Let&#8217;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.<\/p>\n<p>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:<\/p>\n<pre>USE msdb;\r\nGO\r\n-- Create a job\r\nEXEC sp_add_job @job_name=N'LogonHours';\r\nGO\r\n-- Add a T-SQL step to the job\r\nEXEC sp_add_jobstep \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @job_name = N'LogonHours',\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0@step_name=N'Enable\/Disable login', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @subsystem=N'TSQL', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @command=N'IF DATEPART(hour,GETDATE())=9 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ALTER LOGIN thomas_anderson ENABLE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ALTER LOGIN thomas_anderson DISABLE', \r\n\u00a0\u00a0\u00a0 \u00a0\u00a0@database_name=N'master';\r\nGO\r\n-- Associate a schedule to the job\r\n-- to run the job a 5PM\r\nEXEC sp_add_jobschedule\r\n\u00a0\u00a0 @job_name = N'LogonHours', \r\n\u00a0\u00a0 @name=N'LogonHoursScheduleForDisable', \r\n\u00a0\u00a0 @enabled=1, \r\n\u00a0\u00a0 @freq_type=8, \r\n\u00a0\u00a0 @freq_interval=62, \r\n\u00a0\u00a0 @freq_subday_type=1, \r\n\u00a0\u00a0 @freq_subday_interval=0, \r\n\u00a0\u00a0 @freq_relative_interval=0, \r\n\u00a0\u00a0 @freq_recurrence_factor=1, \r\n\u00a0\u00a0 @active_start_time=170000;\r\nGO\u00a0\u00a0 \r\n-- Associate a second schedule to the job\r\n-- to run the job at 9AM\r\nEXEC sp_add_jobschedule \r\n\u00a0\u00a0 @job_name = N'LogonHours',\r\n\u00a0\u00a0 @name=N'LogonHoursScheduleForEnable', \r\n\u00a0\u00a0 @enabled=1, \r\n\u00a0\u00a0 @freq_type=8, \r\n\u00a0\u00a0 @freq_interval=62, \r\n\u00a0\u00a0 @freq_subday_type=1, \r\n\u00a0\u00a0 @freq_subday_interval=0, \r\n\u00a0\u00a0 @freq_relative_interval=0, \r\n\u00a0\u00a0 @freq_recurrence_factor=1, \r\n\u00a0\u00a0 @active_start_time=90000;\r\nGO<\/pre>\n<p>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 <b>thomas_anderson<\/b> login if it&#8217;s 9AM and disables it otherwise.<\/p>\n<p>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!<\/p>\n<p>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.<\/p>\n<p>To do that we need something that will notify us when the user tries to connect. Something close to what we need is the <b>AUDIT_LOGIN<\/b> 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.<\/p>\n<p>Event Notifications allow the automation of our scenario.<\/p>\n<p><b>Note<\/b>: <i>The Event Notifications use the Service Broker infrastructure by sending a message about an event to a Service Broker service.<\/i><\/p>\n<p>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&#8217;s no need to reinvent the wheel we can adapt the code using the base samples from Books Online.<\/p>\n<p>Here&#8217;s the code:<\/p>\n<pre>USE master;\r\nGO\r\n-- Create demo database\r\nCREATE DATABASE DemoDB;\r\nGO\r\n-- Create demo login\r\nCREATE LOGIN thomas_anderson \r\nWITH PASSWORD = 'yukon9.0';\r\nGO\u00a0\u00a0\u00a0 \r\n-- Enable Service Broker for DemoDB database if it's the case\r\nIF EXISTS(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT * \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM sys.databases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE [name]=N'DemoDB' AND is_broker_enabled=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\nALTER DATABASE DemoDB SET ENABLE_BROKER;\r\nGO\r\n-- We will access from the activated stored procedure a view that is \r\n-- located in a different database \r\n-- the sys.dm_exec_sessions dynamic management view\r\n-- The security context of the stored procedure would not allow us to do so\r\n-- unless we set the the TRUSTWORTHY option to ON.\r\nALTER DATABASE DemoDB SET TRUSTWORTHY ON\r\nGO\r\nUSE DemoDB;\r\nGO\r\n-- Create a queue\r\nCREATE QUEUE Logon_Triggers_Queue; \r\nGO\r\n-- Create a service\r\nCREATE SERVICE Logon_Triggers_Service\r\nON QUEUE Logon_Triggers_Queue([http:\/\/schemas.microsoft.com\/SQL\/Notifications\/PostEventNotification]);\r\nGO\r\n-- Create a route\r\nCREATE ROUTE Logon_Triggers_Route \r\nWITH SERVICE_NAME = N'Logon_Triggers_Service', \r\nADDRESS = N'LOCAL';\r\nGO\r\n-- Create the event notification at the server level for the AUDIT_LOGIN event\r\nCREATE EVENT NOTIFICATION Successfull_Login_Notification\r\nON SERVER \r\nFOR AUDIT_LOGIN\r\nTO SERVICE 'Logon_Triggers_Service', 'current database';\r\nGO\r\n\r\n-- Create the stored procedure that will handle the events\r\n-- First set the options required to work with the XML data type\r\nSET ANSI_NULLS ON;\r\nGO\r\nSET QUOTED_IDENTIFIER ON;\r\nGO\r\nCREATE PROCEDURE usp_Logon_Triggers\r\nAS\r\nBEGIN\r\nSET NOCOUNT ON;\r\n-- Use an endless loop to receive messages\r\nWHILE (1 = 1)\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @messageBody VARBINARY(MAX);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @messageTypeName NVARCHAR(256);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WAITFOR ( \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RECEIVE TOP(1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @messageTypeName = message_type_name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @messageBody = message_body\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Logon_Triggers_Queue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ), TIMEOUT 500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 -- If there is no message, exit\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IF @@ROWCOUNT = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BREAK ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 -- If the message type is EventNotification do the actual work \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF (@messageTypeName =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/schemas.microsoft.com\/SQL\/Notifications\/EventNotification')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @data XML;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @SPID VARCHAR(5);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @LoginName NVARCHAR(128);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @data = CAST(@messageBody AS XML);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Get the SPID and the Login name using the value method\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @SPID = @data.value('(\/EVENT_INSTANCE\/SPID)[1]', 'VARCHAR(5)'); \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @LoginName = @data.value('(\/EVENT_INSTANCE\/LoginName)[1]', 'NVARCHAR(128)');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Check the login name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @LoginName=N'thomas_anderson' AND\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT COUNT(*) FROM sys.dm_exec_sessions\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE is_user_process = 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0AND original_login_name = 'thomas_anderson') &gt; 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Kill the current connection if there is already one session established \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- as thomas_anderson\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE ('KILL ' + @SPID);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0END;\r\n\r\nEND;\r\nEND;\r\nGO\r\n\r\n-- Link the stored procedure to the Logon_Triggers_Queue \r\nALTER QUEUE Logon_Triggers_Queue\r\n\u00a0\u00a0\u00a0 WITH STATUS=ON,\r\n\u00a0\u00a0 ACTIVATION (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 STATUS=ON,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PROCEDURE_NAME = usp_Logon_Triggers,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 MAX_QUEUE_READERS = 1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE AS SELF) ;\r\nGO<\/pre>\n<p>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:<\/p>\n<p><a href=\"http:\/\/www.simple-talk.com\/sql\/learn-sql-server\/service-broker-foundations-workbench\/\">http:\/\/www.simple-talk.com\/sql\/learn-sql-server\/service-broker-foundations-workbench\/<\/a><\/p>\n<p>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.<\/p>\n<p>The information posted to the <b>Logon_Triggers_Service,<\/b> about the <b>AUDIT_LOGIN<\/b> event, has the following structure:<\/p>\n<pre>&lt;EVENT_INSTANCE&gt;\r\n\u00a0 &lt;EventType&gt;event_type&lt;\/EventType&gt;\r\n\u00a0 &lt;PostTime&gt;post_time&lt;\/PostTime&gt;\r\n\u00a0 &lt;SPID&gt;spid&lt;\/SPID&gt;\r\n\u00a0 &lt;TextData&gt;text_data&lt;\/TextData&gt;\r\n\u00a0 &lt;BinaryData&gt;binary_data&lt;\/BinaryData&gt;\r\n\u00a0 &lt;DatabaseID&gt;database_id&lt;\/DatabaseID&gt;\r\n\u00a0 &lt;NTUserName&gt;nt_user_name&lt;\/NTUserName&gt;\r\n\u00a0 &lt;NTDomainName&gt;nt_domain_name&lt;\/NTDomainName&gt;\r\n\u00a0 &lt;HostName&gt;host_name&lt;\/HostName&gt;\r\n\u00a0 &lt;ClientProcessID&gt;client_process_id&lt;\/ClientProcessID&gt;\r\n\u00a0 &lt;ApplicationName&gt;application_name&lt;\/ApplicationName&gt;\r\n\u00a0 &lt;LoginName&gt;login_name&lt;\/LoginName&gt;\r\n\u00a0 &lt;StartTime&gt;start_time&lt;\/StartTime&gt;\r\n\u00a0 &lt;EventSubClass&gt;event_subclass&lt;\/EventSubClass&gt;\r\n\u00a0 &lt;Success&gt;success&lt;\/Success&gt;\r\n\u00a0 &lt;IntegerData&gt;integer_data&lt;\/IntegerData&gt;\r\n\u00a0 &lt;ServerName&gt;server_name&lt;\/ServerName&gt;\r\n\u00a0 &lt;DatabaseName&gt;database_name&lt;\/DatabaseName&gt;\r\n\u00a0 &lt;LoginSid&gt;login_sid&lt;\/LoginSid&gt;\r\n\u00a0 &lt;RequestID&gt;request_id&lt;\/RequestID&gt;\r\n\u00a0 &lt;EventSequence&gt;event_sequence&lt;\/EventSequence&gt;\r\n\u00a0 &lt;IsSystem&gt;is_system&lt;\/IsSystem&gt;\r\n\u00a0 &lt;SessionLoginName&gt;session_login_name&lt;\/SessionLoginName&gt;\r\n&lt;\/EVENT_INSTANCE&gt;<\/pre>\n<p>Most of the elements are self explanatory:<\/p>\n<ul>\n<li><b>event_type<\/b> is obviously <b>AUDIT_LOGIN<\/b>.<\/li>\n<li><b>post_time<\/b> contains the time when the event is posted to the service.<\/li>\n<li><b>spid<\/b> represents the ID of the session for the event.<\/li>\n<li><b>text_data<\/b> contains a semicolon-delimited list of all set options.<\/li>\n<li><b>binary_data<\/b> contains the session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers.<\/li>\n<li><b>database_id<\/b> is the ID of the default database or the ID of the database used by the USE database statement if any.<\/li>\n<li><b>nt_user_name <\/b>represents the Windows user name.<\/li>\n<li><b>nt_domain_name<\/b> represents the Windows domain to which the user belongs.<\/li>\n<li><b>host_name<\/b> contains the name of the computer on which the client is running.<\/li>\n<li><b>client_process_id<\/b> is the ID assigned by the host computer to the process where the client application is running.<\/li>\n<li><b>application_name<\/b> is the name of the client application.<\/li>\n<li><b>login_name<\/b> is the name of the login used.<\/li>\n<li><b>start_time<\/b> represents the time that the event started.<\/li>\n<li><b>event_subclass<\/b> indicates if the connection is pooled or non-pooled with values 1 for non-pooled and 2 for pooled.<\/li>\n<li><b>success<\/b> element indicates if the authentication succeeded and has the values 1 for success and 0 for failure. For the <b>AUDIT_LOGIN<\/b> event it will always contain value 1.<\/li>\n<li><b>integer_data<\/b> represents the network packet size.<\/li>\n<li><b>server_name<\/b> represents the name of the instance of SQL Server on which the event occurred.<\/li>\n<li><b>database_name<\/b> is the name of the database<\/li>\n<li><b>login_sid<\/b> contains the security identification number (SID) of the logged-in user.<\/li>\n<li><b>request_id<\/b> represents the ID of the request.<\/li>\n<li><b>event_sequence<\/b> represents the sequence of the event within the request.<\/li>\n<li><b>is_system<\/b> indicates if the event occurred for a user or a system process. In this case it would always be a user process.<\/li>\n<\/ul>\n<p>You can see a fragment of the event data on my computer:<\/p>\n<pre>&lt;EVENT_INSTANCE&gt;\r\n\u00a0 &lt;EventType&gt;AUDIT_LOGIN&lt;\/EventType&gt;\r\n\u00a0 &lt;PostTime&gt;...&lt;\/PostTime&gt;\r\n\u00a0 &lt;SPID&gt;56&lt;\/SPID&gt;\r\n\u00a0 &lt;TextData&gt;-- network protocol: LPC\r\nset quoted_identifier on\r\n...\r\n&lt;\/TextData&gt;\r\n\u00a0 &lt;BinaryData&gt;IAAAKDj0AQAAAAAA&lt;\/BinaryData&gt;\r\n\u00a0 &lt;DatabaseID&gt;1&lt;\/DatabaseID&gt;\r\n\u00a0 &lt;NTUserName&gt;Cristi&lt;\/NTUserName&gt;\r\n\u00a0 &lt;NTDomainName&gt;MICROTRAINING&lt;\/NTDomainName&gt;\r\n\u00a0 &lt;HostName&gt;MICROTRAINING&lt;\/HostName&gt;\r\n\u00a0 &lt;ClientProcessID&gt;2456&lt;\/ClientProcessID&gt;\r\n\u00a0 &lt;ApplicationName&gt;Microsoft SQL Server Management Studio - Query&lt;\/ApplicationName&gt;\r\n\u00a0 &lt;LoginName&gt;MICROTRAINING\\Cristi&lt;\/LoginName&gt;\r\n\u00a0 &lt;StartTime&gt;...&lt;\/StartTime&gt;\r\n\u00a0 &lt;EventSubClass&gt;1&lt;\/EventSubClass&gt;\r\n\u00a0 &lt;Success&gt;1&lt;\/Success&gt;\r\n\u00a0 &lt;IntegerData&gt;4096&lt;\/IntegerData&gt;\r\n\u00a0 &lt;ServerName&gt;MICROTRAINING&lt;\/ServerName&gt;\r\n\u00a0 &lt;DatabaseName&gt;master&lt;\/DatabaseName&gt;\r\n\u00a0\u00a0&lt;LoginSid&gt;AQUAAAAAAAUVAAAAFSWvRwfplC0jX2Nr6wMAAA==&lt;\/LoginSid&gt;\r\n\u00a0 &lt;RequestID&gt;0&lt;\/RequestID&gt;\r\n\u00a0 &lt;EventSequence&gt;290&lt;\/EventSequence&gt;\r\n\u00a0 &lt;IsSystem \/&gt;\r\n\u00a0 &lt;SessionLoginName \/&gt;\r\n&lt;\/EVENT_INSTANCE&gt;<\/pre>\n<p>We need only the SPID (session ID) of the connection that fired the event and the login name. We obtain them by using <b>value<\/b> method of the XML data type.<\/p>\n<p>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.<\/p>\n<p>Let&#8217;s see it at work!<\/p>\n<p>In SQL Server Management Studio, open a query window and login as <b>thomas_anderson<\/b>. Next, open a new query and login again as <b>thomas_anderson<\/b>. Don&#8217;t be fooled by the status message &#8220;Connected from SSMS&#8221;. 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:<\/p>\n<pre>\r\n\"Msg 233, Level 20, State 0, Line 0\r\n\r\nA transport-level error has occurred when sending the request to the server. \r\n(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)\"<\/pre>\n<p>At first look, it seems that we&#8217;ve succeeded in emulating a logon trigger, but let&#8217;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 &#8220;eventually&#8221;. 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.<\/p>\n<h3>Disabling our own mechanism<\/h3>\n<p>Just for educational purposes I will illustrate how we can make the Event Notifications workaround totally useless.<\/p>\n<p>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).<\/p>\n<p>On my computer, a one proc x86 machine, there are, by default, a maximum of 256 worker threads. I obtained this value from the <b>max_worker_count<\/b> column of the <b>sys.dm_os_sys_info<\/b> dynamic management view.<\/p>\n<p>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 <b>usp_Logon_Triggers<\/b> stored procedure. Ergo, if Mr. Anderson sends 256 requests, the Event Notifications-based mechanism won&#8217;t be able to kill them all.<\/p>\n<p>To run multiple requests at the same time we can use the OSTRESS command line utility, which you can download from:<\/p>\n<p><a href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?FamilyID=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&amp;DisplayLang=en\">http:\/\/www.microsoft.com\/downloads\/details.aspx?FamilyID=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&amp;DisplayLang=en<\/a>.<\/p>\n<p>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.<\/p>\n<p>So, open SQL Server Management Studio and run the following code:<\/p>\n<pre>USE DemoDB;\r\nGO\r\n-- Create demo table\r\nCREATE TABLE tblDemo(\r\n\u00a0\u00a0 ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,\r\n\u00a0\u00a0 VAL VARCHAR(128));\r\nGO\r\n-- Create a database user for Mr. Anderson\r\nCREATE USER thomas_anderson FOR LOGIN thomas_anderson;\r\nGO\r\n-- Grant INSERT permission to the user on the new table\r\nGRANT INSERT ON tblDemo TO thomas_anderson;\r\nGO<\/pre>\n<p>Then open a command line and navigate to the folder that contains the ostress.exe executable. Next type the following text and hit ENTER:<\/p>\n<p><code>ostress.exe -l600 -Uthomas_anderson -Pyukon9.0 -Slpc:%COMPUTERNAME% -n256 -Q\"INSERT INTO DemoDB..tblDemo SELECT 'I am in!';\" -q<\/code><\/p>\n<p>The <b>l<\/b> 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.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/442-Image2.gif\" alt=\"442-Image2.gif\" \/><\/p>\n<p>After running the above statement, switch back to SQL Server Management Studio and let&#8217;s see what happened by running:<\/p>\n<pre>SELECT COUNT(*) FROM DemoDB..tblDemo;<\/pre>\n<p>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.<\/p>\n<p>Besides this, if our user has enough rights to send an <b>ALTER QUEUE<\/b> statement to the server he can disable the whole mechanism.<\/p>\n<p>To conclude, the Event Notifications method didn&#8217;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.<\/p>\n<p>Before moving to the next section, please run the clean-up code:<\/p>\n<pre>USE DemoDB;\r\nGO\r\n-- Drop the event notification\r\n-- Can be done from any database context as it is defined\r\n-- at the server level\r\nDROP EVENT NOTIFICATION Successfull_Login_Notification\r\nON SERVER;\r\nGO\r\n-- Drop the service\r\nDROP SERVICE Logon_Triggers_Service;\r\nGO\r\n-- Drop the route\r\nDROP ROUTE Logon_Triggers_Route;\r\nGO \r\n-- Drop the queue\r\nDROP QUEUE Logon_Triggers_Queue;\r\nGO\r\n-- Drop the stored procedure\r\nDROP PROCEDURE usp_Logon_Triggers\r\nGO<\/pre>\n<h3>Logon Triggers<\/h3>\n<p class=\"start\">As we&#8217;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.<\/p>\n<p>A logon trigger fires when a session is established. At that point the LOGON event is raised. The Logon event corresponds to the <b>AUDIT_LOGIN<\/b> SQL Trace event used in the previous section.<\/p>\n<p>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&#8230;then it&#8217;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&#8217;re done.<\/p>\n<p>Let&#8217;s see the previous example written using a logon trigger instead of Event Notifications:<\/p>\n<pre>USE master;\r\nGO\r\nCREATE LOGIN security_login WITH PASSWORD = 'yukon9.0'; \r\nGO\r\nGRANT VIEW SERVER STATE TO security_login;\r\nGO\r\nCREATE TRIGGER connection_limit_trigger\r\nON ALL SERVER WITH EXECUTE AS 'security_login'\r\nFOR LOGON\r\nAS\r\nBEGIN\r\nIF ORIGINAL_LOGIN()= 'thomas_anderson' AND\r\n\u00a0\u00a0\u00a0 (SELECT COUNT(*) FROM sys.dm_exec_sessions\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE is_user_process = 1 AND\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 original_login_name = 'thomas_anderson') &gt; 1\r\n\u00a0\u00a0\u00a0 ROLLBACK;\r\nEND;<\/pre>\n<p>The <b>sys.dm_exec_sessions<\/b> dynamic management view allows you to see all sessions on a SQL Server instance, provided that you have the <b>VIEW SERVER STATE<\/b> permission (or you belong to the sysadmin server role). Otherwise you will see only your current session.<\/p>\n<p>To prevent granting unnecessary rights to Mr. Anderson, we create a new login and grant it the <b>VIEW<\/b><b> SERVER STATE<\/b> 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:<\/p>\n<ol>\n<li>The <b>ROLLBACK<\/b> statement rolls back all data modification and will close the connection, but if there are any statements after the <b>ROLLBACK<\/b>, they will be executed. Any data modification that occurs after the <b>ROLLBACK<\/b> statement is not rolled back to allow you eventually to log the connection attempt in a table.<\/li>\n<li>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&#8217;t try to return a result set or your trigger will fail.<\/li>\n<li>Any <b>PRINT<\/b> messages, messages issued using <b>RAISERROR<\/b> statement or other error messages would be diverted to the SQL Server log.<\/li>\n<\/ol>\n<p>To test it, open SQL Server Management Studio and then open two query windows as <b>thomas_anderson<\/b>. The second log on process will fail and an error message will be displayed.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/442-Image3.gif\" alt=\"442-Image3.gif\" \/><\/p>\n<p>Quite simple isn&#8217;t it?<\/p>\n<h4>Creating logon triggers<\/h4>\n<p>To create a logon trigger, you can use the <b>CREATE TRIGGER<\/b> statement with the following syntax:<\/p>\n<pre>CREATE TRIGGER <em>trigger_name <\/em>\r\nON ALL SERVER \r\n[ WITH &lt;logon_trigger_option&gt; [ <strong>,<\/strong><em>...n <\/em>] ]\r\n{ FOR<em> <\/em>| AFTER } LOGON\u00a0 \r\nAS { <em>sql_statement<\/em>\u00a0 [ ; ] [ <strong>,<\/strong>...<em>n <\/em>] | EXTERNAL NAME &lt; method specifier &gt;\u00a0 [ ; ] }\r\n<strong>&lt;logon_trigger_option&gt; ::=<\/strong>\r\n\u00a0\u00a0\u00a0 [ ENCRYPTION ]\r\n\u00a0\u00a0\u00a0 [ EXECUTE AS Clause ]\r\n\r\n<strong>&lt;method_specifier&gt; ::=<\/strong>\r\n<em>\u00a0\u00a0\u00a0 assembly_name<\/em><strong>.<\/strong><em>class_name<\/em><strong>.<\/strong><em>method_name<\/em><\/pre>\n<p>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.<\/p>\n<p>The <b>WITH ENCRYPTION<\/b> clause allows you to encrypt the definition of the trigger.<\/p>\n<p>If you do not specify an execution context for the trigger, using the <b>EXECUTE AS<\/b> clause, the default behavior is to use the context of the caller of the module.<\/p>\n<p>Once created, you can modify or drop logon triggers using the <b>ALTER TRIGGER<\/b> or <b>DROP TRIGGER<\/b> statements, respectively.<\/p>\n<h4>Getting information inside logon triggers<\/h4>\n<p>To get at information inside a logon trigger you can use the <b>EVENTDATA()<\/b> function. In this particular case, this will return xml information having the following schema:<\/p>\n<pre>&lt;EVENT_INSTANCE&gt; \r\n\u00a0\u00a0\u00a0 &lt;EventType&gt;event_type&lt;\/EventType&gt; \r\n\u00a0\u00a0\u00a0 &lt;PostTime&gt;post_time&lt;\/PostTime&gt; \r\n\u00a0\u00a0\u00a0 &lt;SPID&gt;spid&lt;\/SPID&gt; \r\n\u00a0\u00a0\u00a0 &lt;ServerName&gt;server_name&lt;\/ServerName&gt; \r\n&lt;LoginName&gt;login_name&lt;\/LoginName&gt; \r\n&lt;LoginType&gt;login_type&lt;\/LoginType&gt; \r\n&lt;SID&gt;sid&lt;\/SID&gt; \r\n&lt;ClientHost&gt;client_host&lt;\/ClientHost&gt; \r\n&lt;IsPooled&gt;is_pooled&lt;\/IsPooled&gt; \r\n&lt;\/EVENT_INSTANCE&gt;<\/pre>\n<p>The <b>LOGON<\/b> event schema is somewhat similar to the AUDIT_LOGIN event schema, only simpler:<\/p>\n<ul>\n<li><b>event_type<\/b> is <b>LOGON<\/b>.<\/li>\n<li><b>post_time<\/b> contains the time for session request.<\/li>\n<li>The<b> is_pooled<\/b> element indicates if the connection is using connection pooling (value 1) or not (value 0).<\/li>\n<\/ul>\n<p>The rest of the elements are as described for the <i>AUDIT_LOGIN<\/i> event.<\/p>\n<p>Besides the <b>EVENTDATA()<\/b> function, you can also use system functions such as <b>ORIGINAL_LOGIN()<\/b> used in the previous example if you need additional information.<\/p>\n<h4>CLR Logon Triggers<\/h4>\n<p>In most everyday scenarios, you probably would not need CLR code to implement a Logon Trigger. However, to demonstrate that there&#8217;s nothing too complicated about it, here&#8217;s an example. We will create a CLR Logon Trigger to audit successful logons in a table:<\/p>\n<ol>\n<li>Open SQL Server Management Studio and in a new query window run the following code to create the audit table:\n<pre>USE DemoDB;\r\nGO\r\n-- Create the audit table\r\nCREATE TABLE tblAudit(\r\n\u00a0\u00a0 [EventID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,\r\n\u00a0\u00a0 [PostTime] DATETIME,\r\n\u00a0\u00a0 [SPID] SMALLINT,\r\n\u00a0\u00a0 [ServerName] NVARCHAR(128),\r\n\u00a0\u00a0 [LoginName] NVARCHAR(128),\r\n\u00a0\u00a0 [LoginType] NVARCHAR(64),\r\n\u00a0\u00a0 [SID] VARBINARY(85),\r\n\u00a0\u00a0 [ClientHost] NVARCHAR(128),\r\n\u00a0\u00a0 [IsPooled] BIT);\r\nGO\r\nUSE master;\r\nGO\r\n-- Create a login to use for the execution context\r\n-- of the new trigger\r\nCREATE LOGIN audit_login WITH PASSWORD = 'yukon9.0'; \r\nGO\r\n-- Create a database user for the new login\r\nUSE DemoDB;\r\nCREATE USER audit_user FOR LOGIN audit_login;\r\nGO \r\n-- Grant INSERT permission to the new user\r\nGRANT INSERT ON tblAudit TO audit_user;\r\nGO<\/pre>\n<\/li>\n<li>Then open Visual Studio 2005.<\/li>\n<li>On the File menu select New Project.<\/li>\n<li>Expand Database projects and select SQL-CLR as the project type.<\/li>\n<li>In the same window SQL Server Project C# template, enter <b>CLRLogonTrigger<\/b> in the Name textbox, C:\\Projects for location and click OK.<br \/>\n<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/442-Image4.gif\" alt=\"442-Image4.gif\" \/><\/li>\n<li>On the Add Database Reference window, click Cancel.<\/li>\n<li>In the Solution Explorer, right click the solution name and select Add &#8211; Trigger.<br \/>\n<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/442-Image5.gif\" alt=\"442-Image5.gif\" \/><\/li>\n<li>In the Add New Item window &#8211; enter for the name <b>DemoTrigger<\/b>.<br \/>\n<img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/442-Image6.gif\" alt=\"442-Image6.gif\" \/><\/li>\n<li>Replace the auto-generated code with the following:<\/li>\n<\/ol>\n<pre>using System;\r\nusing System.Data;\r\nusing System;\r\nusing System.Data;\r\nusing System.Data.SqlClient;\r\nusing Microsoft.SqlServer.Server;\r\nusing System.Xml;\r\nusing System.Transactions;\r\n\r\npublic partial class Triggers\r\n{\r\n\r\n\u00a0\u00a0\u00a0 [Microsoft.SqlServer.Server.SqlTrigger(Name = \"DemoTrigger\", Target = \"ALL SERVER\", Event = \"LOGON\")]\r\n\u00a0\u00a0\u00a0 public static void DemoTrigger()\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 using (SqlConnection connection = new SqlConnection(@\"context connection=true\"))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Get the trigger context\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlTriggerContext triggContext = SqlContext.TriggerContext;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Create a new SqlCommand\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlCommand command = new SqlCommand();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Get event's data\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XmlDocument doc = new XmlDocument();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 doc.LoadXml(triggContext.EventData.Value);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Open the connection.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connection.Open();\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Build the INSERT statement using parameters\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Connection = connection;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.CommandText = @\"INSERT INTO DemoDB..tblAudit(\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[PostTime], \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[SPID], \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[ServerName], \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[LoginName], \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[LoginType], \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[SID], \r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[ClientHost], \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [IsPooled]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES (\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@PostTime, \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@SPID, \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ServerName, \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@LoginName, \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@LoginType, \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@SID, \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@ClientHost, \r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@IsPooled\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Add parameters\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ PostTime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlParameter parameter = new SqlParameter(\"@PostTime\", SqlDbType.DateTime);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/PostTime\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ SPID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@SPID\", SqlDbType.SmallInt);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/SPID\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ ServerName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@ServerName\", SqlDbType.NVarChar, 128);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/ServerName\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ LoginName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@LoginName\", SqlDbType.NVarChar, 128);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/LoginName\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ LoginType\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@LoginType\", SqlDbType.NVarChar, 64);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/LoginType\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ SID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@SID\", SqlDbType.VarBinary, 85);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/SID\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ ClientHost\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@ClientHost\", SqlDbType.NVarChar, 128);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/ClientHost\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ IsPooled\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter = new SqlParameter(\"@IsPooled\", SqlDbType.SmallInt);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameter.Value = doc.DocumentElement.SelectSingleNode(\"\/EVENT_INSTANCE\/IsPooled\").InnerText;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters.Add(parameter);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Execute the statement\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0command.ExecuteNonQuery();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 catch (Exception ex)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/If the audit failed prevent the connection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Get the current transaction and roll it back.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Transaction trans = Transaction.Current;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trans.Rollback();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n}<\/pre>\n<p>10. Build the project and then close Visual Studio.<\/p>\n<p>11. Back in SQL Server Management Studio run the following script to create the trigger:<\/p>\n<pre>USE [master]\r\nGO\r\n-- Import the assembly\r\nCREATE ASSEMBLY [LogonTriggers]\r\nFROM \r\n'C:\\Projects\\CLRLogonTrigger\\CLRLogonTrigger\\bin\\Debug\\CLRLogonTrigger.dll';\r\nGO\r\n-- Create the trigger\r\nCREATE TRIGGER [CLRLogonTrigger] \r\nON ALL SERVER WITH EXECUTE AS 'audit_login'\r\n\u00a0FOR LOGON AS\r\n\u00a0EXTERNAL NAME [LogonTriggers].[Triggers].[DemoTrigger]\r\nGO<\/pre>\n<p>12. To test the trigger open a new Query Window and run the following:<\/p>\n<pre>SELECT \r\nEventID,SPID,ServerName,LoginName,LoginType\r\nFROM DemoDB..tblAudit;<\/pre>\n<p>You can see the output on my computer (and it should be similar on yours):<\/p>\n<pre>EventID SPID ServerName\u00a0\u00a0\u00a0 LoginName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LoginType\r\n------- ---- ----------\u00a0\u00a0 \u00a0-------------------- ------------------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 54\u00a0\u00a0 MICROTRAINING MICROTRAINING\\Cristi Windows (NT) Login<\/pre>\n<p>What did we just do? We used the attributes of the CLR module (<b>Name<\/b>, <b>Target<\/b> and <b>Event<\/b>) to specify that the <b>DemoTrigger<\/b> method should be registered as a trigger and also specify the name, the target and the event that activates the trigger.<\/p>\n<p>The information about the event (<b>LOGON<\/b> event in this case) is exposed in the <b>EventData<\/b> property of the <b>SqlTriggerContext<\/b> class.<\/p>\n<p>In T-SQL, we would use XQuery to parse the XML data but in our .NET code we can use an <b>XmlDocument<\/b>. 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.<\/p>\n<p>After you&#8217;ve tested the CRL trigger, you can clean-up by running the following T-SQL code:<\/p>\n<pre>USE master;\r\nGO\r\n-- Drop the trigger\r\nDROP TRIGGER [CLRLogonTrigger] \r\nON ALL SERVER;\r\nGO \r\n-- Drop the assembly\r\nDROP ASSEMBLY LogonTriggers;\r\nGO<\/pre>\n<p>Additionally, you can drop the DemoDB database and the logins created:<\/p>\n<pre>USE master;\r\nGO\r\nDROP DATABASE DemoDB;\r\nGO\r\nDROP LOGIN security_login;\r\nGO\r\nDROP LOGIN thomas_anderson;\r\nGO<\/pre>\n<h3>Special circumstances<\/h3>\n<p>Pop quiz: You run the following statement<\/p>\n<pre>CREATE TRIGGER DenyAnyConnection\r\nON ALL SERVER WITH EXECUTE AS 'security_login'\r\nFOR LOGON\r\nAS\r\nBEGIN\r\n\u00a0\u00a0\u00a0 ROLLBACK;\r\nEND;<\/pre>\n<p>What happens after you hit F5?<\/p>\n<p>A. You&#8217;ve succeeded to lock yourself out of the server.<\/p>\n<p>B. You can still connect.<\/p>\n<p>Most of you will tend to choose A, which is the wrong option and that&#8217;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.<\/p>\n<p>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 <b>DISABLE TRIGGER<\/b> statement to disable the trigger. Alternatively, I could have used the DAC to save the day.<\/p>\n<p>Therefore, remember that you have those two options to disable a Logon Trigger that prevents any connection.<\/p>\n<ol>\n<li>Use DAC<\/li>\n<li>Stop your server and restart it using the minimal configuration mode<\/li>\n<\/ol>\n<p>When you can, favor the DAC method instead of restarting your server.<\/p>\n<h3>Logon Triggers on SQL Server 2000<\/h3>\n<p class=\"start\">While doing the research for this article, I encountered a question on a forum:<\/p>\n<p>&#8220;What complicated hacks can be used to implement logon triggers on SQL Server 2000?&#8221;<\/p>\n<p>I wasn&#8217;t aware that such a thing was possible, so I forwarded the question to an MS guy. He answered: &#8220;Sure! We have a patch that makes a SQL Server 2000 behave like a 2005 server. There&#8217;s just one problem: it&#8217;s expensive. It costs almost as much as a 2005 box!&#8221;<\/p>\n<p>I&#8217;ve worked with SQL Server 2005 since 2003 at that time and I didn&#8217;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:<\/p>\n<p><a href=\"http:\/\/www.microsoft.com\/sql\/howtobuy\/default.mspx\">http:\/\/www.microsoft.com\/sql\/howtobuy\/default.mspx<\/a>.<\/p>\n<p>I felt like a dummy for the second time. It was a link for buying SQL Server 2005. Anyway those two days weren&#8217;t lost. I learned that developers from Microsoft can have a great sense of humor.<\/p>\n<p>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.<\/p>\n<h3>Instead of a conclusion<\/h3>\n<p class=\"start\">The way I see logon triggers is that they are just a framework that allow you to implement lots of custom scenarios.<\/p>\n<p>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.<\/p>\n<p>Now I have some observations that you can call &#8220;best practices&#8221; if you like, but may more accurately be called &#8220;common sense&#8221;:<\/p>\n<ul>\n<li>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 &#8211; Object Explorer and the trigger fires. Then he wants to see an object definition &#8211; the trigger fires again. You get my point.<\/li>\n<li>If you have to use transactions inside logon triggers remember that if you issue a <b>ROLLBACK TRANSACTION<\/b>, the connection is gone.<\/li>\n<li>Use error handling inside your triggers. Guess what happens if a transaction-abort error occurs? Not a hard question and the answer is &#8220;connection closed&#8221;.<\/li>\n<li>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.<\/li>\n<\/ul>\n<p>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&#8217;s another story!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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! &hellip;<\/p>\n","protected":false},"author":43858,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4824,4619,4150,4151,4252,4825],"coauthors":[48712],"class_list":["post-314","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-etl","tag-security","tag-sql","tag-sql-server","tag-t-sql-programming","tag-tsql-login-sql-server-login-trigger-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/314","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/43858"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=314"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/314\/revisions"}],"predecessor-version":[{"id":74112,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/314\/revisions\/74112"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=314"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=314"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}