Page History
...
For each ODBC, Database or Extensible Source Connection RED maintains an in-memory credential set including the username, password, and connection string for each connection. This in-memory credential set is what we term the ‘Profile’ for authentication during the session of RED.
Additionally the Scheduler Configuration credentials are also stored in-memory for the session and included in the Profile when saving it to disk.
The in-memory profile is session based and therefore the credentials are specific to the user logged on during that session. The connection string itself is however stored in the metadata so that each RED user still uses the same authentication method as other users while in the RED UI.
Saving Profiles to Disk
Profiles can be saved to disk so that users need not enter usernames and passwords into each of their connections whenever they log in to RED.
To save a Profile including session passwords, right click on the Connections node in the objects tree and select 'Save Profile'
...
| Info | ||
|---|---|---|
| ||
| Session passwords are encrypted at rest (on the file in disk) during the save using Windows DPAPI (user-based) encryption. These profile files will therefore only ever be able to be used and decrypted by the Windows user who saved them. |
...
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.
...
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
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, [System.Security.Cryptography.DataProtectionScope]::CurrentUser) # 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.
...
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
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.
...
| Info |
|---|
The following example has had passwords truncated for display purposes. |
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
{
"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"
} |
...