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, I have the following password policy on my local machine:

(You can get to this MMC dialog from Administrative Tools | Local Security Policy.)

So, when I try to create the following SQL Server account with a password < 7 characters:

CREATE LOGIN foo WITH PASSWORD = 'bar'

I get the following error message:

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

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.

Kent Tegels has some further information about this change.