Logon failed for login due to trigger execution.

While trying to connect SQL Server using SQL Server Management Studio Object Explorer, I got the following error message "Logon failed for login 'sqluser' due to trigger execution.".

When I see the error message occured during connect to SQL Server 2008 R2 database, I realized that the cause of the sql exception is the SQL Server logon trigger which I created recently for SQL Server login audit purposes.

Since database administrators and t-sql developers are first introduced with logon triggers in SQL Server 2005, I guess sharing how to troubleshoot with logon triggers in SQL Server will be useful for many. This logon trigger sample case is experienced in SQL Server 2008 R2, but the steps for the solution of "Logon failed for login due to trigger execution" error is same in SQL Server 2005, SQL Server 2008 and in SQL Server 2011.

Microsoft SQL Server, Error: 17892 Detail

Error detail due to SQL Server logon trigger execution :

TITLE: Connect to Server
Cannot connect to LOCALHOST.
ADDITIONAL INFORMATION:

Logon failed for login 'sqluser' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

The SQL Server Logon trigger error details are as follows:

Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Possible Reasons of SQL Server Logon Trigger Errors

SQL logon triggers are critical objects that can effect the availability of SQL Server by preventing sql logins and SQL Server users to connect to SQL Server. The SQL engine will throw this error when the login user does not have required permissions and priviledges on the database trigger and related sql objects. Another reason is a programmatic problem after logon trigger is executed, probably a bug in sql code of the SQL Server logon trigger.

If the problem is related with permissions, SQL Server administrator users or SQL Server sa users will be able to connect to SQL Server.
If SQL Server sa user or other SQL Server administrator users can not connect to SQL Server instance, then the reason is a bug in the sql trigger code.

Solution to SQL Server Logon Trigger Errors

In such a case when an error has occurred after logon trigger execution, the only solution for SQL Server administrators to connect SQL Server 2008 instance is using the SQL Server dedicated administrator connection aka SQL Server DAC.

For both cases described as the possible reasons for Logon Trigger errors, SQL Server database administrators can look at error logs at "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG" for sql error details.
Please note that the above SQL Server Log folder will change according to the installed SQL Server version and installation path, etc.

SQL Server Dedicated Administrator Connection - SQL DAC

In order to connect SQL Server instance through SQL Server DAC, open SSMS (SQL Server Management Studio).
Then click on the New Query button. Please note that SQL Server administrators can not connect to SQL databases using Object Explorer window because Dedicated administrator connections are not supported through Object Explorer.

In the "Server name" textbox type "ADMIN:instancename" by replacing the instance name with your target SQL Server database instance name.
The "ADMIN:" in the SQL Server name is indicating you are trying to connect SQL Server using dedicated administrator connection which is known as SQL Server DAC.

After you connect to SQL Server with sql DAC, you can execute the following t-sql script to disable logon trigger or drop SQL Server logon trigger.
After DBA disable SQL Server logon trigger or drop it, sql users will be able to connect SQL Server instance successfully.

If you don't know the SQL logon trigger name, you can execute sql select statement over sys.server_triggers below to list all SQL Server triggers defined for all databases

The SQL Server logon trigger is listed in SSMS (SQL Server Management Studio) Object Explorer window under the Server Objects - Triggers node as follows.

Another method to connect SQL Server is using SQLCMD with Dedicated Administrator Connection - DAC.
In order to connect using SQLCMD utility, open CMD screen. Type sqlcmd.
Using the -A, SQL Server database administrators can connect SQL Server instance with dedicated administrator connection over sqlcmd utility.

For my case when I experienced the "Logon failed for login due to trigger execution." SQL Server error, the reason was DDL structure of the table where I log SQL logins using the SQL Server logon trigger.

The SQL Server error log helped me to find out the error details. After I alter log table structure for allowing NULL values, SQL Server sa user and admin users were able to connect SQL Server instance successfully.

But the users that do not have administrative rights on the server failed to logon SQL Server because of lack of permissions required to acccess sql objects used in logon trigger.

Enable SQL Logon Trigger

After you disable SQL logon trigger and make required changes, you can enable trigger by executing the following t-sql command :