Find blank passwords - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Find blank passwords

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-29 : 07:10:45

Hi,

I am trying to audit our SQL servers is there a query I can run as admin that will tell me which accounts have blank passwords.

Cheers D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-29 : 07:29:40

Most probably all blank passwords have the same hash value in table master..syslogins.
Create a new unique user with blank password and copy the password hash in master..syslogins.

Most probably all other equal password hash also have blank password.

E 12°55'05.25"
N 56°04'39.16"

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-29 : 09:56:57

Cheers the password is actualy NULL in that table for blanks!! Cheers though I needed the table to look in.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-29 : 10:13:46

This script will find your sloppy users:


--Password Check
--blindman, 9/6/2007

set nocount on

declare	@TestLogin varchar(100)
declare	@TestPassword varchar(100)
declare	@Result int
declare	@OSQLString varchar(500)
declare	@Counter int
declare	@StockPasswords table (StockPassword varchar(128))

insert into @StockPasswords (StockPassword)
select 	''
union select 'Password'
union select 'sa'

/*
--Allow command shell
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
*/

/*
--Prevent command shell
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
*/

--Check for passwords similiar to login
declare	LoginCursor Cursor dynamic for 
select	loginname
from	master..syslogins
where	isntname = 0
		and loginname not like '#%'

declare	PasswordCursor Cursor dynamic for 
select	StockPassword
from	@StockPasswords

open	LoginCursor
open PasswordCursor

fetch first from LoginCursor into @TestLogin

while	@@Fetch_Status = 0
	begin
	--check for exact match
	set	@TestPassword = @TestLogin
	set	@OSQLString = 'osql -U' + @TestLogin + ' -P' + @TestPassword + ' -S' + @@ServerName
	--print	@OSQLString
	exec	@Result = master..xp_cmdshell @OSQLString, NO_OUTPUT
	if @Result = 0 select 'Exact match: ' + convert(varchar(50), @TestLogin + '/' + @TestPassword)
	else --Check for simple appended numerics
		begin
		set	@Counter = 0
		while	@Counter < 10
			begin
			set	@TestPassword = @TestLogin + right('0' + cast(@Counter as varchar(2)), 2)
			set	@OSQLString = 'osql -U' + @TestLogin + ' -P' + @TestPassword + ' -S' + @@ServerName
			exec	@Result = master..xp_cmdshell @OSQLString, NO_OUTPUT
			if @Result = 0 print 'Close match: ' + convert(varchar(50), @TestLogin + '/' + @TestPassword)
			set @Counter = @Counter + 1
			end
		end

	--check for stock passwords
	fetch first from PasswordCursor into @TestPassword

	while	@@Fetch_Status = 0
		begin
		set	@OSQLString = 'osql -U' + @TestLogin + ' -P' + @TestPassword + ' -S' + @@ServerName
		if @Result = 0 select 'Stock password match: ' + convert(varchar(50), @TestLogin + '/' + Coalesce(nullif(@TestPassword, ''), ''''''))
		fetch next from PasswordCursor into @TestPassword
		end

	fetch next from LoginCursor into @TestLogin

	end

close LoginCursor
deallocate LoginCursor
close PasswordCursor
deallocate PasswordCursor

e4 d5 xd5 Nf6

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-29 : 11:23:59

Cheers for the script it found a couple of accounts with passwords that match the username. It does not show up any of the accounts with blank passwords though.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-29 : 13:19:55

Should be easy to edit to add a section to check for blank passwords.
When I get a chance I'll modify it, or you can and then post the change.

e4 d5 xd5 Nf6

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-29 : 13:32:55

Oops. There was a line left out of that code. Try this, which caught some test logins without passwords for me:


--Password Check
--blindman, 9/6/2007
----------------------------------------------------------------
--blindman, 7/29/2008: Fixed error checking passwords.
----------------------------------------------------------------

set nocount on

declare	@TestLogin varchar(100)
declare	@TestPassword varchar(100)
declare	@Result int
declare	@OSQLString varchar(500)
declare	@Counter int
declare	@StockPasswords table (StockPassword varchar(128))

insert into @StockPasswords (StockPassword)
select 	''
union select 'Password'
union select 'sa'

/*
--Allow command shell
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
*/

/*
--Prevent command shell
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
*/

--Check for passwords similiar to login
declare	LoginCursor Cursor dynamic for 
select	loginname
from	master..syslogins
where	isntname = 0
		and loginname not like '#%'

declare	PasswordCursor Cursor dynamic for 
select	StockPassword
from	@StockPasswords

open	LoginCursor
open PasswordCursor

fetch first from LoginCursor into @TestLogin

while	@@Fetch_Status = 0
	begin
	--check for exact match
	set	@TestPassword = @TestLogin
	set	@OSQLString = 'osql -U' + @TestLogin + ' -P' + @TestPassword + ' -S' + @@ServerName
	--print	@OSQLString
	exec	@Result = master..xp_cmdshell @OSQLString, NO_OUTPUT
	if @Result = 0 select 'Exact match: ' + convert(varchar(50), @TestLogin + '/' + @TestPassword)
	else --Check for simple appended numerics
		begin
		set	@Counter = 0
		while	@Counter < 10
			begin
			set	@TestPassword = @TestLogin + right('0' + cast(@Counter as varchar(2)), 2)
			set	@OSQLString = 'osql -U' + @TestLogin + ' -P' + @TestPassword + ' -S' + @@ServerName
			exec	@Result = master..xp_cmdshell @OSQLString, NO_OUTPUT
			if @Result = 0 print 'Close match: ' + convert(varchar(50), @TestLogin + '/' + @TestPassword)
			set @Counter = @Counter + 1
			end
		end

	--check for stock passwords
	fetch first from PasswordCursor into @TestPassword

	while	@@Fetch_Status = 0
		begin
		set	@OSQLString = 'osql -U' + @TestLogin + ' -P' + @TestPassword + ' -S' + @@ServerName
		exec	@Result = master..xp_cmdshell @OSQLString, NO_OUTPUT
		if @Result = 0 select 'Stock password match: ' + convert(varchar(50), @TestLogin + '/' + Coalesce(nullif(@TestPassword, ''), ''''''))
		fetch next from PasswordCursor into @TestPassword
		end

	fetch next from LoginCursor into @TestLogin

	end

close LoginCursor
deallocate LoginCursor
close PasswordCursor
deallocate PasswordCursor