Page History

Versions Compared

Key

This line was added.
This line was removed.
Formatting was changed.

...

Tip

title	Tip

The users' AppData location can be found by typing %APPDATA% into the address bar of a Windows browser and pressing enter.

Using OAuth or similar authentication methods

For some authentication methods you may need to include additional authentication tokens in your connection strings. If the token is static or long lived it can be added directly into one of the Auth Token fields in your connection and referenced in your connection string.

Updating the Scheduler Profile

Similar to saving a RED Profile to disk to persist between sessions for a User, the RED Scheduler also requires persistent storage of the Profile which can be shared securely across Jobs. The RED Scheduler Profile values are stored in a table using secure encryption methods. See the Scheduler Profile Maintenance section for more details.

Using OAuth or similar authentication methods

For short lived or expiring tokens a For short lived or expiring tokens a Scripted Authentication approach may be required. This is achieved by setting an Auth Script Command on your connection which could execute an authentication workflow script to retrieve access tokens or other authentication values and update the RED Profile file making them available to the RED UI and Scripts. If you use expiring tokens with the scheduler, you would need to create and schedule a script to refresh your tokens and update your scheduler profile records, see the Scheduler Configuration section for more details.

...

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 RED Profile file or Scheduler Profile table as the password or one of the Auth Token fields for a connection. If you use expiring tokens with a scheduler then you will need to create a script to refresh your tokens and update your scheduler profile records, see the Scheduler Configuration section for more details.

...

Code Block

language	powershell
title	DPAPI Decrypt
linenumbers	true
collapse	true

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.

 -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

language	json
title	Profile JSON
collapse	true

{
    "redServerPort": "5432",
    "redUserId": "postgres",
    "authTokens": {
        "token_2": {
            "value": "",
            "displayName": ""
        },
        "token_3": {
            "value": "AQAAANCMnd8BFdERjHoAwE",
            "displayName": "dsn"
        },
        "token_1": {
            "value": "AQAAANCMnd8BFdERjHoAwE",
            "displayName": "user"
        }
    },
    "redDsnArchitecture": "64",
    "authConfig": {
        "authExpiresAfterMins": 20,
        "authScriptCommand": "powershell -executionpolicy bypass -f \"C:\\temp\\RED\\ws_auth\\ws_auth.ps1\""
    },
    "redUserPwd": "AQAAANCMnd8BFdERjHoAwE",
    "redDsn": "repo",
    "redConnectionString": "dsn=$AUTHTKN_3$;uid=$AUTHTKN_1$;pwd=$PASSWORD$;",
    "schedulerDashboardUser": "wsdashboarduser",
    "redServer": "localhost",
    "schedulerApiUser": "wsapiuser",
    "schedulerDashboardPwd": "AQAAANCMnd8BFdERjHoAwE",
    "connections": [{
            "connectionName": "Database Source System",
            "connectionString": "dsn=$DSN$;",
            "password": "AQAAANCMnd8BFdERjHoAwE",
            "userId": "",
            "dsn": "",
            "authConfig": {
                "authExpiresAfterMins": 0,
                "authScriptCommand": ""
            },
            "authTokens": {}
        }, {
            "connectionName": "Snowfalke MFA",
            "connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$$AUTHTKN_1$;passcodeInPassword=on;",
            "password": "AQAAANCMnd8BFdERjHoAwE",
            "userId": "snowfalke_user",
            "dsn": "",
            "authConfig": {
                "authExpiresAfterMins": 240,
                "authScriptCommand": "powershell -executionpolicy bypass -f \"C:\\temp\\RED\\ws_auth\\ws_auth.ps1\""
            },
            "authTokens": {
                "token_2": {
                    "value": "",
                    "displayName": ""
                },
                "token_3": {
                    "value": "",
                    "displayName": ""
                },
                "token_1": {
                    "value": "AQAAANCMnd8BFdERjHoAwE",
                    "displayName": "MFA TOTP"
                }
            }
        }, {
            "connectionName": "WslTutorial_DataSeq",
            "connectionString": "dsn=$AUTHTKN_3$;uid=$AUTHTKN_1$;pwd=$AUTHTKN_2$;",
            "password": "AQAAANCMnd8BFdERjHoAwE",
            "userId": "",
            "dsn": "",
            "authConfig": {
                "authExpiresAfterMins": 0,
                "authScriptCommand": ""
            },
            "authTokens": {}
        }, {
            "connectionName": "Data Warehouse SQL",
            "connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;",
            "password": "AQAAANCMnd8BFdERjHoAwE",
            "userId": "red1",
            "dsn": "",
            "authConfig": {
                "authExpiresAfterMins": 0,
                "authScriptCommand": ""
            },
            "authTokens": {
                "token_2":

Code Block

language	json
title	Profile JSON
collapse	true

{
  "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;",
      "passwordvalue": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA",
      "userId": "reduser_user"
    },
     {
      "connectionNamedisplayName": "WslTutorial_DataSeq",

               "connectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;",
 },
         "password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAA",
      "userIdtoken_3": "red1"{
    }
  ],
  "redConnectionMethod": "Advanced Connect",
  "redConnectionString": "dsn=$DSN$;uid=$USER$;pwd=$PASSWORD$;database=sql15_9010_pg;",
  "redDatabase": "sql15_9010_pg",
  "redDsn": "sql15",
  "redServervalue": "",
                    "redServerPortdisplayName": "",
   "redUserId": "red1",
  "authConfig": {
    "authScriptCommand": "powershell -executionpolicy bypass -f authscript.ps1" },
          "authExpiresAfterMins": 60,
      "authUrltoken_1": "https://test.com",
{
     "customProperty": "",
    "accessTokenExpiryTime": "2025-07-03T10:51:00",
    "refreshTokenExpiryTime": "2025-07-03T10:51:00"
  },
  "redDsnArchitecturevalue": "64",
  "authTokens": {
    "token_1": {
      "value": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAaXbH2rKfq0qz2x8mcZDgoAAAAAACAAAAAAAQZg",
      "displayName": "Access Token"
    },
    "token_2": {
      "value": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAaXbH2rKfq0qz2x8mcZDgoAAAAAACAAAAAAAQZg", }
      "displayName": "Refresh Token"
    },
       "token_3": {
   }
    ],
    "valueredConnectionMethod": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAaXbH2rKfq0qz2x8mcZDgoAAAAAACAAAAAAAQZgAdvanced Connect",
      "displayNameschedulerApiPwd": "JWT"
    }
  }, 
AQAAANCMnd8BFdERjHoAwE",
    "redUserPwdredDatabase": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAK9Z1yRvrzEOIvwCfKZ96UAAAAAACAAAAAAAQZgAArepo"
}

