Have you ever created a SQL Server login and left 'Enforce password policy' checked by mistake?

Getting the exception 'Login failed for user ApplicationUser.  Reason: The password of the account must be changed.' when you're not expecting it is a sure sign someone forgot to untick the password policy checkboxes when they created your application user

There are three little checkboxes that are ticked by default when creating a user through the SQL Server Management Studio user interface:

  • Enforce password policy
  • Enforce password expiration
  • User must change password at next login

These map to the options CHECK_POLICY=ON, CHECK_EXPIRATION=ON and PASSWORD=N'xxxxxxx' MUST_CHANGE respectively if you're creating the user via script. The problem/issue with them is that they're blocking to the application and, if switched on in error, not something you want left switched on. With MUST_CHANGE switched on you'll see this exception when code tries to connect to SQL Server:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Login failed for user 'ApplicationUser'.  Reason: The password of the account must be changed.

The quickest way to correct this is to run the following script (with a user that has rights to do so, that isn't your application user!):

ALTER LOGIN [ApplicationUser]

ALTER LOGIN [ApplicationUser]

If you think, hang on, the second script's just an extended version of the first one and then try to run just that, unfortunately that doesn't work and you'll see the message:

Msg 15128, Level 16, State 1, Line 2
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

Because the current passsword is se to require a change on first login, this blocks changing the policy associated with the login.

If you don't have another user you can use to run the above script (e.g. your DBA's gone home for the day and you need to un-block the app and worry about CHECK_POLICY and CHECK_EXPIRATION later) then you can use SQL Server Management Studio to login as your application user and you'll get prompted with:

Changing password for a user with MUST_CHANGE set via the SQL Server Management Studio user interface

Depending on the policy that's been set (SQL Server uses the password policy applied to the machine it's running on) you may even be able to get away with using the same password, which is great if you were explicit when requesting the password that was set, perhaps because it's specified in approximately 1,000,000 coinfiguration files for the application in question. Not that that ever happens, right?

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment