Password policy for SQL Server 2005

Question

Looking for a good password policy stored procedure.

Ofcourse for my windows accounts windows admins alreadyhave one.

For my SQL Server instnaces I need one stored procedure which can send an email 15 days before that the passwords for schemas / sa accounts are getting expired.

Can I use some password generator tool which automatically changes my password for around 200 instances.

I am not worried about chaging the password for my schemas as it can impact the application usage b ut just for sa.

And i am looking for some easy way to do it.

Answer 1

Hi Namit, I haven't done it but I think this might be helpful for you. Where you can poll and check for password expiration.

http://sqlskills.com/blogs/bobb/post/How-DO-you-change-your-SQL-Login-password.aspx

http://sqlskills.com/BLOGS/BOBB/post/Changing-SQL-Login-password-with-the-utilities.aspx

Thanks

Answer 2

 created a password verify fuction in orale with the following considerations:-


• Password should not be same as the username
• Length of the password should be at least 8
• Password should not be SIMPLE
• Password should contain at least one letter, one digit and one special character.
• Password should differ from the previous password by at least 3 letters
• Password should expire in 90 days.
• Account should be locked out with 3 unsuccessful attempts.

Then I assigned this to a profile and alter sys / system users with this pofile.

I need some similar procedure for "sa" accounts in SQL Server.
I do not want to touch Domain accounts and schema logins.

The idea mentioend to use powershell to do this is really great but I need a cooked solution if someone already have implemented this policy for SQL server somewhere else?

Answer 3

Hi Namit,

If you check the enforce password policy and enforce password expiration, aren't they good enough ? They will make use of the OS local security policy.

You may push down a global policy to the 200 instances( assuming 1 instance = 1 machine) and standardize the password policy. 

Answer 4

check policy is ON for my SQL Server accounts and my local windows policy says 90 Days expire for my accounts.

If I unckeck password expiration for my SQL Server login and check the password policy, will my password expire?

Actually Iwant my password to be complex as my windows policy but I do not want my password to be expire.

I think that check_policy overrides password_expire and even I uncheck enforce expiration, it will still check my windows policy for expiration.

Answer 5

Hi,

If you check the enforce password policy and uncheck the enforce password expiration, your password will still follows the Windows password complexity standard but it will not expire.

In fact for all my application sql IDs, they have the enforce password policy checked and the enforce password expiration unchecked. The passwords indeed follow the Windows policy and they don't expire. For user sql IDs, both are preferred to be checked.