3

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

2

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
}
  • I don't think this will help with this question since the existing passwords are not known. – Dan Guzman Jun 28 at 11:06
  • @DanGuzman same in the example from DZONE. They built the list of weak passwords and just compare it with existing passwords in the system. Unless i am missing something. Can you please elaborate why the example is not good for this case? – Mikey Bronowski Jun 28 at 11:40
  • Another feature I did not know about. This can be used to find weak passwords if they are common. Is it an oxymoron to have a weak uncommon password? – kevinsky Jun 28 at 11:46
  • 2
    @MikeyBronowski, the question asks "check which SQL accounts have a weak password", meaning that they do not meet the complexity rules if CHECK_POLICY=ON were turned enforced. If you have a solution to check existing login passwords, add the code to your answer as I did not see it in the links. Also, when posting links in answers, one should include the relevant code snippet or excerpt in case the urn changes or content removed. – Dan Guzman Jun 28 at 12:46
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.