Using Windows Authentication for SQL Agent

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

When pushing the "Test" button on a SQL connection object, the SQL agent uses credentials as a local DB Account instead of an AD domain account. 
The following message appears in a pop-up window when trying to test connection using Windows Authentication rather than a SQL Login.

"This driver is not configured for integrated authentication."

 

Environment

Release:11.2, 12.0, 12.1, 12.2, 12.3
Component: Automation Engine

Resolution

The following settings and steps must be implemented to make the SQL Agent use an AD account:

-The correct sqljdbc_auth.dll must be in the bin directory (ensure the file's correct architecture, e.g. x64)
- The parameter "WindowsAuthentication" in the Agent's .ini must be set to "1" instead of "0"
- In Client 0, in UC_HOSTCHAR_DEFAULT the parameter "ANONYMOUS_JOB" needs to be set to "Y"
- Add a Connection Properties Extension in the connection object's advanced settings with parameter = integratedSecurity and value = true:

The Agent will need to be restarted by a domain user after all the steps above have been completed. If the service manager is being used, then this can be accomplished by either starting the service manager with a domain user (not recommended) or changing the properties for the Agent on the Service Manager dialog:

Additional Information

If you want to test the Connection Object, do not use the Test button inside the CONN object itself: This button uses the DB Service for the testing which doesn't have the necessary elements.

Instead, create a test job that uses the Connection Object, then run the job or use the Test button inside job for testing the connection.