Find accounts with passwords that are not complex

Question

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.

Answer 1

Maybe that will help PWDCOMPARE (https://learn.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
}

Answer 2

If you have any enforcement responsibility or ability;

After you have modified enfource password policy in SQL. You make a mandate that passwords need to be updated by a specific time/date and you can validate that has occured with

SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');

Code source

In general, all passwords should be changed regularly so it should be relatively easy to defend this policy change.

Automatic expiring passwords can be problematic for application access, so this may or may not be a viable solution in your environment. But manually password changes should not pose a problem.