http://www.sqlservercentral.com/blogs/brian_kelley/2008/11/01/auditing-for-blank-passwords/

Printed 2014/10/22 06:29AM

Auditing for Blank Passwords

2008/11/01

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 @SQL NVARCHAR(4000); 
DECLARE @login sysname


DECLARE cursLogins CURSOR 
FORWARD_ONLY 
FOR SELECT name FROM sys.sql_logins


OPEN cursLogins


FETCH NEXT FROM cursLogins INTO @login


WHILE @@FETCH_STATUS 

BEGIN 
  PRINT 
'Testing [' @login ']...'

  
SET @SQL 'ALTER LOGIN [' @login '] WITH PASSWORD = '''' OLD_PASSWORD = '''';'

  
EXEC(@SQL
); 
  
FETCH NEXT FROM cursLogins INTO @login

END 

CLOSE 
cursLogins

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-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.