SQL Server 2005 Standard Logins and Local Security Policies
Suppose you've got the following on a computer you are working with...:
- Any edition of SQL Server 2005 installed with mixed authentication mode support enabled
- A local and/or group policy that applies that computer controlling login attributes like when an account expires, minimum password strength, length and so.
- You've created one or more standard logins when such policies were being enforced by that computer.
There's a subtle and sometimes overlooked change with SQL Server 2005 that applies to both Standard and Windows Integrated logins. It's pretty easy to understand why such policies would affect a Windows Integrated login, but its not so obvious that the same policies would be applied to a standard login. However, surprise, they are!
That's a feature, actually, since it improves the security of the system. But it can also be a real problem since there's no notification provide that a login is about to or has gone stale. While its a best practice not to circumvent this behavior, it is possible. The easy way to tell SQL Server to ignore the applicable policies when you generate the login. There's two options in the CREATE LOGIN statement that help you do this:
- CHECK_EXPIRATION, which does what the name implies
- CHECK_POLICY, which verifies that the given password does, in fact, comply with the applicable length and strength policies.
Here's an example of creating a login that overrides the password expiration policy, but does enforce any applicable length and strength policies.
CREATE LOGIN MyAppLogin WITH PASSWORD='Something4U2Try2Guess', DEFAULT_DATABASE = Orphange, CHECK_EXPIRATION = OFF, CHECK_POLICY = ON