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

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]
WITH PASSWORD = '1234'
GO

ALTER LOGIN [ApplicationUser]
WITH PASSWORD = '1234',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF

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:

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?