Sql-server – Find accounts with passwords that are not complex

passwordSecuritysql server 2014

I can see all the SQL Login passwords that enforce the password policy with this command

SELECT * FROM sys.sql_logins WHERE is_policy_checked = 1

However, it seems that when the enforce password policy is checked, it requires the next password change to enforce the policy and doesn't mean that current password does:

This MS article states

Password complexity policies are designed to deter brute force attacks
by increasing the number of possible passwords. When password
complexity policy is enforced, new passwords must meet the following
guidelines:

I can verify the above as follows

Create login with weak password:

CREATE LOGIN [TestLogin] WITH PASSWORD=N'weak', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Enforce the policy

ALTER LOGIN [TestLogin] WITH CHECK_POLICY=ON

at this point, the following query returns 1

SELECT is_policy_checked FROM sys.sql_logins WHERE name = 'TestLogin'

So is there anyway I can check which SQL accounts have a weak password? My guess is "no" as I would have thought this was a bit of a security vulnerability and the only way would be to change all the passwords when the policy has been set to check.

Best Answer

Maybe that will help PWDCOMPARE (https://docs.microsoft.com/en-us/sql/t-sql/functions/pwdcompare-transact-sql?view=sql-server-2017)?

Some example: https://dzone.com/articles/use-pwdcompare-to-find-sql-logins-with-weak-passwo

Credits @SQLRockstar link

First, let's build a list of weak passwords and save it to file:

$pwdList = Get-Content .\password_list.txt

Build a query and execute against SQL Server:

$SQLText = "SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('$password', password_hash) = 1;"
Invoke-Sqlcmd -Query $SQLText -ServerInstance $SQLServer
}
Related Question