How to enforce password policy for a SQL Server login? Check the login’s current setting, then use ALTER LOGIN ... CHECK_POLICY = ON if policy enforcement is required.
The interview question usually starts with a domain-joined Windows server and a strict organization password policy. SQL Server can apply the Windows password policy to SQL Server authentication logins. The applicable policy comes from the host’s local Windows settings or its domain. Windows-authenticated logins are managed by Windows instead of this SQL login option.
Check the login first
For a SQL login named sqlauthority, this read-only query shows both policy and expiration settings:
SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name = N'sqlauthority';If the login exists and policy checking is off, an administrator can enable it with:
ALTER LOGIN [sqlauthority] WITH CHECK_POLICY = ON;I left the password out deliberately. My old example combined CHECK_POLICY = ON with PASSWORD = 'sql', which is a weak sample password and a poor illustration of the policy we are trying to enforce. Use your approved credential-change process when a password must be reset.
Know what changes
When policy checking changes from off to on, SQL Server also turns password expiration on unless you explicitly leave expiration off. It initializes password history from the current password hash. Check the login’s operational requirements before changing either setting. Turning a check on does not manufacture a strong password or replace the organization’s credential review.
The default for a newly created SQL login is policy checking on, but I would still verify the actual login. A domain policy is not the only possible source; Windows local policy can be the source too. A missing or weak host policy limits what SQL Server can enforce.
For broader security context, see my posts on beginning SQL Server security, a security primer, contained-database security, and security conversations with a DBA.
References: Microsoft SQL Server password policy and ALTER LOGIN.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
