Securely Encrypt and Store Passwords for SQL Job Types in AutoSys

Products

• Autosys Workload Automation
• Workload Automation Agent

Issue/Introduction

You want to set up a SQL job type and encrypt the password or token, so it is not exposed in plain text ․
This process requires securely storing the credentials within the application database using the native security utility ․
AutoSys dynamically pulls the encrypted password at runtime based on the owner attribute.

ERROR MESSAGE: "None"

SYMPTOMS:

• Plain-text passwords exposed in Job Information Language (JIL) definitions

• Need to authenticate against a database securely

CONTEXT: Creating new SQL job definitions requiring secure authentication

IMPACT: Potential security risk if passwords are left in plain text

Environment

 

• Application: AutoSys Workload Automation (AutopSys) 12․X, 24.X

• Agent: AutoSys System Agent 12․X, 24.X

   

   

 

Cause

Client security requirments

Resolution

PREREQUISITES:

• Command line access to the manager server

• EDIT superuser privileges

STEPS:

You must do this as an EDIT superuser.

1. Log in to the AutoSys server (manager) via the command line.

2. Type autosys_secure and press Enter.

3. From the interactive menu, choose [5] Manage users.

4. Next, choose [1] Manage users with password.

5. Choose [1] Create a user.

6. The utility will prompt you for three things:

   • User name: Enter the database user (e.g., db_admin).

   • Host or domain name: Enter the machine name where the agent resides, or the database server.
     If this user will run jobs on multiple agents, you can often use an asterisk (*) or leave it blank depending on your security policy, but best practice is to bind it to the specific agent machine (e.g., db_agent_01).

   • Password: Enter the database password in plain text (it will be encrypted upon saving).

7. Confirm the password and exit the utility by pressing [0] until you are back at the command prompt.

EXPECTED: The encrypted credentials are stored in the database

Step 2: Define the SQL Job in JIL

Now that the password is encrypted and stored in AutoSys, you can create the SQL job. You do not put the password in the JIL file. AutoSys will dynamically pull the encrypted password at runtime based on the owner attribute.

Here is an example of what the JIL looks like:

Plaintext

 

insert_job: my_secure_sql_job
job_type: SQL
machine: db_agent_01
owner: db_admin@db_agent_01
server: my_sql_database_server
sql_command: "EXECUTE my_stored_procedure"
destination_file: "/path/to/output/log/sql_output.log"
description: "Executes a secure SQL query without exposing the password"
alarm_if_fail: y

Step 3: Insert the Job

Once your JIL file (e.g., sql_job.jil) is ready, insert it into the database using the jil command:

Bash

 

jil < sql_job.jil

How the Process Works (Behind the Scenes)

1. When my_secure_sql_job is triggered, the AutoSys scheduler reads the job definition.

2. It sees the owner is db_admin@db_agent_01.

3. The scheduler checks its encrypted database for a password associated with db_admin@db_agent_01.

4. It encrypts the payload (using AES encryption via the cryptkey.txt file configured on your 12.1 environment) and sends the job execution request along with the credentials to the 12.1 SP1 Agent.

5. The Agent decrypts the payload and authenticates against the SQL server, executes the sql_command, and writes the results to the destination_file.

VERIFY SUCCESS:

• The job executes without exposing the password

• The agent decrypts the payload and authenticates against the SQL server