Audit SQL Passwords for Easily Guessed

In case you are in scenario where you do not have complete control over the install of SQL there are a number of problems that can go wrong by letting non-DBAs perform the work. One of the biggest is setting the sa password to an easily guessed one. You are then on the hook for any problems that one of your well meaning developers creates by making changes with the sa user.

I like to perform annual audits of the sa user to ensure that none of servers are using some standard passwords for this account. I check for blank password, password = password, and password = sa utilizing a powershell script which records the results of all servers for documentation purposes. This is also a great tool to satisfy your Audit department!

When running comment out the passwords that you do not want to check and change the user id and password as needed.

Thanks to K. Brian Kelley for the original script.


# $serverList contains the list of SQL Server instances to audit
# $auditReport is the path to the file where the results are written
$serverList = "c:\Documents\servers.txt";
#$auditReport = "c:\Documents\sa_password_password_audit.txt";
$auditReport = "c:\Documents\sa_password_blank_audit.txt";
#$auditReport = "c:\Documents\sa_password_sa_audit.txt";
# cycle through each SQL Server instance and report the findings to the screen
ForEach ($SQLServer in Get-Content $serverList)
{
 
    #$connStr = "Server=$SQLServer; User Id=sa; Pwd=password"
    $connStr = "Server=$SQLServer; User Id=sa; Pwd="
    #$connStr = "Server=$SQLServer; User Id=sa; Pwd=sa"
       
    $conn = new-object System.Data.SqlClient.SqlConnection($connStr)
    try
    {
        $conn.Open()
        " !! Problem: Connected with password SA password to $SQLServer !!" | Out-File -filepath $auditReport -append;
    }
    catch [System.Data.SqlClient.SqlException]
    {
 "$SQLServer - OK." | Out-File -filepath $auditReport -append;       
    }
    $conn.Close();
}