Login Failed: Password Expired or Must Be Changed (Errors 18487 and 18488)

A user or an application suddenly cannot connect, and for once the message says why: the password. These two errors only ever happen to SQL Server logins, never Windows ones, and they are the password policy doing exactly what it was set up to do. The work is deciding whether it should be.

Which One You Have

• 18488, must be changed. The login was created or reset with MUST_CHANGE, so SQL Server will not let it in until the password is changed. Common on a login a DBA set up for a person, then handed to an application.
• 18487, expired. The login has CHECK_EXPIRATION on and its password is older than the Windows password policy allows. It worked for weeks, then stopped overnight with nothing deployed. The message reads Reason: The password of the account has expired.

18488 is what the lab below produced on demand. 18487 cannot be produced on demand, because it needs the policy’s age to pass; the cause, the check and the fix are the same, and the message text is SQL Server’s own, from sys.messages. Microsoft’s password policy page covers how the two settings work.

Check the Login

SELECT name,
       is_policy_checked,
       is_expiration_checked,
       LOGINPROPERTY(name, 'IsMustChange') AS is_must_change,
       LOGINPROPERTY(name, 'IsExpired') AS is_expired,
       LOGINPROPERTY(name, 'DaysUntilExpiration') AS days_until_expiration
FROM sys.sql_logins
WHERE name = N'zz_pwd_must';

is_must_change = 1 is your 18488, is_expired = 1 is your 18487. The error log says the same thing with a time and the client address, which is how you find out which application has been failing: Error: 18488, Severity: 14, State: 1 followed by the login and the reason. Troubleshoot Login Failed for User (Error 18456) covers reading that log when the reason is less helpful.

The Fix

A person’s login: let them change it. SSMS prompts for a new password when a login with must-change connects, and anyone can change their own with ALTER LOGIN ... WITH PASSWORD = N'new' OLD_PASSWORD = N'old'.

An application’s login: nobody is there to answer a prompt, so the DBA resets it, and the order matters. Try to switch expiry off first and SQL Server refuses:

Reset the password first. That clears must-change on its own:

ALTER LOGIN zz_pwd_must WITH PASSWORD = N'';

-- for a service or application login only, and only after the reset:
ALTER LOGIN zz_pwd_must WITH CHECK_EXPIRATION = OFF;

Must-change went to 0 and the expiry clock restarted at 42 days, the lab’s Windows password policy. The login connected straight away. Turning CHECK_EXPIRATION off then worked, and left CHECK_POLICY on, so the password still has to meet complexity rules. Switching expiry off is a security decision, not a tidy-up: an application login that never expires needs rotating on your own schedule instead. Microsoft’s ALTER LOGIN page has every option.

Catch the Next One Before It Expires

Every SQL login with expiry on is an 18487 with a date. This lists them, soonest first, so the application logins among them get dealt with on a quiet afternoon:

SELECT name,
       LOGINPROPERTY(name, 'DaysUntilExpiration') AS days_until_expiration,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set
FROM sys.sql_logins
WHERE is_expiration_checked = 1
  AND is_disabled = 0
ORDER BY days_until_expiration;

Get Login Security Audit covers the rest of a login review: policy off, sysadmin members, and logins nobody has used.

Best Practices

• Keep CHECK_POLICY on for every SQL login. It is what stops weak passwords. Switching it off to get past one of these errors swaps a login problem for a worse one.
• Expiry suits people, not applications. Leave CHECK_EXPIRATION on for a person’s login. For an application login it is often not practical: the password lives in a config file or a vault, and an expiry nobody planned for is exactly how 18487 takes an application down overnight. If you turn it off, rotate the password on your own schedule instead.
• Better still, stop giving applications SQL logins. Where the application runs on Windows in the domain, run it under a group Managed Service Account (gMSA) and give that account a Windows login in SQL Server, CREATE LOGIN [DOMAIN\app-gmsa$] FROM WINDOWS. Active Directory changes its password automatically, every 30 days by default, and nobody ever types or stores it, so there is nothing to expire, reset or leak.
• Where a gMSA will not fit, a non-Windows client or a vendor product that insists on SQL authentication, use an AD service account or SQL login whose password a vault rotates, not one somebody remembers to change.

Microsoft’s gMSA guide covers creating one. A gMSA connecting over Windows authentication also needs its SPNs right for Kerberos; Kerberos vs NTLM covers that.

Frequently Asked Questions

Where To Go Next