An error about password policy has been causing some confusion among users. Most are trying to find an option within SQL Server itself to manage password policies, when in fact this is coming from Windows. When you create a SQL Server login, the server will validate the password against the password policy of the local machine.

For example, the following password policy is active on a machine:

***You may view/modify your machine’s current local security policy by going to Administrative Tools -> Local Security Policy.***

If you try to create the following SQL Server account with a password less than 7 characters:

CREATE LOGIN foo WITH PASSWORD = ‘bar’

The following error message will be generated:

Msg 15116, Level 16, State 1, Line 1
Password validation failed. The password does not meet policy requirements because it is too short.

You will see similar errors for different violations, such as:

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet policy requirements because it is not complex enough.

Three possible workarounds are:

• change the local/domain password policy;
  
• use a password that meets the password policy requirements; or,
  
• use the CHECK_POLICY option to disable policy validation:
  
  CREATE LOGIN foo WITH PASSWORD = ‘bar’,
         CHECK_POLICY = OFF

You might also want to look at the CHECK_EXPIRATION option, which will help prevent your accounts from expiring without warning you.