Find weak login passwords in your server

  • dachimoto

    SSC Veteran

    Points: 220

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

  • Kuido Külm

    SSC-Addicted

    Points: 493

    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

  • parmstrong1107

    SSC Enthusiast

    Points: 195

    Have you considered simply enforcing password policy?

  • Lee Linares

    SSCrazy

    Points: 2662

    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.

  • bennie.roodt

    SSC Rookie

    Points: 37

    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

  • c07550285

    SSC Rookie

    Points: 46

    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

  • Kuido Külm

    SSC-Addicted

    Points: 493

    Add COLLATE DATABASE_DEFAULT to table variable definition

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

  • c07550285

    SSC Rookie

    Points: 46

    it works, thanks.

  • rokuba

    SSC Enthusiast

    Points: 154

    see here

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply