Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.
Where do I start?
To install the Powershell Secret Management module, execute the following series of one-liners in a PowerShell prompt to install the latest version from the PowerShell Gallery:
Note: This module has finally reached GA (Generally Available) status.
## - install from the PowerShell Gallery both: SecretManagement, and SecretStore modules:
Install-Module Microsoft.PowerShell.SecretManagement, Microsoft.PowerShell.SecretStore
## - Register the vault with a given name:
Register-SecretVault -Name SecretStore -ModuleName Microsoft.PowerShell.SecretStore -DefaultVault
Now, we got the default “SecretStore” vault created. The vault password will ask once you start adding secrets.
The following cmdlets make it easy to manage your vault:
## - Module: Microsoft.PowerShell.SecretManagement
Get-Secret
Get-SecretInfo
Get-SecretVault
Register-SecretVault
Remove-Secret
Set-Secret
Test-SecretVault
Unregister-SecretVault
## - Microsoft.PowerShell.SecretStore
Get-SecretStoreConfiguration
Reset-SecretStore
Set-SecretStoreConfiguration
Set-SecretStorePassword
Unlock-SecretStore
Note: By-Design. There can only be one vault available.
Take your time to learn these commands.
Let the fun begin
Let’s cut down to the chase and see how this works. I’m going to proceed to create my secret SQL Server connection string values.
Keep in mind, secrets management supports five types of objects: byte[], String, SecureString, PSCredential, and Hashtable. By DEFAULT, the secret will be stored as a ‘SecureString‘ object.
Be creative! Why not store my connection string(s) as a hash table object containing my credential information in the following way:
## - Create hashtable object containing the SQL Connection String:
[hashtable]$MysqlCred01 = @{SqlName = "localhost,1445";Sqlusr = "sa"; SqlPwd = '$MyPwd01!';};
## - This is to veryfy the hashtable object was Properly created:
$MysqlCred01.GetType();
$MysqlCred01
Next after creating the hashtable object, is to save it in the vault with the following command “Set-Secret“:
## - Storing the secret in the vault:
Set-Secret -name MysqlCred01 -secret $MysqlCred01
Note: the first time you store a secret value to the vault, you’ll be prompted for a password.
As you save more secrets, use the following command “Get-SecretInfo” to list what you have in the vault:
## Displaying all stored secrets:
Get-SecretInfo
Now, to get your secret from the vault and use it in PowerShell:
## - Pulling the secret out of the vault into PowerShell variable as plain text:
$MysqlhashCred01 = Get-secret -name MysqlCred01 -asplaintext
## - Accessing hash table values:
$MysqlhashCred01.SqlName
$MysqlhashCred01.Sqlusr
$MysqlhashCred01.SqlPwd
You will notice that eventually, your access will time-out locking you out of the vault. Here’s you use the following command “Unlock-SecretStore” to temporarily unlock the vault:
## - Unlocking the vault to access your secrets providing the vault password:
Unlock-SecretStore -Password '$yourpwd!'
Now, the “Unlock-SecretStore” command is useful for script automation. when you want the script to quickly access the vault. You’ll need to do the following:
## - Unlocking the vault for automation:
Unlock-SecretStore -Password $(ConvertTo-SecureString -String '$yourpwd!' -AsPlainText);
Get-SecretInfo
This way SecretStore vault will not prompt for a password.
Implementing Secret in a GUI Application
Here’s an example of implementing secret in one of my SAPIEN PowerShell Studio GUI applications that check for SQL Server Database Index Fragmentation.
This is a multi-form Window application that where you can select a connection string stored in your SecretStore vault. then you can select the Database and click on the “Start-Job” button to list the status of Database index fragmentation. In this sample application, I can connect to both my local SQL Server and 2 of my Azure SQL Databases.
If you work with PowerShell, both SAPIEN’s Primalscript and PowerShell Studio is a good tool to have for any Administrators and DevOps. Try them out!
For more information
1. Secret Management Blog post.
2. Secret Management in Github. (Post any bugs and/or feedback here)
3. SecretStore in Github. (Post any bugs and/or feedback here)
Have a GREAT SQL PowerShell Day! This is the way!
