A new feature of SQL Server 2005 that has been fairly well publicized is the ability, on Windows 2003 operating systems, to enforce password stregth, expiration, and lockout policies on SQL Server logins, as the operating system enforces them on Windows logins. The way that this works is that SQL Server calls NetValidatePasswordPolicy, a Win32 function available on Windows 2003. So if I have a machine policy (either standalone or more likely inherited from a domain policy) that a password must be at least 8 characters long, the following DDL will fail:
CREATE LOGIN bob WITH PASSWORD = 'bob'
CREATE LOGIN bob WITH PASSWORD = 'bob000000'
However, did you realize that password on other secrets will follow policies as well? For example:
CREATE APPLICATION ROLE somerolename WITH PASSWORD = 'aaa'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'aaa'
CREATE CERTIFICATE foo WITH SUBJECT = 'foo', ENCRYPTION_PASSWORD = 'aaa'
CREATE SYMMETRIC KEY skey WITH ALGORITHM = DES ENCRYPTION BY PASSWORD = 'aaa'
will all fail for the same policy reasons. The lone straggler, at least as of Dec CTP is ASYMMETRIC KEY. This works…
CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'a'