'CHECK_POLICY' Option is set to 'ON' for SQL Server logins

Question

Hi,

We are using SQL Server 2017 in production. Many of the application is running on these databases. Recently our security team asked to ensure 'CHECK_POLICY' Option is set to 'ON' for all SQL authenticated Logins.

If we set this for any existing login and if the password was simple then will the login work afterwards when we set 'CHECK_POLICY' Option is set to 'ON' or we have to create new password for the logins to meet password complexity?

Answer 1

Why don't you test it? Create login with CHECK_POLICY=OFF and with a password of a single letter. Alter the login to set CHECK_POLICY=ON. Test if you can still log in.

I did not test this myself, I would be very surprised if you cannot, since there is not really a way to check the complexity of an existing password. Remember that they are stored as salted hashes, so it is very difficult to retrieve the password by a direct lookup. (But a brute-force attack will find it quickly enough for an attacker.)

Answer 2

Hi @Kazi Ariful Haq ,

Based on my test, after modifying check_policy from off to on, you can still log in to SQL Server.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 3

Hi there,

I think Microsoft Learn are quite clear
https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy?view=sql-server-ver15

"Password Complexity
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:"

So, for existing SQL logins it does not matter.
Only if you set a new password.

Regarding the policy enforcment:
Policy Enforcement
The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:

When CHECK_POLICY is changed to ON, the following behaviors occur:

CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.

The password history is initialized with the value of the current password hash.

So, once you activate the policy and renew a password you cannot use the recent one immediatly.