Find weak login passwords in your server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Thao Truong » Find weak login passwords in your server

Find weak login passwords in your server

Rate Topic

Display Mode

Topic Options

Author

Message

dachimoto

Posted Wednesday, December 16, 2009 12:38 PM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 17, 2012 8:12 AM
Points: 76, Visits: 72

Comments posted to this topic are about the item Find weak login passwords in your server

Post #835388

Kuido Külm

Posted Tuesday, January 05, 2010 1:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 15, 2012 1:12 AM
Points: 185, Visits: 43

Can also add password REVERSE option and add login default database owner to select clause

SELECT sql_logins.name AS [LoginName],
CASE
WHEN PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name)),password_hash) = 0 THEN REPLACE(t2.WeakPwd,'@@Name',sql_logins.name)
ELSE REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name))
END AS [Password]
,sql_logins.default_database_name,sql_logins.is_policy_checked,sql_logins.is_expiration_checked,sql_logins.is_disabled
,(SELECT suser_sname(owner_sid) FROM sys.databases WHERE databases.name = sql_logins.default_database_name) AS database_owner
FROM sys.sql_logins INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',sql_logins.name),password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name)),password_hash) = 1 )
--WHERE sql_logins.is_disabled=0
ORDER BY sql_logins.name

Post #841905

parmstrong1107

Posted Tuesday, January 05, 2010 8:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 8:11 AM
Points: 35, Visits: 145

Have you considered simply enforcing password policy?

Post #842154

Lee Linares

Posted Tuesday, January 05, 2010 11:18 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, May 18, 2013 5:18 PM
Points: 69, Visits: 584

I found that if I included 'password', 'PASSWORD', and 'Password' in the @WeakPwdList table variable the script would not return all users with those passwords. The fix was to use UNION ALL instead of UNION. Thanks for the code. This is very useful.

Post #842274

bennie.roodt

Posted Friday, May 14, 2010 2:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 11:12 PM
Points: 1, Visits: 192

Hi,

Here is a script that will work for SQL2000 too.

DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))
--Define weak password list
--Use @@Name if users password contain their name
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''
UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT 'abc'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '123456'
UNION SELECT '@@Name123'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
-- SELECT * FROM @WeakPwdList
SELECT syslogins.name AS [LoginName],
CASE
WHEN PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(syslogins.name)),password) = 0 THEN REPLACE(t2.WeakPwd,'@@Name',syslogins.name)
ELSE REPLACE(t2.WeakPwd,'@@Name',REVERSE(syslogins.name))
END AS [Password]
,syslogins.dbname as Default_Database
,(SELECT suser_sname(sid) FROM sysdatabases WHERE sysdatabases.name = syslogins.dbname) AS database_owner
FROM syslogins INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',syslogins.name),password) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(syslogins.name)),password) = 1 )
--WHERE syslogins.is_disabled=0
ORDER BY syslogins.name

Post #921812

c07550285

Posted Tuesday, May 18, 2010 9:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:05 PM
Points: 2, Visits: 22

Hi, i get this error, any help?.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the replace operation.(42000,468)

thanks

Post #923654

Kuido Külm

Posted Tuesday, May 18, 2010 11:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 15, 2012 1:12 AM
Points: 185, Visits: 43

Add COLLATE DATABASE_DEFAULT to table variable definition

DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255) COLLATE DATABASE_DEFAULT )

Post #924058

c07550285

Posted Wednesday, May 19, 2010 6:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:05 PM
Points: 2, Visits: 22

it works, thanks.

Post #924220

rokuba

Posted Sunday, November 07, 2010 2:33 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 2:33 AM
Points: 72, Visits: 128

see here

Post #1016920

« Prev Topic | Next Topic »

Permissions