Errors 18487 and 18488: Expired SQL Logins and Password Policy Blocks

Fix it now

18487 means the password of the account has expired. 18488 means it must be changed. Neither is corruption, a network fault or a permission problem, and neither is fixed by recreating the login. The question they raise is whether age-based expiry belongs on this login at all.

SELECT name, is_expiration_checked, is_policy_checked,
       LOGINPROPERTY(name,'IsMustChange')        AS must_change,
       LOGINPROPERTY(name,'IsExpired')           AS expired,
       LOGINPROPERTY(name,'DaysUntilExpiration') AS days_left
FROM   sys.sql_logins
ORDER  BY days_left;

1. For a person, connect once with SSMS using the old password and let it prompt for a replacement, or change it in T-SQL: ALTER LOGIN [jsmith] WITH PASSWORD = 'new' OLD_PASSWORD = 'old';
2. For a service account, set a new password: ALTER LOGIN [svc_app] WITH PASSWORD = '<long random password>';
3. Then decide whether expiry belongs on it: ALTER LOGIN [svc_app] WITH CHECK_EXPIRATION = OFF;
4. For 18488, clear the flag by setting the password again without MUST_CHANGE, then confirm LOGINPROPERTY('svc_app','IsMustChange') returns 0.
5. Restart the application or service so it stops presenting the old password, then confirm it connects.

Stop the application before you reset the password. A service reconnecting with the old one will keep failing, and on a policy that locks accounts it will lock the login again within seconds of each reset.

If the login connects without a change prompt, you are done. If the reset is itself being rejected, the next section covers the policy rules that produce that.

Why it happens

SQL Server does not maintain a password policy of its own. When a SQL login has CHECK_POLICY on, the instance asks Windows to validate the password against the policy in force on the machine hosting SQL Server: length, complexity, history, lockout and maximum age. On a domain member that is the domain policy; on a standalone server it is the local policy. That is the whole mechanism, and it explains most of the surprises.

CHECK_EXPIRATION is the separate switch that applies the maximum-age part, and it depends on CHECK_POLICY. Microsoft documents the T-SQL defaults as CHECK_POLICY on and CHECK_EXPIRATION off, and documents that CHECK_EXPIRATION cannot be turned on while CHECK_POLICY is off. So do not assume from a default what a given login has; read is_expiration_checked and is_policy_checked and find out.

MUST_CHANGE is the third option and it produces 18488. It marks the login so the password an administrator set must be replaced by the user before the login can do anything. Microsoft documents that MUST_CHANGE requires both CHECK_EXPIRATION and CHECK_POLICY to be on, otherwise the statement fails. It is a sensible setting for a person and a poor one for a service, because a service has no way to answer a prompt.

15128 is the engine enforcing exactly that relationship: CHECK_POLICY and CHECK_EXPIRATION cannot be turned off while MUST_CHANGE is on. It is not a fault, it is the order of operations. Clear MUST_CHANGE by setting the password, and only then change the policy switches.

Full reference

Telling the codes apart

15116 and 15118 are frequently treated as the same message and they are not. One is about length and the other about complexity, and knowing which you have saves a round of guessing at a password the policy will accept.

The login options and how they constrain each other

Turning CHECK_POLICY off has two documented side effects people do not expect: the password history is cleared, and the lockout time is reset. Turning it back on initialises the history with the current password hash. If you are toggling it to unlock an account, that is fine; if you are toggling it in a hardened environment, know what you are resetting.

Reading LOGINPROPERTY without guessing

A NULL from DaysUntilExpiration is the answer people misread most often. It does not mean the login is fine; it means expiry is not being checked on it at all, which may or may not be what you intended. -1 is different again, and means the policy in force never expires passwords.

Unlocking without changing the password

Microsoft documents toggling CHECK_POLICY off and back on as a way to unlock a login without setting a new password. It works because turning the policy off resets the lockout time. Use it when the password is known to be correct and an application simply hammered it until the account locked, and pair it with fixing whatever was hammering it, because otherwise the account locks again on the next retry.

Seeing the next wave before it lands

A maximum password age is measured from the day the password was set, so logins created together expire together, and nobody records the date at creation time. The query at the top of this article, ordered by days remaining, tells you which logins expire next. Run it on a schedule and this error stops being a surprise, which is a better outcome than getting quicker at fixing it.

Every code this article covers

Confirm the fix worked

1. Connect with the affected login and confirm it succeeds without a change prompt.
2. SELECT LOGINPROPERTY('<login>','IsMustChange'); returns 0.
3. SELECT LOGINPROPERTY('<login>','DaysUntilExpiration'); returns a value you understand: a number you are comfortable with, -1 for a policy that never expires, or NULL because expiry is deliberately off.
4. SELECT LOGINPROPERTY('<login>','BadPasswordCount'); is not climbing, which proves nothing is still retrying with the old password.
5. The application reconnects and runs its real workload, not just a test from SSMS.

Questions people ask about this