CA WA DE MS SQL 2016 database and named instances

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

DE fails to start when connecting to MS-SQL database using a named instance.

Error:

Unable to connect to the database. Error message: The TCP/IP connection to the host SQLHOSTMyProd, port 12345 has failed. Error: "SQLHOSTMyProd. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

 

Environment

Release : 12.1, 12.2

Component : CA WORKLOAD AUTOMATION DE (DSERIES)

Cause

There can be several issues.

The port in the firewall may be blocked.  If this is a new database, check the port.

The named instances in JDBC string uses single \ (backslash) after server name.

The named instance may be getting added to the host name of SQL server, which will result in incorrect hostname.

Resolution

Make sure the ports are open from DE host to MS-SQL database host.

If DE is on Linux or UNIX and using named instance, then provide two \\ (backslashes in the JDBC string.  The JDBC string is kept in the db.properties in <install_dir>/conf.

E.g.

jdbc.URL=jdbc:sqlserver://;servername=SQLHOST\\PRODINSTANCE;port=12345;DatabaseName=dseriesDB;selectMethod=cursor

The named instance in above example is after servername=SQLHOST .  Add two \\, the first one escapes the second one.

Once changes are done, restart DE using startServer in <install_dir>/bin.