SUBERROR Failed Status(database access failed)

Products

• CA Workload Automation DE - Business Agents (dSeries)
• CA Workload Automation DE
• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)
• CA Workload Automation DE - Scheduler (dSeries)

Issue/Introduction

We installed the ca agent and installed msql agent on Production instance, both agent and mssql is running without any problems. When we trying to retrieve the MSSQL server info i am getting below error.

07/11/2020 11:29:30.265+1000 5 SqlPlugin.Thread-6.CybWOBSpoolOutput.emitSpoolFileLine[:13] - Getting sql exception ... I/O Error: DB server closed connection.
07/11/2020 11:29:30.265+1000 5 SqlPlugin.Thread-6.CybWOBSpoolOutput.emitSpoolFileLine[:13] - Retry 1, monitoring is false
07/11/2020 11:29:30.265+1000 1 SqlPlugin.Thread-6.ThreadedRequestHandler.run[:191] - java.sql.SQLException: I/O Error: DB server closed connection.
                                                                                      at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2311)
                                                                                      at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
                                                                                      at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:352)
                                                                                      at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
                                                                                      at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:185)
                                                                                      at java.sql.DriverManager.getConnection(DriverManager.java:664)
                                                                                      at java.sql.DriverManager.getConnection(DriverManager.java:247)
                                                                                      at cybermation.plugins.sql.datasource.Registry.begin(Registry.java:271)
                                                                                      at cybermation.plugins.sql.datasource.AbstractDBAccessHandler.execute(AbstractDBAccessHandler.java:41)
                                                                                      at cybermation.plugins.sql.requesthandler.ThreadedRequestHandler.execute(ThreadedRequestHandler.java:101)
                                                                                      at cybermation.plugins.sql.requesthandler.ThreadedRequestHandler.run(ThreadedRequestHandler.java:127)
                                                                                      at java.lang.Thread.run(Thread.java:748)
                                                                                     Caused by: java.io.IOException: DB server closed connection.
 

MS SQL Server is using SSL/Forced encryption and configured to use jTDS driver. SQL log shows error like below;
"Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: xx.xxx.xxx.xx]"

                                                                                     

Environment

Component : CA Workload Automation Agent for MS SQL Server

Resolution

Suggestions:

1. To use jTDS driver for SSL connection (Windows authentication)

- Download jTDS driver and copy the jtds-x.x.x.jar to <agent_installdir>\jars\ext directory

- Update connection string like below;

db.url=jdbc:jtds:sqlserver://<dbhost>:<dbport>;DatabaseName=<dbname>;Integrated Security=True;domain=<domain name>;ssl=require

2. To use SQL JDBC driver for SQL Server authentication

- Download latest SQL JDBC driver to replace the jTDS driver in <agent_installdir>\jars\ext directory

For example, replace jtds-1.3.1.jar with mssql-jdbc-8.2.2.jre8.jar file (Do NOT rename the jtds-1.3.1.jar file)

- Update the MSSQL plugin system properties file (<agent_installdir>\config\sqlagent.properties) with SQL JDBC driver classname;

#db.classname=net.sourceforge.jtds.jdbc.Driver
db.classname=com.microsoft.sqlserver.jdbc.SQLServerDriver

- Update the connection string in target properties file like below;

db.url=jdbc:sqlserver://<dbhost>:<dbport>;DatabaseName=<dbname>;encrypt=true;trustServerCertificate=true

Note: Restart the agent for the change to take effect.