Password expectations set by Windows do not automatically apply to every SQL login. CHECK_POLICY determines whether SQL Server applies the Windows password-policy mechanisms to that login. Expiration is a related setting with a separate operational decision.
Start With SQL Authentication and Windows Scope
These examples concern SQL Server running on Windows and logins using SQL authentication. Windows-authenticated identities follow their own account-policy arrangements. Do not interpret SQL login flags as controls over the underlying Windows account.
SQL Server can use Windows password validation mechanisms for complexity, history, and lockout behavior. Effective policy can come from local settings or applicable domain policy. Review the actual server policy before assuming what a checked flag enforces.
I inspect login settings alongside the identity's owner and purpose. I also ask how its credentials are rotated in the application. A secure-looking flag cannot update a connection string hidden on an unattended server.
Azure SQL Database has different enforcement behavior and should not inherit this Windows expiration workflow. It enforces password complexity without the same password-expiration policy arrangement. Keep the deployment boundary clear when reusing an audit query across environments.
Find CHECK_POLICY Exceptions Without Reading Password Hashes
The catalog exposes whether each visible SQL login has policy and expiration checks enabled. Start with an inventory, then focus on unchecked policy. Metadata visibility limits the population, so perform a full audit under an appropriately authorized identity.
SELECT name, is_disabled, is_policy_checked, is_expiration_checked,
create_date, modify_date
FROM sys.sql_logins
ORDER BY name;
SELECT name, is_disabled, is_expiration_checked
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY name;An unchecked policy flag deserves an explanation, but it does not reveal the password's actual strength. A checked flag also does not establish that the existing password was recently changed. Separate configuration evidence from credential-lifecycle evidence.
Do not retrieve password_hash for an ordinary configuration review. These checks need names, flags, and policy properties instead. Protect the audit output because login names and operational identities still describe sensitive administration details.
Include disabled logins in the review. They can be re-enabled later with their previous configuration. Give every retained exception an owner and a review date rather than treating disabled status as permanent retirement.
Read Lockout and Password-Age Properties
LOGINPROPERTY returns individual policy properties without exposing the credential itself. BadPasswordCount describes consecutive failed password attempts, not the total count of all historical login failures. PasswordLastSetTime tells you when the current password was set.
SELECT name,
LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime,
LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime,
LOGINPROPERTY(name, 'IsLocked') AS IsLocked,
LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime,
LOGINPROPERTY(name, 'IsExpired') AS IsExpired,
LOGINPROPERTY(name, 'IsMustChange') AS IsMustChange,
LOGINPROPERTY(name, 'DaysUntilExpiration') AS DaysUntilExpiration
FROM sys.sql_logins
ORDER BY name;Returned properties depend on supported policy state and visibility. NULL does not universally mean zero failures or no expiration risk. Read the function's property-specific meaning and the login's flags together before assigning an audit verdict.
DaysUntilExpiration can be NULL when the relevant checks are disabled. It can return minus one when the applicable policy never expires the password. These outcomes need descriptive reporting rather than forcing every value into a countdown.
A locked login also needs a cause investigation. An application using an old password can immediately repeat failed attempts after an administrator unlocks it. Fix the credential source before treating repeated unlocking as the operational solution.
Turn On CHECK_POLICY With an Explicit Expiration Choice
Use a dedicated test SQL login created through your approved credential process, on a test instance. ALTER LOGIN changes a server-level object, not a database setting. The next example assumes PolicyTestLogin already exists. It intentionally changes only that named demonstration login, rather than rewriting every login returned by the audit.
IF NOT EXISTS
(
SELECT 1 FROM sys.sql_logins WHERE name = N'PolicyTestLogin'
)
THROW 51000, 'Create the dedicated test SQL login first.', 1;
ALTER LOGIN PolicyTestLogin
WITH CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name = N'PolicyTestLogin';When policy is turned on, specify the expiration choice explicitly. Turning policy off also turns expiration off and clears associated history and lockout state. Do not toggle it off and on as a casual way to clear an operational symptom.
Enabling checks initializes password history using the current password hash. It does not reveal or prove the complexity of the existing secret. Plan a controlled password change when a legacy credential's strength or ownership is uncertain.
For an interactive test identity requiring expiration, use the next change after confirming its intended login behavior. Expiration cannot be enabled while policy is disabled. MUST_CHANGE also requires both settings enabled when assigning a password that must change.
ALTER LOGIN PolicyTestLogin
WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
SELECT name, is_policy_checked, is_expiration_checked,
LOGINPROPERTY(name, 'IsExpired') AS IsExpired,
LOGINPROPERTY(name, 'IsMustChange') AS IsMustChange
FROM sys.sql_logins
WHERE name = N'PolicyTestLogin';Do not print a password into a public sample or a routine audit log. Use an approved secret-generation and storage process for the actual rotation. Administrative password resets and user changes have different history behavior, so review the supported ALTER LOGIN rules.
Treat Service Expiration as an Operating Contract
An unattended service cannot answer an interactive password-change prompt during startup. Uncoordinated expiration can therefore stop connections at an inconvenient moment. That is a reason to design credential rotation, not a reason to keep a weak shared secret forever.
Where an approved service-account exception disables expiration, retain strong unique credentials and the applicable policy checks. Restrict permissions to the service's actual work. Store the secret securely, assign an owner, and rotate it through a tested coordinated process.
CHECK_EXPIRATION=OFF does not disable complexity enforcement when policy remains enabled. It also does not prevent rotation or eliminate incident-driven replacement. The exception concerns automatic age-based expiration, not permission to ignore credential management.
Consider supported Windows authentication for services when it fits the environment and account-management design. That changes the identity arrangement rather than simply adding another SQL login flag. Test the actual service identity and its database permissions before adopting it.
Verify Changes From the Application's Point of View
After a controlled rotation or policy change, re-read the catalog and relevant properties. Verify fresh application connections, rather than only existing pooled connections. A pooled session can continue working while the next physical login exposes the broken credential source.
Test restart and failure recovery for the service using its approved credential arrangement. Include the owner who can update all dependent locations. Do not intentionally lock a production service login merely to prove that its policy works.
I review CHECK_POLICY exceptions with both the security and application owners. I also require a clear rotation procedure for nonexpiring service credentials. The password policy should not be the only team member who remembers the account exists.
Who updates every dependent credential location when this login's password changes? Answer that before enabling an expiration requirement. Verify CHECK_POLICY again when reviewing the resulting login configuration. Keep the demonstration login's final settings consistent with its approved purpose, or remove the dedicated test identity after the review.
Related reading on this blog: Setting up a Robust Password Policy and Unlocking User Without Changing Password.
A password-policy flag is not complete credential management, it is one control in an owned identity lifecycle.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
