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.
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 |
authConfig | Auth Configuration JSON | {"authExpiresAfterMins":0,"authScriptCommand":""} |
| token_1 | Encrypted Token | SAEfvc2xapJEWpQed8DJYvBEEnRedR94NstiHJUlLt0gC |
| token_2 | Encrypted Token | VKSJDH2xapJEWpQed8DJYvBEEnRedsdflLt0gC |
| token_3 | Encrypted Token | vTj0Q2xasdASfdnihvde98EnRedR94NstiHJUlLt0gC |
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.
The Extensible Authentication fields added in in 10.6.0.0 can only be updated via the maintenance script, described in the next section, these fields will be added to this wizard in the next minor version of RED. |
To launch the wizard from RED UI, go to the Scheduler tab on the ribbon and click on Authentication.
When the wizard opens you have two options:
Current metadata: Use the metadata repository and authentication profile used to log in to RED.
Connect to another: Use a different metadata repository and authentication profile.
Click on Current metadata to use the current connection settings. You will be presented with the ‘authentication profile’ screen. On the Encryption field, enter the Profile Encryption Secret (used to encrypt any passwords entered) and each connection's credentials as well as an appropriate connection string for use by your scheduler service.
Once you entered the credentials for each connection, click Apply. The wizard 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. |
When you choose Connect to another, the Connect to repository screen will open. Provide the required details for connecting to another repository.
Once you connect to a repository you will be presented with the ‘authentication profile’ screen. On the Encryption field, enter the Profile Encryption Secret (used to encrypt any passwords entered) and each connection's credentials as well as an appropriate connection string for use by your scheduler service.
Once you entered the credentials for each connection, click Apply. The wizard 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. |
After updating the Scheduler Authentication Profile click Finish to close the wizard.
You can also launch the wizard outside RED, go to the RED installation folder and run RedSchedulerProfileMaintenance.exe. When launching the wizard outside RED, the Connect to repository screen will open. Provide the required details for connecting to another repository. |
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:
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.
If needed, you can also set additional connection settings like:
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.
| This script will need to be run every time you add a new connection to RED, so that the Scheduler Profile is kept in-sync. The subsequent times you run this script the UI will be prepopulated with any existing records in the "redadmin"."ws_scheduler_profile" so that you do not have to re-enter them but allows updating any records as required. |
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" |
