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"
The settings are saved in Registry "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\TestDSN"
The SecretAccessKey is stored encrypted
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?
I stumbled upon two important clues that lead me to the correct answer below.
The product documentation specifies "Current User Only" or "All Users Of This Machine"... that sounds a lot like Data Protection Encryption (DPAPI)
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