How To Handle Passwords in PowerShell Automation

Ajay Dwivedi | December 17, 2023 | Powershell, SQLMonitor, TSQL | 1 Comment

Spread the love

I have been writing automations using PowerShell for many years now. One common issue I notice with people’s code is the improper handling of passwords. In this blog, I share how to set up a Credential Manager on a SQLServer and use the same for handling passwords in automation.

To setup Credential Manager, we need to download and execute the following steps using scripts from my Github repo SQLMonitor.

1. Create tables

Credential-Manager/SCH-[dbo].[credential_manager].sql

This SQL script creates 2 required tables – dbo.credential_manager & dbo.dbo.credential_manager_audit.

2. Create procedures

The above 4 procedures are used to add, remove, update & fetch credentials with Credential Manager.

3. Adjust appropriate permissions

Credential-Manager/SCH-Permissions.sql

This script creates a certificate-based login/user and signs all the procedures created above. Then execute permission is provided on the above procedures so anyone having access to the Credential Manager server can utilize the Credential Manager through the procedures.

Below are some sample ways to use the above procedures-

use DBA
go

IF DB_NAME() = 'master'
	raiserror ('Kindly execute all queries in [DBA] database', 20, -1) with log;
go

-- Check all Logins
select server_ip, server_name, [user_name], is_sql_user, is_rdp_user, password_hash, salt, 
created_date, created_by, updated_date, updated_by, delegate_login_01, delegate_login_02, remarks 
from dbo.credential_manager
go


/* Insert Credentials */
exec dbo.usp_add_credential
			@server_ip = '*',
			--@server_name = '<server_name>',
			@user_name = 'sa',
			@password_string = 'SomeStringPassword',
			--@passphrase_string = '421',
			--@is_sql_user = 1,
			--@is_rdp_user = 1,
			--@save_passphrase = 1,
			@remarks = 'sa Credential';
go

/* Fetch Credentials */
declare @password varchar(256);
exec dbo.usp_get_credential 
		--@server_ip = '*',
		--@user_name = 'Lab\SQLServices',
		@password = @password output;
select @password as [@password];
go


/* Remove Credential */
exec dbo.usp_delete_credential
	@server_ip = '*',
	@user_name = 'Test',
	@password = 'SomeStringPassword'
go


/* Update Credential */
exec dbo.usp_update_credential
	@server_ip = '*',
	@user_name = 'Test',
	--@password = 'SomeStringPassword',
	@confirm_forgot_password = 1,
	@remarks = 'Testing update'
go


/* Get All Credential for Specific Server */
declare @server_ip char(15) = '*'
select server_ip, server_name, [user_name], is_sql_user, is_rdp_user, 
		password_hash, [password] = cast(DecryptByPassPhrase(cast(salt as varchar),password_hash ,1, isnull(@server_ip,server_ip)) as varchar),
		salt, salt_raw = cast(salt as varchar),	created_date, created_by, updated_date, updated_by, 
		delegate_login_01, delegate_login_02, remarks 
from dbo.credential_manager
where @server_ip is null or server_ip = @server_ip
go


/* Get All Credentials */
select server_ip, server_name, [user_name], is_sql_user, is_rdp_user, 
		password_hash, [password] = cast(DecryptByPassPhrase(cast(salt as varchar),password_hash ,1, server_ip) as varchar),
		salt, salt_raw = cast(salt as varchar),	created_date, created_by, updated_date, updated_by, 
		delegate_login_01, delegate_login_02, remarks 
from dbo.credential_manager cm
where cm.server_ip = 'SomeServer'
go

use DBA

IF DB_NAME() = 'master'

raiserror ('Kindly execute all queries in [DBA] database', 20, -1) with log;

-- Check all Logins

select server_ip, server_name, [user_name], is_sql_user, is_rdp_user, password_hash, salt,

created_date, created_by, updated_date, updated_by, delegate_login_01, delegate_login_02, remarks

from dbo.credential_manager

/* Insert Credentials */

exec dbo.usp_add_credential

@server_ip = '*',

--@server_name = '<server_name>',

@user_name = 'sa',

@password_string = 'SomeStringPassword',

--@passphrase_string = '421',

--@is_sql_user = 1,

--@is_rdp_user = 1,

--@save_passphrase = 1,

@remarks = 'sa Credential';

/* Fetch Credentials */

declare @password varchar(256);

exec dbo.usp_get_credential

--@server_ip = '*',

--@user_name = 'Lab\SQLServices',

@password = @password output;

select @password as [@password];

/* Remove Credential */

exec dbo.usp_delete_credential

@server_ip = '*',

@user_name = 'Test',

@password = 'SomeStringPassword'

/* Update Credential */

exec dbo.usp_update_credential

@server_ip = '*',

@user_name = 'Test',

--@password = 'SomeStringPassword',

@confirm_forgot_password = 1,

@remarks = 'Testing update'

/* Get All Credential for Specific Server */

declare @server_ip char(15) = '*'

select server_ip, server_name, [user_name], is_sql_user, is_rdp_user,

password_hash, [password] = cast(DecryptByPassPhrase(cast(salt as varchar),password_hash ,1, isnull(@server_ip,server_ip)) as varchar),

salt, salt_raw = cast(salt as varchar), created_date, created_by, updated_date, updated_by,

delegate_login_01, delegate_login_02, remarks

from dbo.credential_manager

where @server_ip is null or server_ip = @server_ip

/* Get All Credentials */

select server_ip, server_name, [user_name], is_sql_user, is_rdp_user,

password_hash, [password] = cast(DecryptByPassPhrase(cast(salt as varchar),password_hash ,1, server_ip) as varchar),

salt, salt_raw = cast(salt as varchar), created_date, created_by, updated_date, updated_by,

delegate_login_01, delegate_login_02, remarks

from dbo.credential_manager cm

where cm.server_ip = 'SomeServer'

I would recommend watching the below video which explains Credential Manager to a great deal.

I hope this will help anyone writing automation for SQLServer using PowerShell or needs a Password Vault.

Tags:Credential Manager, Password Vault, PowerShell Password Handling, SQL Server, sql-server, tsql

About The Author

Ajay Dwivedi

I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.

One Comment

SQLServer Performance Optimization and Automation

How To Handle Passwords in PowerShell Automation

1. Create tables

2. Create procedures

3. Adjust appropriate permissions

Like this:

Related

About The Author

Ajay Dwivedi

Leave a ReplyCancel reply

1. Create tables

2. Create procedures

3. Adjust appropriate permissions

Share this:

Like this:

Related

Related Posts

About The Author

Ajay Dwivedi

Leave a ReplyCancel reply