SQL and Windows Authentication with WA Agent Database/SQL Plugins

Products

• Workload Automation Agent
• CA Workload Automation AE - Business Agents (AutoSys)
• ESP dSeries Workload Automation - Business Agents (dSeries)
• ESP dSeries Workload Automation - System Agent (dSeries)

Issue/Introduction

User with SQL database and Windows authentication have issues and get these errors:

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:XXXXXXXX-1234-5678-9012-34567890XXXX
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3422)..
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-9.4.1.x64 in java.library.path

-----

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'xxxx'. ClientConnectionId:xxxxxxxx-4635-4012-a48a-fdc4xxxxxxxx

-----

 

Environment

Release: 12.x, 24.x

Components:

• Workload Automation Agent for Databases
• Workload Automation Agent for Microsoft SQL Server

OS: Windows only

Cause

The Database/SQL plugin will need new JDBC driver and DLL installed with the agent.

Resolution

Steps:

1. Download the latest JDBC driver from Microsoft. Extract the jar file from the zip. It will have this naming format:
   • mssql-jdbc-9.x.x.jre8 (e.g mssql-jdbc-9.4.1.jre8)
2. Copy the jar to <agent install directory>\jars\ext
   • E.g. "C:\Program Files\CA\WA Agent\jars\ext"
3. Remove any existing sql JDBC jar files (move it out of agent directory, don't rename).
4. Copy the auth DLL from JDBC package downloaded from Microsoft. It will be under: "sqljdbc_9.4/enu/auth/x64"
   • The DLL naming format is:
   • mssql-jdbc_auth-9.x.x.x64.dll, e.g. mssql-jdbc_auth-9.4.1.x64.dll
5. Copy that DLL to "<agent install directory"
   • E.g. "C:\Program Files\CA\WA Agent"

Additional Information

Sample JDBC string for domain user:

jdbc:sqlserver://db_host:1433;databaseName=MyDB;integratedSecurity=true;authenticationScheme=NTLM

The "authenticationScheme=NTLM" is optional and may be required if NTLM is still being used.  The Database plugin may not be able to login as well.  If DB accepts integrated and native authentication then start agent as AD user that can login to SQL.  

There is a recommendation from Microsoft about it as well. Make sure that agent machine is part of the same domain as SQL DB and add machine account (it has $ at end) to the DB.  Consult Windows admin about machine account where agent is installed, then add it to the DB.  The following is a sample SQL to add machine accounts

CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS;