Introduction
Like the RED Client, Azkaban Executor Servers create and use an in-memory Profile at runtime for access to the required runtime credentials for the Metadata, Sources and Target connections. Since an Azkaban job can be run on any Executor on any machine we store any required credentials (passwords encrypted) and connection strings in a central location under the "redadmin"."ws_scheduler_profile" table of the RED metadata repository database.
The Azkaban Executor Servers retrieve credentials and connection strings from the "redadmin"."ws_scheduler_profile" at job runtime and merges any connections missing from the profile records with those found in the RED metadata. This merging process allows for Windows style authentication to work without the need to maintain the Scheduler Profile.
Scheduler Profile Record Structure
Table structure of the "redadmin"."ws_scheduler_profile" table:
| Field | Description | Example |
|---|---|---|
| sp_con_name | Lowercased connection name, used as salt | repository |
| sp_con_string | ODBC Connection String | dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$; |
| sp_user_name | User Name | redscheduler_user |
| sp_encrypted_pwd | Encrypted Password | VTj0Q2xapJEWpQed8DJYvBEEnRedR94NstiHJUlLt0gC |
Encryption
WhereScape provides an encryption utility as a stand-alone tool as well as being imbedded in the applicable Azkaban components. Azkaban decryption of WhereScape encrypted passwords expects the salt used for encryption to be the lowercased connection name. Azkaban has access to the Profile Password, required for decryption, via a new property (com.wherescape.red.profilePassword) in the azkaban.local.properties file of each Azkaban server instance. The password values in both the Azkaban properties files and the Azkaban Users.xml file can also be stored using encryption so that they do not appear in plain text in those files. See Azkaban properties for more details.
See encryption-util.jar section for more details on using the WhereScape encryption utility.
Maintaining the Scheduler Profile
The "redadmin"."ws_scheduler_profile" table should be secured from regular users of the RED client and therefore maintenance of the records stored in this table will normally be done by an administrator user or a user with the specific permissions described below.
Minimum database permissions required:
- SELECT on the ‘red’ schema objects of the RED metadata database.
- SELECT, INSERT, UPDATE, DELETE on the ‘redadmin’ schema objects of the RED metadata database.
To add encrypted profile rows to the scheduler profile RED provides a script with the metadata installation which is designed to be run from within the RED UI by a user with the minimum required permissions mentioned above.
For new installs, from RED 10.2.+, you will find the wsl_scheduler_profile_maintenance host script under your Host Script's in your RED metadata repository. For upgraded repositories you will need to load this script into RED manually from <RED_Installation_Directory>\Administrator\Scripts\wsl_scheduler_profile_maintenance.ps1
Once you execute the script you will be presented with a dialog where you can enter the Profile Password (used to encrypt any passwords entered) and each connection's credentials as well as an appropriate connection string for use by your scheduler service.
Profile Encryption Secret
It is important that you enter and use the same Profile Encryption Secret as you provided in your Azkaban installations otherwise Azkaban will not be able to decrypt these profile records.
Once you have done entering your Scheduler Profile for each connection, click OK.
The script will then take the passwords you have entered and encrypt them using the encryption-util.jar, with lowercased connection names as the encryption salt and the Profile Password as the encryption secret.
Next the script attempts to update the "redadmin"."ws_scheduler_profile" with the Profile details provided and outputs the result and the insert statement to the results pane of RED.
Running the script stand-alone
If you prefer to run the script stand-alone outside of RED then you will need to set the required environment variables prior to running the script, an example follows:
REM set required env vars SET WSL_BINDIR=C:\Program Files\WhereScape\RED SET WSL_META_DSN=Metadata ODBC DSN SET WSL_META_USER=redadmin_user SET WSL_META_PWD=pwd SET WSL_META_CONSTRING=dsn=Metadata ODBC DSN;uid=redadmin_user;pwd=pwd; REM run the script powershell -executionpolicy bypass -f "%WSL_BINDIR%\Administrator\Scripts\wsl_scheduler_profile_maintenance.ps1"