Error: U02012031 SQL agent job fails due to trigger execution - Automic Workload Automation

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation
• Automic SaaS

Issue/Introduction

When running or upgrading to a newer SQL agent (v21 or v24), jobs fail with a database driver error related to login triggers.  The following shows up in the job report:

2024-09-26 19:46:24 - U02012031 The database driver returned the following error message:
2024-09-26 19:46:24             Logon failed for login '####' due to trigger execution.

Environment

• Automic Workload Automation
• Microsoft SQL Server
• SQL Agent v21 and higher

Cause

The error occurs because login triggers defined on the MS SQL database block the connection from newer JDBC drivers or Java versions used by the updated agent. Newer versions often have increased security requirements that trigger these database-level restrictions.

Resolution

Identify and evaluate the login triggers on the database.

1. Work with a Database Administrator (DBA) to identify existing logon triggers using the following SQL query:

sql

 

SELECT name AS [Trigger Name],        is_disabled AS [Is Disabled],        create_date AS [Create Date],        modify_date AS [Modify Date] FROM sys.server_triggers WHERE OBJECTPROPERTY(object_id, 'IsLogonTrigger') = 1;

2. If triggers are found, evaluate if they can be disabled or modified to allow the agent login.
3. As a temporary workaround, downgrade the JDBC driver (e.g., from sqljdbc_12 to sqljdbc_9) in the agent's jdbc folder to bypass certain trigger executions, though addressing the trigger directly is the recommended long-term solution.
4. Ensure the auth_dll is present in the agent bin directory.

 

Additional Information

Note: Validate the driver version, which is located at the jdbc folder and that the auth_dll is in the bin directory.