Handling SQL Server Connection strings with PowerShell Secret Management

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

## - Microsoft.PowerShell.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:

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:

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:

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);

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!

PowerShell Working with SQL Logins Name

This blog post is following “Changing a SQL Server Login name with T-SQL“. Let’s take advantage of the .NET SMO framework assembly object model Namespaces with PowerShell to change a Windows account in a SQL Server Logins.

Remember to download the latest version of PowerShell.

There’s nothing wrong in using SSMS (SQL Server Management Studio) as our GUI application to manage our SQL Server engine. But soon, you will have the need to use PowerShell scripting for automating some daily tasks. And, Trust me! It will save you time.

Both Microsoft and the SQL Server Community provide you with some of the Awesome tools, such as the following PowerShell modules: SQLPS, SqlServer, Secretmanagement, and DBATools.

Let’s begin with creating  a list all SQL users on our SQL Server using the DBATools module  “Get-DBAuser” command:

Get-DBADBUser -SqlInstance 'localhost,1433'

As you can see, this command returns a lot of information you can export and dissect in many way.

Now, let’s take this a little further using SMO Object Model Namespaces.

Don’t be scare! in order to start using these SMO Classes. To start, all you need to have installed any of the following PowerShell Modules: SQLPS, SQLServer or DBATools, then execute the “import-Module” command:

## This will load SMO assemblies:
Import-Module SqlServer

Then all necessary SMO Assemblies are loaded and ready to be consumed during your PowerShell session. You can start building your own PowerShell one-liners or scripts/functions command to interact with the SQL Server engine.

Let’s cut to chase, and create a simple PowerShell function “Get-SqlLogins‘ to simply list all my SQL logins in my SQL Server:

## - function_Get-SqlLogins.ps1:

function Get-Sqllogins
[parameter(Mandatory = $true)]

## - Prepare connection to SQL Server:
$SQLSrvConn = `
new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($sqlname, $uname, $upwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - Get SQL SERVER list of database names:
$global:itm = 0
$SQLSrvObj.logins | Select-Object @{ l = 'itm'; e = { $global:itm; ++$global:itm }; }, name, logintype;

$sqlname = 'localhost,1433';
$uname = 'sa';
$upwd = '$SqlPwd01!';

Get-Sqllogins -sqlname $sqlname -uname $uname -upwd $upwd

## - End-of-File

Note: Save this code as function_Get-Sqllogins.ps1. 

You can edit this file to run one liner at the time and explore the $SQLSrObj PowerShell object.

Use the following GET-Member(alias gm)command to explore the object content:

## - exploring .NET Objects:
$SQLSrvObj | gm | Out-GridView

This is a good way to learn about your PowerShell objects.  You’ll be surprised by the ton of information you can find for documentation.

Now, try listing all SQL logins names by typing the following:

## - shorthand  to list all values in a object proprety:

So, with a few lines of code, you can quickly get results.

Now, proceeding with looking for the Windows account I want to change the name in the SQL Login.

For this, I need to add line numbers to the PSObject result. This way I can Isolate the Login ID:

$global:cnt = 0
$SQLSrvObj.logins | Select-Object @{ l = ‘cnt’; e = { $global:cnt; ++$global:cnt }; }, name, logintype

For the finale: Changing the SQL Login Name. I’m going to manually do this using SMO PowerShell One-liner:
I found that element #5 is the SQL login I need to change:

## - verify before making the changeto the SQL Login object;

So far we’ve been working with SMO .NET Objects properties. Here’s where we use SMO .NET methods which affect the object (element#5) I have manually selected using “$SQLSrvObj.logins[5]“:

Last steps for updating the SQL Login name:

Note: Keeping in mind, the actual change starts at the Windows Account level. 

1. The *.Alter() method sets the object ready to be changed:


2. followup by the *.rename(**string**) method which will affect the name object.


3. And, finally we use the *.refresh() to update all logins list with the name change.


AS you can see,  this open to some automation opportunities that can involve Windows Domain with SQL Server Accounts administration.

Don’t forget! always test your scripts. Practice makes a good scripter, and never be afraid of trying new stuff.

SQL PowerShell! It is the way!

Changing a SQL Server Login name with T-SQL

It’s been some time since my last blog post! Changing a SQL Server Login name uising  T-SQL.

Everyone who has tried at first, knows that it has its caveats. I couldn’t find a straight answer to the errors I was getting. You would think changing the sql login name should be simple. But, I was using the wrong approach.

First, we are working with “WindowsUser” login type. In SQLServer, I’m trying to fix a Windows User (local account)name  from “dev01” to “devUser01” using the following T-SQL statement:

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]

but, in  doing so I got the following error message:

“Windows NT user or group ‘MXTLPT01\DevUser01’ not found. Check the name again.”

Of course, “MXTLPT01\DevUser01” Windows user account doesn’t exist on my system.

So, I created the user I try to run the T_SQL statement:

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\Devtest01]

Nope, it didn’t not work ending with the following message:

Msg 15098, Level 16, State 1, Line 31
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.

Basically, It translate to your Windows Account SID Object in SQL Server doesn’t match the object you’re trying to change.

In other word, you need to work with the Windows User originally created in WINDOWS (Locally or Domain), has changed first before executing the T-SQL statement “Alter Login… With Name=…”will be successful:

I ran the statement after the the change took place in my Windows system;

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]

This time it works.

Wait! What about using PowerShell?

Check out the next blog post “PowerShell Working with SQL Logins Name

Happy SQLAdmin!