SQL Server Login Password Rotation: Fully Automated

George Lin
Oct 30, 2021
5 min read

Updated: Nov 16, 2021

Solution Description:

The passwords are created randomly using PowerShell API and are always kept in a secure Azure key vault as a secret
The Azure KV is well configured to only allow access from the specific VM in the specific Vnet/Subnet.
The password rotation process first retrieve the login name and password from the Azure key vault, then use this credential to connect to hundreds of SQL instances to change its own password
The list of target SQL instances is maintained in a database table which is accessible to the password rotation process
Once the password has been reset on all the target SQL instances, this new password is uploaded to KV to update the password secret. This generates a new version the secret value
The password rotation process insert a row to a database table to track the password change log including a flag column indicating if the password change succeeded or failed on the SQL Instance.
The password rotation process is conducted by a PowerShell script and triggered by a SQL Agent job. The PowerShell script also calls a stored procedure to check the log table and send out email alerts if it finds any row(s) with flag column indicating password change failure. The email alert contains a list of SQL instances that failed password rotation along with the error messages

Step 1. Create a new database or use an existing one to create password rotation schema

On a SQLVM which can reach to all target SQL instances and also has access to the key vault password secret, run this script below to create required tables and view:

USE [NewORExistingDatabase]
GO
CREATE SCHEMA [PWD]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Create the table to hold a list of all SQL Instances that may require login password rotation
CREATE TABLE [PWD].[ALL_SQLInstances](
	[servername] [varchar](256) NOT NULL,
	[ipaddress] [varchar](256) NULL,
	[sqlport] [varchar](16) NULL,
 CONSTRAINT [PK_ALL_SQLInstances] PRIMARY KEY CLUSTERED 
(
	[servername] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Create the table to track password change state on each and every SQL Instances
CREATE TABLE [PWD].[ChangePWDLog](
	[ServerName] [varchar](1000) NULL,
	[LoginName] [varchar](100) NULL,
	[PWDChangeState] [bit] NULL,
	[PWDChangeTime] [datetime2](7) NULL,
	[ErrorMSG] [varchar](6000) NULL
) ON [PRIMARY]
GO

-- Create the view to select SQL Instances for password reset
CREATE view [PWD].[PWDRotationServerList] as select * from [PWD].[ALL_SQLInstances]
GO

Run script below to create the stored procedure that check password rotation state and send email alerts if failure detected.

USE [NewORExistingDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE   PROCEDURE [PWD].[Check_PWD_Rotation_State]
	@StartTime datetime2,
   @email_list varchar(100) = 'saasopsdatabaseadmins@pointclickcare.com;'

AS
BEGIN
set nocount on;

declare @realsubject varchar(200)
declare @attached_filename varchar(200)
declare @body_text varchar(8000)
declare @warning_query varchar(2000)

declare @num_of_succeeded_pwd_rotation int
declare @num_of_failed_pwd_rotation int

select @num_of_succeeded_pwd_rotation = count(1) from pwd.ChangePWDLog where PWDChangeState=1 and PWDChangeTime > @StartTime
select @num_of_failed_pwd_rotation = count(1) from pwd.ChangePWDLog where PWDChangeState=0 and PWDChangeTime > @StartTime

set @body_text=''

if @num_of_failed_pwd_rotation > 0
	begin
		set @warning_query='set nocount on;
select 
cast(ServerName as varchar(64)) as ServerName,
cast(LoginName as varchar(20)) as SQLLoginName,
PWDChangeTime,
cast(ErrorMSG as varchar(120)) as ErrorMSG from pwd.ChangePWDLog where PWDChangeState=0 and PWDChangeTime > ''' + cast(@StartTime as varchar(60))+''''

		set @body_text='Monitoring SQL login password rotation state, Succeeded: '+ cast(@num_of_succeeded_pwd_rotation as varchar(10)) + ', failed: '+ cast(@num_of_failed_pwd_rotation as varchar(10)) +'. see attached file for the details. You can also query dbo.ChangePWDLog table in dba_autoupload database to see full details of password rotation history.'

		select @realsubject= 'Failed SQL login password rotation reports'
		set @attached_filename='Servers_Failed_PWD_Rotation_'+convert(varchar(16),getdate(),110)+'.txt'
		exec msdb.dbo.sp_send_dbmail 
		@recipients=@email_list,			
		@subject= @realsubject,
		@body=@body_text,
		@query=@warning_query,
		@execute_query_database='master',
		@attach_query_result_as_file=1,
		@query_attachment_filename=@attached_filename,
		@query_result_header=1
--		@query_no_truncate = 0
end
END

GO

Step 2. Create two secrets in the Azure key vault

Secret1 Name: "TargetSQLLogin"
Secret1 Value: this should be the real target SQL Login name
Secret2 Name: "TargetSQLLoginPWD"
Secret2 Value: this should be the real target SQL Login password

Step 3. Deploy the PowerShell script

On the same SQLVM, create the following two folders in C: drive:

C:\SQLAdmin
C:\SQLAdmin\Log

Use the code blow to create a new PowerShell script named "autorotatepwdv2.ps1" in folder c:\SQLAdmin

#variables to run this powershell file
param(
    [string]$PWDRotationDB = "NewOrExistingDatabase", 
    [string]$PWDRotationSQLInstance = "SQLInstanceHavingKVAccess",
    [string]$PWDRotationKV = "YourWellSecuredKeyVault",
    [string]$kv_sqllogin_secret = "TargetSQLLogin",
    [string]$kv_sqlloginpwd_secret = "TargetSQLLoginPWD"
)

$curfolder = get-location
"Importing SQLPS Module... please wait...."
import-module sqlps -disablenamechecking
#move back to location on disk driver.. versus staying in SQLSEERVER drive..
cd $curfolder

#invoke the web request on the tenant to get the token for the local host in the specific port for the VM. 
$Response = Invoke-RestMethod -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fvault.azure.net' -Method GET -Headers @{Metadata="true"}

#extract the access token from the response
$KeyVaultToken = $Response.access_token

# to retrieve the secret value
$KVLoginURL = "https://" + $PWDRotationKV + ".vault.azure.net/secrets/" + $kv_sqllogin_secret + "?api-version=2016-10-01"
$KVLoginPWDURL = "https://" + $PWDRotationKV + ".vault.azure.net/secrets/" + $kv_sqlloginpwd_secret + "?api-version=2016-10-01"

$sqluser = (Invoke-RestMethod -Uri $KVLoginURL -Method GET -Headers @{Authorization="Bearer $KeyVaultToken"}).value
$sqlpassword = (Invoke-RestMethod -Uri $KVLoginPWDURL -Method GET -Headers @{Authorization="Bearer $KeyVaultToken"}).value

$query = "select servername from [pwd].[PWDRotationServerList]"
$SQLServers = invoke-sqlcmd -query $query -database $PWDRotationDB -serverinstance $PWDRotationSQLInstance -ErrorAction Stop #-querytimeout 65535
##$SQLServers.Length

# Import System.Web assembly
Add-Type -AssemblyName System.Web
# Generate random password
$NewPWD = [System.Web.Security.Membership]::GeneratePassword(12,2)

$StartTime = Get-Date -Format o

Foreach($SQLServer in $SQLServers.servername){
            $SQL01 = "Begin try 
ALTER LOGIN [$sqluser] WITH PASSWORD=N'$NewPWD'; 
select 1
End try
Begin Catch 
select 0
End Catch "
            $TimeStamp = Get-Date -Format o
            try {
                $Result = $NULL
                $Result = invoke-sqlcmd -query $SQL01 -database master -serverinstance $SQLServer -Username $sqluser -Password $sqlpassword -ErrorAction SilentlyContinue  -ErrorVariable InvokeSQLError
            }
            catch {
                $flag = 0
            }
            $ErrorMSG =$InvokeSQLError -replace "'","''"
            
            If ($NULL -ne $Result) {

                if($Result.Column1 -eq 1) {
                    $flag = 1
                } 
                else {
                    $flag = 0
                    $ErrorMSG = "Failed to run the SQL Statement" + ($SQL01 -replace "'","''") +" on the server"
                }
            } else {
                if ($ErrorMSG.Length -ne 0) { $flag = 0}
            }     

            $SQL02 = "Insert into PWD.[ChangePWDLog] (ServerName,LoginName,PWDChangeState,PWDChangeTime,ErrorMSG) Values ('$SQLServer','$sqluser',$flag,'$TimeStamp','$ErrorMSG')"
            invoke-sqlcmd -query $SQL02 -database $PWDRotationDB -serverinstance $PWDRotationSQLInstance
}

# To update seceret value:
$bodyObject = @{"value" = $NewPWD}
#$bodyObject = @{"value" = "4f968c626b33"}
$bodyJson = ConvertTo-Json -InputObject $bodyObject
#
Invoke-RestMethod -Uri $KVLoginPWDURL -Method Put -Headers @{Authorization="Bearer $KeyVaultToken"} -Body $bodyJson -ContentType "application/json"

$SQL03 = "exec [PWD].[Check_PWD_Rotation_State] '$StartTime'"
invoke-sqlcmd -query $SQL03 -database $PWDRotationDB -serverinstance $PWDRotationSQLInstance

Step 4, Create SQL Agent job to run password rotation PowerShell automatically

On the same SQL instance, run code below to create the SQL Agent job.

USE [msdb]
GO
 
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
 
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_SQLServer-Login-pwd-rotation', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run-PS-Script', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'powershell.exe "c:\SQLAdmin\autorotatepwdv2.ps1"', 
		@database_name=N'master', 
		@output_file_name=N'C:\sqladmin\Log\DBA_Login_pwd_rotation_runtime_output.log', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly', 
		@enabled=0, 
		@freq_type=16, 
		@freq_interval=27, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20211027, 
		@active_end_date=99991231, 
		@active_start_time=91000, 
		@active_end_time=235959, 
		@schedule_uid=N'dd379422-1c04-470e-81ea-cbf7b3338c23'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

SQL Server Login Password Rotation: Fully Automated

Recent Posts

Comments