Advanced Connect

The Advanced Connect feature provides secure user-based authentication mechanisms for your metadata, sources, and target connection in RED. This feature combines session based authentication and complete control over ODBC conection strings.
When a Scheduler uses Advanced Connect it allows you to override the connection strings stored in the metadata as the Scheduler's in-memory Profile Initialization will favor the Profile file attributes over the metadata for stored connection strings and DSN names. This allows for headless connections on the Scheduler while still allowing interactive connections from the RED UI.

Before you begin

Prior to setting up a RED Windows Scheduler using Advanced Connect it must first be enabled in RED, please follow the RED User Guide on Enabling Advanced Connect before continuing with the following configuration.
You need a Profile file that includes any connections and their encrypted credentials (if required). An Advanced Connect Profile can be created from RED and should be created by the same Windows user the Scheduler service runs under so that passwords can be decrypted.

Installing the Scheduler

RED Setup Administrator as Admin and click  Install Scheduler  toolbar item to open the scheduler definition dialog. Make sure to change the  Service logged in under User  to the same Windows User that created the Profile file from RED.
Once you select a SQL Server DSN in the ODBC Connection drop-down menu a new Advanced Connect control becomes visible, check the box, and select the Profile you created earlier.

If you open the Scheduler log file after creating the scheduler you should see a successful connection like this example:

Modifying an existing Scheduler to use Advanced Connect

If you already have a Scheduler and want to convert it to use Advanced Connect then run Setup Administrator as Admin on the machine running the Scheduler service and right click on a Scheduler and select Configure. Selecting a Profile file in this screen (and clicking OK) will transfer it to the correct location and naming convention for the Scheduler. When you next restart your Scheduler it will connect using Advanced Connect and initialize an in-memory Profile containing the connection attributes for each Advanced Connection in the metadata.

Scheduler Profile Initialization

When the Scheduler service starts and there is a matching profile file in the install directory for the Scheduler then the Scheduler will use Advanced Connect to connect to the metadata using the credentials and connection string in the Profile file.
Example of the install directory for a Scheduler using Advanced Connect.

 
After the initial metadata connection is made the Scheduler will then initialize an in-memory Profile by combining the credentials and connection strings in the Profile file with any other connections found in the metadata which were not included in the profile file. The Scheduler service will use the Profile file's credentials and connection strings if provided; otherwise, fallback to the RED metadata.

Updating a running scheduler's Profile

From time to time you need to add a new connection to RED that uses Advanced Connect, or to update a connection's credentials, these changes will not be included in any Scheduler's in-memory Profile unless a refresh is actioned for each scheduler.
To refresh a Scheduler's Profile you must perform the following steps:

1. Update the Profile File on the Scheduler machine
2. Then perform either of the following:
   1.  'Poll for Status and Refresh Profile' from RED UI
   2. Restart the scheduler service

Updating the Profile File
When adding or modifying Advanced Connect connections the actual Profile file need to be recreated from RED by the Windows user that the Scheduler service is running under (select to include encrypted passwords when saving the Profile from RED). Then the file will need to be transferred to the Scheduler install directory to overwrite the existing profile file for the Scheduler. The profile file must have the same file name as the Scheduler .ini file but with the .profile extension.
The file can be transferred manually as described above or you can run Setup Administrator as Admin on the machine running the Scheduler Service and right click on a Scheduler and select Configure. Selecting a Profile file in this screen will transfer it to the correct location and naming convention for the Scheduler overwriting the existing Profile.

Poll for Status and Refresh Profile
Use the 'Poll for Status and Refresh Profile' context option on each Scheduler from the Scheduler tab of RED, after listing the schedulers from the menu item Scheduler>Scheduler Status

Restart the Scheduler Service

When the Scheduler service is restarted this re-runs the in-memory Profile initialization process. On the machine running the scheduler you want to refresh, run WhereScape Setup Administrator as Admin and restart the scheduler. Note this same process can be achieved through Windows tool  Services.msc  by finding and restarting the service.

Tips for Using OAuth or similar authentication methods

For some authentication methods you may need to use a script (or web browser) to login to a data source and generate an access token to use in your connection string. The access token could then be added in the Profile file as the password for a connection. If you have expiring tokens then you will need to create a script to refresh your tokens and restart your scheduler service to pick up the new tokens.
This would involve the following general steps to be implemented in a script:

1. Log in to a server and get a new token (this should be a non-interactive process for the Scheduler).
2. Encrypt the token with Windows DPAPI ensuring the script is running as the same Windows User as the scheduler service.
3. Create a base64 string from the encrypted token
4. Modify the Scheduler Profile file (which is in .JSON format) replacing the password on the affected connection with the base64 string.
5. Restart the Scheduler service OR select 'Poll for Status and Refresh Profile' from RED for the Scheduler.

Example PowerShell script to create an encrypted base-64 string using Windows DPAPI:

Add-Type   -AssemblyName System.Security
$myPass  =   "myp@ssw0rd!"
#Convert the pwd string to a byte array.
$bytes  = [System.Text.Encoding]::Unicode.GetBytes($myPass)
#Encrypt the byte array.
$encryptedBytes  = [System.Security.Cryptography.ProtectedData]::Protect(
$bytes, 
$null, 
#This is the equivalent form stored in the Profile files for RED
$encryptedProfilePassword=[System.Convert]::ToBase64String($encryptedBytes)
Write-Output $encryptedProfilePassword

If for some reason you need to decrypt the profile file passwords in a script the below method shows how to do this. Note that only the same Windows User that encrypted the password in the first place will be able to decrypt it.
Example PowerShell script to decrypt Windows DPAPI encrypted base64 string:

Add-Type -AssemblyName System.Security
# set this to an encrypted string taken from the Profile file
$encryptedProfilePassword=”<YOUR ENCRYPTED STRING>”
# first convert the extracted RED Profile string FromBase64String to Byte array
$encryptedBytes = [System.Convert]::FromBase64String($encryptedProfilePassword)
Write-Host "Encrypted Bytes" -ForegroundColor Cyan
Write-Host ([string] $encryptedBytes) -ForegroundColor DarkGreen
# Unencrypt the data.
$bytes = [System.Security.Cryptography.ProtectedData]::Unprotect(
$encryptedBytes,
$null,
[System.Security.Cryptography.DataProtectionScope]::CurrentUser)
$plainTextPwd = [System.Text.Encoding]::Unicode.GetString($bytes)
Write-Host "Decrypted Data" -ForegroundColor Cyan
Write-Host $plainTextPwd -ForegroundColor Red

Example Profile file
The Profile file is a .JSON file which makes it easy to programmatically update any connection attributes it contains.

{
"connections": [{
"connectionName": "Tutorial (OLTP)",
"connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;database=WslTutorial_DataSeq;",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA",
"userId": "red1"
}, {
"connectionName": "SQL_Target",
"connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;database=sql15_9010_pg;",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA",
"userId": "red1"
}, {
"connectionName": "PostgreSQL_Target",
"connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;database=pg15_9010;",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA",
"userId": "reduser_user"
}, {
"connectionName": "WslTutorial_DataSeq",
"connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA",
"userId": "red1"
}
],
"redConnectionMethod": "Advanced Connect",
"redConnectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;database=sql15_9010_pg;",
"redDatabase": "sql15_9010_pg",
"redDsn": "sql15",
"redDsnArchitecture": "64",
"redServer": "",
"redServerPort": "",
"redUserId": "red1",
"redUserPwd": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA"
}