By: K. Brian Kelley | Updated: 2012-09-04 | Comments | Related: > Security
Problem
I need to automate the auditing of my SQL Servers for blank sa passwords. What's the best way to do this? Are there any options in PowerShell? Check out this tip to learn more.
Solution
With the use of PowerShell, this becomes a pretty easy thing to do. Using a trick from a previous tip, we can pull a list of SQL Servers from a text file and then test each one to see if we can log in via sa using no password. Below is an example of the text file. Note that each SQL Server instance is listed on a separate line:
mysqlserver\sql2005
mysqlserver\sql2008
mysqlserver\sql2008r2
First, let's define the loop where we get the SQL Servers from the text file:
# $serverList contains the list of SQL Server instances to audit
$serverList = "c:\scripts\servers.txt";
# cycle through each SQL Server instance and report the findings to the screen
ForEach ($SQLServer in Get-Content $serverList)
{
# Audit code goes here
}
What we'll need to do is attempt to establish a connection to each SQL Server using a connection string with the username of sa and a blank password. We can test to see if the connection is successful using try...catch. If the connection is successful, that's a problem. That means we were able to login as sa with a blank password. In that case, we need to raise an alert that the SQL Server is vulnerable. On the other hand, if the connection fails, then we know attempting to logon with a blank sa password was unsuccessful. So if we put all this together, we're left with:
# $serverList contains the list of SQL Server instances to audit
$serverList = "c:\scripts\servers.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="
$conn = new-object System.Data.SqlClient.SqlConnection($connStr)
try
{
$conn.Open()
Write-Host " !! Problem: Connected with blank SA password to $SQLServer !!"
}
catch [System.Data.SqlClient.SqlException]
{
Write-Host "$SQLServer - OK."
}
$conn.Close();
}
Below is an example of the output. Here we see that the sql2008 named instance has been flagged as having a blank sa password.
mysqlserver\sql2005 - OK.
!! Problem: Connected with blank SA password to mysqlserver\sql2008 !!
mysqlserver\sql2008r2 - OK.
If you're automating it, however, you probably don't want the output to go to the screen. The easiest way to change the script is to add another variable, $auditReport, which has the path to the audit file where we will write our results and to pipe our output string to that file instead of using Write-Host. In that case we'll use the Out-File with the -append option like so:
# $serverList contains the list of SQL Server instances to audit
# $auditReport is the path to the file where the results are written
$serverList = "c:\scripts\servers.txt";
$auditReport = "c:\scripts\sa_blank_password_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="
$conn = new-object System.Data.SqlClient.SqlConnection($connStr)
try
{
$conn.Open()
# Use of the ` character extends the line
" !! Problem: Connected with blank SA password to $SQLServer !!" | Out-File `
-filepath $auditReport -append;
}
catch [System.Data.SqlClient.SqlException]
{
"$SQLServer - OK." | Out-File -filepath $auditReport -append;
}
$conn.Close();
}
Next Steps
- Learn how to audit who are members of the sysadmin group on multiple servers using PowerShell.
- Understand what SQL Server 2005 and above provide to help you ensure logins don't have blank passwords.
- Read up on how to connect to SQL Server from PowerShell using SMO.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-09-04
