You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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 Unicode 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 Unicode string using Windows DPAPI:

DPAPI Encrypt
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.

Example PowerShell script to decrypt Windows DPAPI encrypted base64 Unicode string:

DPAPI Decrypt
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.

Note
The following example has had passwords truncated for display purposes.

Profile JSON
{
 "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"
}

 

  • No labels