Using PowerShell to Audit for Blank SA Passwords

By:   |   Updated: 2012-09-04   |   Comments   |   Related: More > 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


Last Updated: 2012-09-04


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools