Users experience login issues to SQL Server when Logon Triggers are enabled


Symptoms


After creating a logon trigger an attempt to login to SQL Server may fail with the following error message:

Msg 17892, Level 14, State 1, Server<Server Name>, Line 1
Logon failed for login <Login Name>due to trigger execution.

Cause


The problem could occur if there is an error when executing trigger code for that specific user account. Some of the scenarios include:
  • The trigger tires to insert data into a table that does not exist.
  • The login does not have permissions to the object that is referred to by the logon trigger.

Resolution


You can use one of the resolutions below depending on the scenario you are in.

Case 1: You currently have access to an open session to SQL server under an admin account

In this case you can take the corrective action that is needed to fix your trigger code.

Example 1: If an object referred to by the trigger code does not exist, create that object so that the login trigger can execute successfully.

Example 2: If an object referred to by the trigger code does exist but users do not have permissions, grant them the necessary privileges to access the object.

 Alternatively, you can just drop or disable the login trigger so that users can continue to login to SQL Server.

 

Case 2: You do not have any current session that is open under admin privileges, but Dedicated Administrator Connection (DAC) is enabled on the SQL Server.

In this case you can use the DAC connection to take the same steps as discussed in Case 1 since DAC connections are not affected by Login triggers. For more information on DAC connection refer to the following topic in SQL Server 2008 Books Online

Using a Dedicated Administrator Connection

To check whether DAC is enabled on your SQL Server you can check SQL Server error log for a message that is similar to the following.

2010-02-09 16:17:44.150 Server Dedicated admin connection support was established for listening locally on port 1434.

Case 3: You neither have DAC enabled on your server nor have an existing admin session to SQL Server.

In this scenario, the only way to remediate the problem would be to take the following steps:

  1. Stop SQL Server and related services.
  2. Start SQL Server from the command prompt using the startup parameters –c, -m and –f. Doing this disables the login trigger and lets you perform the same remedial measures that are discussed under Case 1 above.
  3. Note: The above procedure requires a SA or an equivalent administrator account. 

For more information about these and other startup options you can refer to the following topic in SQL Server Books Online

More Information


Another situation where logon triggers fail, in my experience is when using the EVENTDATA function.  This function returns XML, and its case sensitive.  So, when a customer created the following logon trigger, intending to block access based on IP address, they ran into trouble:

CREATE TRIGGER tr_logon_CheckIP 
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF 
IS_SRVROLEMEMBER('sysadmin'1
  
BEGIN
   DECLARE 
@IP NVARCHAR(15);
   
SET @IP (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]''NVARCHAR(15)'));
   
IF NOT EXISTS(SELECT IP FROM DBAWork.dbo.ValidIP WHERE IP @IP)
     
ROLLBACK;
  
END;
END;
GO
 

My customer didn't maintain case when copying this from the internet on this part of the trigger:

SELECT EVENTDATA().value('(/event_instance/clienthost)[1]''NVARCHAR(15)'));

 

As a consequence, EVENTDATA always returned NULL, and all his SA equivalent logins were denied access.  In this case the DAC connection was not enabled, so we had no choice but to restart the server with the startup parameters listed above to drop the trigger.