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'
you need:
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'
3 thoughts on “Password policies on object passwords”
Looking at the docs for SQL Server 2005 it says: "When password complexity policy is enforced, new passwords must meet the following guidelines."
Seems like a recipe for disaster starting up. From that snippet it almost sounds like SQL Server 2005 will AUTOMATICALLY enforce password complexity when the OS policy is turned on.
I’m all for password complexity functionality etc, but just because the OS requires password complexity may not necessarily mean that I want complexity on my SQL Server. Am I jumping to conclusions? (i.e. what I’d LOVE to see would be something like SET PASSWORD_COMPLEXITY INHERIT | ON | OFF (somehow expressed as ints) such that even if the OS didn’t require complexity you could have it in the SQL Server, or if the OS did require it you could override…. or just inherit what the OS/policy dictated).
How does one tell when a login will expire when the login is created with CHECK_EXPIRATION=ON in SQL Server 2005 on Windows 2003.
Say that the policy is set to 30 days. Where can I check the day when the logon will expire. Is there a place that keeps the expiration date or a place that keeps the password last changed date?
You could use the modify date in sys.sql_logins in conjunction with knowledge of your password policy to determine when your SQL Logins will expire. The problem with SQL Server keeping an expiration date is that the domain admin could change the policy for number of days.
Comments are closed.