Setup Autosys on Linux with SQL Server database

Products

• Autosys Workload Automation

Issue/Introduction

In order to setup Autosys on Linux with SQL Server database, there are certain modules that need to be installed and configured for this to work. 

Resolution

• Install SQL Server ODBC driver first from this location: SQL Server ODBC Driver

• Install unixODBC driver next

yuminstall unixODBC

• Verify the version and location of odbc.ini / odbcinst.ini  files

odbcinst -j

unixODBC 2.3.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

• Update /etc/odbcinst.ini per requirement, example Encrypt=no  if needed  and note down the path of the Driver 

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1
UsageCount=1
Encrypt = no

• Create a DSN entry (/etc/odbc.ini) to connect to the SQL Server database:

# [DSN name]
[Autosys1211DB]
Driver = ODBC Driver 18 for SQL Server
# Server = [protocol:]server[,port]
Server = tcp:Autosys1211DB,1433
Encrypt = no
#TrustServerCertificate = 1

## add Encrypt=no if needed

• Verify connection to the database using valid credentials:

PATH=/opt/mssql-tools18/bin:$PATH

sqlcmd -U sa -P MyPassword -S Autosys1211DB -No

Note: If  -No  / -C  flags are not used, errors like this may appear:

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.

• If needed adjust $AUTOUSER/autosys.*  scripts or custom scripts to have references to ODBC/sqlcmd

LD_LIBRARY_PATH=/opt/microsoft/msodbcsql18/lib64:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

PATH=/opt/mssql-tools18/bin:$PATH

 

Additional Information

1) Below error indicates the database is not available for connection,  check if sqlcmd works and that the database is functional

[09/14/2023 16:17:04]CAUAJM_I_10655 System is running in single server mode. Event server: SQLServer-Hostname.example.com,1433:Autosys1211DB.
[09/14/2023 16:17:34]CAUAJM_E_18600 Database function call <SQLDriverConnect> failed in <connect SQLDriverConnect Failed> with return code: -1

 

2) Below indicates missing unixODBC RPM,  install/reinstall it and make sure the libodbc.so  file exists

[09/14/2023 16:17:04]CAUAJM_I_10655 System is running in single server mode. Event server: SQLServer-Hostname.example.com,1433:Autosys1211DB.
[09/14/2023 15:31:55]CAUAJM_E_18421 Unable to load ODBC libraries <libodbc.so: cannot open shared object file: No such file or directory>
[09/14/2023 15:31:55]init_mssql failed with return code:-1