Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find weak login passwords in your server Expand / Collapse
Author
Message
Posted Wednesday, December 16, 2009 12:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 7:19 PM
Points: 76, Visits: 73
Comments posted to this topic are about the item Find weak login passwords in your server
Post #835388
Posted Tuesday, January 05, 2010 1:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, January 05, 2010 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 35, Visits: 174
Have you considered simply enforcing password policy?


Post #842154
Posted Tuesday, January 05, 2010 11:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 6:11 AM
Points: 83, Visits: 819
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
Posted Friday, May 14, 2010 2:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:15 PM
Points: 1, Visits: 250
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
Posted Tuesday, May 18, 2010 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 06, 2014 5:24 AM
Points: 2, Visits: 24
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
Posted Tuesday, May 18, 2010 11:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, May 19, 2010 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 06, 2014 5:24 AM
Points: 2, Visits: 24
it works, thanks.
Post #924220
Posted Sunday, November 07, 2010 2:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 »

Add to briefcase

Permissions Expand / Collapse