Using Profiles

The following Environment Variables are created at run-time for Scripts associated to ODBC, Database and Extensible Source Connections:
Where User, Password and Connection Strings are set from the current RED session credentials in the in-memory Profile.
WSL_<META|TGT|SRC>_CONSTRING contains the complete connection string with tokens $DSN$, $USER$, $PASSWORD$ and $AUTHTKN_<1|2|3>$ replaced.

Metadata	Target	Source
WSL_META_DSN	WSL_TGT_DSN	WSL_SRC_DSN
WSL_META_DSN_ARCH	WSL_TGT_DSN_ARCH	WSL_SRC_DSN_ARCH
WSL_META_SERVER	WSL_TGT_SERVER	WSL_SRC_SERVER
WSL_META_DBID	WSL_TGT_DBID	WSL_SRC_DBID
WSL_META_USER	WSL_TGT_USER	WSL_SRC_USER
WSL_META_PWD	WSL_TGT_PWD	WSL_SRC_PWD
WSL_META_CONSTRING	WSL_TGT_CONSTRING	WSL_SRC_CONSTRING
WSL_META_AUTHCFG	WSL_SRC_AUTHCFG	WSL_SRC_AUTHCFG
WSL_META_AUTHTKN_[1\|2\|3]	WSL_TGT_AUTHTKN_[1\|2\|3]	WSL_SRC_AUTHTKN_[1\|2\|3]

...

Content

Space Tools

Versions Compared

Old Version 6

New Version Current

Key

Using OAuth or similar authentication methods

Updating the Scheduler Profile

Using OAuth or similar authentication methods

Example Profile file

Example Profile file

Using Profiles