Printed 2015/10/08 10:08PM

Auditing for Blank Passwords


Auditing blank passwords in SQL Server 2005 and 2008 proves a bit more challenging than in SQL Server 2000. In SQL Server 2000 we could query syslogins and see what the password column contained. If it was NULL, then we knew that if that was a SQL Server login, we had a problem. In SQL Server 2005 and 2008, however, if we query sys.sql_logins, the password_hash column will always be populated, even if the password is blank. That means we'll need another means to audit the blank password. The trick is to attempt a password reset where we specify the old password to be blank. Here's a quick script that tests all of the SQL Server logins on a server.

DECLARE @login sysname

FOR SELECT name FROM sys.sql_logins

OPEN cursLogins

FETCH NEXT FROM cursLogins INTO @login


'Testing [' @login ']...'


FETCH NEXT FROM cursLogins INTO @login



DEALLOCATE cursLogins;  

In our results pane, if we get an error, that's a good thing. It means that the old password wasn't blank. However, if we don't get an error, we have a problem. Here is an example:

Testing [RegularJoe]...
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'RegularJoe', because it does not exist or you do not have permission.

Testing [BlankPassword]...

With RegularJoe we receive an error saying we can't change the password because we don't have permission. This is because the OLD_PASSWORD option was specified. I'm running with sysadmin rights, so normally I would be able to do so. However, the OLD_PASSWORD option is the trick. By setting it to blank, we can verify that none of our SQL Server based logins are blank. If they are, we won't see an error, like with the login I created called BlankPassword. No error means the password reset was successful. And that means the old password was blank. That login will need correcting.


Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.