Errors attempting to install the Scheduler on Linux using a MS-SQL database with a Named Instance

book

Article ID: 207071

calendar_today

Updated On:

Products

CA Workload Automation AE - Scheduler (AutoSys)

Issue/Introduction

During the Scheduler installation on Linux, having selected MS-SQL as the database type, it prompts you to enter the database host.
Where the MS-SQL database is using a Named Instance, it asks you to enter it using this format:  HOST\INSTANCE

After entering this (and also the DB port in the following prompt), the installation cannot proceed and this error is received:

Error:
[CAUAJM_E_112515] Unable to connect to the "HOST\INSTANCE" database
server using the database client utility. For more information, refer to the
log file.

Cause

The Linux installer tests the connection to the database using both a bundled JDBC and the 'sqlcmd' command (from linux package mssql-tools).

The sqlcmd command on Linux requires two backslashes like this:

sqlcmd -U user -P password -S HOST\\INSTANCE

If using a non default port for the database (default is 1433), the command must be:

sqlcmd -U user -P password -S HOST\\INSTANCE,PORT

The installation asks for HOST\INSTANCE, so the sqlcmd command ends up badly formatted with a single backslash, causing it to fail.
If you try using double backslashes during the install, it also fails as this affects the formatting of the JDBC connect string the installer uses.

Environment

Release : 12.0 and 12.1

Component : CA Workload Automation AE (AutoSys)

OS: Linux only

Resolution

Engineering will make code changes to the installer in the next release following AutoSys 12.1.

The fix will still prompt for HOST\INSTANCE with a single backslash, but will correctly build and format both the JDBC connect string and the sqlcmd command so the install can proceed.

Use the following workaround to install AutoSys on Linux using a MS-SQL Named Instance:

1) Skip the Database Check

You can export the following variable to instruct the installer to skip the DB checks:

export IA_AE_SKIP_DB_CHECK=1

2) Run the AutoSys 12.0 or 12.1 installation

During the install, enter the database information using a single backslash :  HOST\INSTANCE

This is important, as it will be used to set the EventServer parameter in the $AUTOUSER/config.$AUTOSERV file correctly.


3) Run the CreateAEDB.pl script

After the installation is complete, you will need to create the database schema for AutoSys using the $AUTOSYS/dbobj/MSQ/CreateAEDB.pl script.

A few considerations when running the CreateAEDB.pl perl script:

a) Use the HOST\\INSTANCE format with 2 double backslashes
b) Add the port number if using a non-default port: HOST\\INSTANCE,PORT
c) If your SA user password has special characters, you will need to enter it enclosed in double quotes ... i.e.  "<password>".
Example: If your password is  123),!abc  ... enter it like this in the password field:  "123),!abc"

4) Start the Scheduler and Application Server

Once the CreateAEDB.pl script has successfully created the AutoSys DB, you may start the AutoSys Scheduler and Application Server.


CreateAEDB.pl script example:
----------------------------------------- 
Here is an example where a Named Instance was used with a non-default DB port and a password with special characters:

The sqlcmd works on the command line with this format:
sqlcmd -U sa -P "123),!abc" -S lvntest000348.bpc.broadcom.net\\INSTSQL2016,14330

$ perl CreateAEDB.pl 
CreateAEDB:  Creating/Updating AutoSys Workload Automation Database

CreateAEDB:  Server name [muntest000044.bpc.broadcom.net]? > lvntest000348.bpc.broadcom.net\\INSTSQL2016
CreateAEDB:  Database name [AEDB]? >
CreateAEDB:  User name with system admin privileges [sa]? >
CreateAEDB:  sa user's password [sa]? > "123),!abc"  <====== The password will not actually be visible when you enter it here.
CreateAEDB:  Confirm sa user's password ? > "123),!abc"  <====== The password will not actually be visible when you enter it here. 
CreateAEDB:  Database user name [autosys]? > 
CreateAEDB:  autosys user password [autosys]? > 
CreateAEDB:  Confirm autosys user password ? > 
CreateAEDB:  JRE Directory [/opt/CA/WorkloadAutomationAE/JRE64_WA]? > 
CreateAEDB:  Continue and create the database tables? (y|[n])> y
CreateAEDB:  Updating AutoSys Workload Automation database AEDB on lvntest000348.bpc.broadcom.net\\INSTSQL2016
CreateAEDB:  Creating DB Users.
CreateAEDB:  Creating the database tables.
CreateAEDB:  Creating the DB stored procedures.
CreateAEDB:  Writing 435 rows of initial DB data.
CreateAEDB:  Writing 3724 rows of read-only DB data.
CreateAEDB:  Fixing database data where required.
CreateAEDB:  Reconciling of communication tables where required.

CreateAEDB:  AutoSys Workload Automation database created successfully.

 

Additional Information

This issue is specific to Linux, as sqlcmd.exe on Windows accepts a single backslash.