amazon-web-servicesodbcamazon-redshiftdsn

How to store encrypted SecretAccessKey in registry for Redshift ODBC driver?


Ideally you create a ODBC DSN using the "ODBC Data Sources" UI.

I am creating a Redshift Driver DSN(TestDSN) using Authentication Type "IAM Credentials"

Amazon Redshift ODBC Driver DSN Setup screenshot

The settings are saved in Registry "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\TestDSN"

The SecretAccessKey is stored encrypted (image)

Now I want to create a DSN programmatically, and that can be done by creating Registry keys from my code AWS documentation to Configure driver options

But the only problem is, that for Redshift Driver to use this DSN, it needs the SecretAccessKeyEncrypted.

How do I encrypt my SecretAccessKey to store in Registry so I can create a valid Redshift ODBC DSN?


Solution

  • I stumbled upon two important clues that lead me to the correct answer below.

    1. The product documentation specifies "Current User Only" or "All Users Of This Machine"... that sounds a lot like Data Protection Encryption (DPAPI)

    2. All of the values I tried for SecretAccessKeyEncrypted had similar leading characters in the registry, and began with AQAAANCMnd8BFdERjHoAwE/Cl+ (definitely uses DPAPI).

    I made a Powershell script that has two functions, one for encrypting a new plaintext password into a SecretAccessKeyEncrypted value, and one for decrypting SecretAccessKeyEncrypted into plaintext.

    ### Dependencies 
    Add-Type -AssemblyName System.Security
    Add-Type -AssemblyName System.Core  
    
    
    ### Function Declarations
    Function Decrypt-SecretAccessKey {
    param (
        [string]$EncryptedPassword
    )
    $protectedByteArray   = [System.Convert]::FromBase64String($EncryptedPassword)
    $decryptedByteArray   = [System.Security.Cryptography.ProtectedData]::Unprotect($protectedByteArray, $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser)
    $base64pass           = [System.Convert]::ToBase64String($decryptedByteArray)
    $plaintextPass        = [System.Text.Encoding]::ascii.GetString([System.Convert]::FromBase64String($base64pass))
    return $plaintextPass
    }
    
    Function Encrypt-SecretAccessKey {
    param (
        [string]$PlaintextPassword
    )
    $base64plaintext      = [System.Text.Encoding]::ascii.GetBytes($PlaintextPassword)
    $encryptedByteArray   = [System.Security.Cryptography.ProtectedData]::Protect($base64plaintext, $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser)
    $encryptedPass        = [System.Convert]::ToBase64String($encryptedByteArray)
    return $encryptedPass
    }
    
    
    ### Example calls
    
    $testPassword="anythingYouWant"
    
    $NewEncryptedPassword  = Encrypt-SecretAccessKey -PlaintextPassword $testPassword
    $NewEncryptedPassword
    $NewDecryptedPassword = Decrypt-SecretAccessKey -EncryptedPassword  $NewEncryptedPassword
    $NewDecryptedPassword