Introduction

In this article, I will help you how to set secure SA passwords for bulk servers. Sometimes we have many SQL server instances in the company environment, with the count being in the hundreds. For security requirements, you may need to ensure the SA password is different for all of them, while making strong passwords. There will be too many tasks if you do it manually,  so the best practice is to bulk set the SA passwords with a script and store them securely.

My solution is to use PowerShell to generate strong random passwords with the server list and save them into a CSV file, then use the "Invoke-Sqlcmd" command to bulk reset the SA password via retrieving the value from the CSV file. The script also will verify whether the SA password changing successfully by comparing the hash value of the passwords after the change.

Please follow the steps:

1. 1. Use PowerShell to generate the list stored safe random password
   2. Bulk reset the SA passwords
   3. Verify the SA passwords changed

Use PowerShell to generate the list stored safe random password

First, you should create a server list that includes all SQL Servers that need to be updated. In this script, "C:\Users\***\Desktop\Serverlist.txt" is the server list. Here is the content of the sample server list (C:\Users\***\Desktop\Serverlist.txt):

Then, you can run the following PowerShell script on your server or PC which can connect to all SQL instances in the server list:

$servers = Get-Content "C:\Users\***\Desktop\Serverlist.txt"
Function New-RandomPassword { 
    [CmdletBinding()]
    param(
        [Parameter(
            Position = 0,
            Mandatory = $false
        )]
        [ValidateRange(5,79)]
        [int]    $Length = 16,
        [switch] $ExcludeSpecialCharacters
    )
 
    BEGIN {
        $SpecialCharacters = @((33,35) + (37..38) + (42..44) + (60,62,63,64) + (91..94))
    
    # remove " - 34  
    # remove $ - 36
    # remove ' - 39
    # remove = - 61
    }
    PROCESS {
        try {
            if (-not $ExcludeSpecialCharacters) {
                    $Password = -join ((48..57) + (65..90) + (97..122) + $SpecialCharacters | Get-Random -Count $Length | foreach {[char]$_})
                } else {
                    $Password = -join ((48..57) + (65..90) + (97..122) | Get-Random -Count $Length | foreach {[char]$_})
            }
        } catch {
            Write-Error $_.Exception.Message
        }
    }
    END {
        Write-Output $Password
    }
}
foreach ($ser in $servers){
$sapassword = New-RandomPassword -Length 16

$TestResult = New-Object psobject -Property @{
InstanceName = $ser
SaPassword = $sapassword
}
$TestResult | Select-Object InstanceName,SaPassword | Export-Csv -Path "C:\Users\***\Desktop\SQL_SA_pwd.csv" -NoTypeInformation -Append

} 

Here is the ASCII code of the printable characters, you can add or remove some of them in the variable definition, $SpecialCharacters, in my script:

Notes: I removed the special character "$" in the password generation candidate characters because it will cause the issue it is called a reference variable not a special character in the password string!

Also, you can change the length of the password with the parameter "-Length" to meet your company security policy.

When the Powershell script running finished you will get the results in "C:\Users\***\Desktop\SQL_SA_pwd.csv":

Bulk Reset the SA passwords

Once we get the generated SA  password list, e.g.  "C:\Users\***\Desktop\SQL_SA_pwd.csv", we can use the following PowerShell script to bulk reset, this script will read the predefined SA password value and instance name from the file, then it will use the command "Invoke-Sqlcmd" to connect to the SQL instance in the csv file to execute the SQL script:

ALTER LOGIN sa WITH PASSWORD = '$(sapwd)'; GO

In this script, "$(sapwd)" is the variable call of "$sa = $Server.SaPassword" one by one.

Here is the bulk rest SA passwords script:

$Servers = Import-Csv "C:\Users\***\Desktop\SQL_SA_pwd.csv" 
foreach ($Server in $Servers) {
$sa = $Server.SaPassword
$StringArray = "sapwd=$sa"
Invoke-Sqlcmd -ServerInstance $Server.InstanceName -Database master -Query "ALTER LOGIN sa WITH PASSWORD = '$(sapwd)';  
GO" -Variable $StringArray
} 

Notes: Before running the script please make sure you already installed the PowerShell module "sqlserver" on your PC.

Verify the SA passwords are Changed

Last but not least we need to verify the SA passwords change results, in the case due for some reason the SA password change failed. Please use the following PowerShell script to check that, this script will read the instance name and predefined SA password, and it will use the command "Invoke-Sqlcmd" to connect the SQL instance in the csv file to execute the SQL script

SELECT @@SERVERNAME AS ServerName,CASE WHEN (SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('$(sapwd)', password_hash) = 1) = 'sa' THEN 'Yes' ELSE 'No' END saPasswordCorrect; GO

, this SQL script will compare the hash of the predefined SA passwords and existing SA passwords, if the comparison is same it will output value "Yes" for column "SaPasswordCorrect" if not it will output value "No" for column "SaPasswordCorrect" in the csv file C:\Users\***\Desktop\SQL_SA_check.csv.

$Servers = Import-Csv "C:\Users\***\Desktop\SQL_SA_pwd.csv" 
foreach ($Server in $Servers) {
    $sa = $Server.SaPassword
    $StringArray = "sapwd=$sa"
    
    Invoke-Sqlcmd -ServerInstance $Server.InstanceName -Database master -Query "SELECT @@SERVERNAME AS ServerName,CASE WHEN (SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('$(sapwd)', password_hash) = 1) = 'sa' THEN 'Yes' ELSE 'No' END saPasswordCorrect;
    GO" -Variable $StringArray | Export-Csv -Path C:\Users\***\Desktop\SQL_SA_check.csv -Append -NoTypeInformation
}

Check results e.g. "C:\Users\***\Desktop\SQL_SA_check.csv":

If the value of "saPasswordCorrect" is "Yes" it means the SA passwords changed successfully.

When the process is finished you should safely store the password file. You can import the passwords into the password central management platform or tool.

Unlock the SA password(if needed)

If your SA passwords were locked, but you do not want to change them you can use the following SQL script:

ALTER LOGIN sa WITH CHECK_POLICY = OFF;
ALTER LOGIN sa with CHECK_POLICY = ON;
GO

Summary

In this article, you will learn how to use the PowerShell to reduce the manual task( reset SA password) for SQL Server operation and my script also guides you on how to read the variables from a CSV file and how to save the output results to a CSV file, understand how it works then you can modify it to bring my script into more situations for SQL Server daily operations, also the script for verifying the SA password can be used for audit, thanks.