April 5, 2011 at 3:13 am
Looking for a good password policy stored procedure.
Ofcourse for my windows accounts, windows admins already have 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 changing 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.
April 6, 2011 at 1:27 pm
There isn't a tool to do this. A query you could probably tie to Database Mail would be based on:
SELECT [name], LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'Password_Date'
FROM sys.sql_logins
ORDER BY Password_Date ASC;
As to the second part, a stored procedure would have to make Linked Server connections all over the place. Not exactly ideal. Your best bet here is to write a quick PowerShell script which randomly generates the password, reads a file that has all the instances, and then goes and resets the sa password on these instances.
K. Brian Kelley
@kbriankelley
April 7, 2011 at 2:14 am
I 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?
April 7, 2011 at 2:31 am
You cannot modify SQL Server's set password ability without resorting to an unsupported system. Also realize that as of SQL Server 2005, if you use password policy enforcement, SQL Server will use the password policy set at the domain level. This is set in the default domain policy GPO and is applied to all systems in the domain. This overrides whatever is set locally on the Windows system.
SQL Server actually looks at the Windows system's policy and that's what it uses for password policy enforcement. This includes lockout policy, password expiration, # of previous passwords remembered, minimum password length, and password complexity. You can choose to enforce password policy but not enforce expiration, this is akin to marking an account with a never expiring password on the computer or in Active Directory.
If you're not sure how the password policy works at the domain level, you should talk to one of your Active Directory administrators. Once you understand how it works at the domain level, you'll understand how SQL Server can apply it, too. You can always choose to override the password policy for a SQL Server based login, but this should generally not be done unless you have no other choice (such as with 3rd party products with hardcoded passwords for their logins).
K. Brian Kelley
@kbriankelley
April 7, 2011 at 2:50 am
Thanks for your reply.
Sorry I am bit confused here.
I am not looking for domain accounts which are my logins in SQL Server.
I am looking for changing the password for sa user and if required SQL logins (not windows accounts).
April 7, 2011 at 7:30 am
I understand.
SQL Server 2005 introduced the capability to take account policies from the local Windows machines. These are applied to SQL Server based logins like sa.
So the things we enforce for Windows based user accounts can be enforced, with the exact same rules, can be enforced with SQL logins:
- Password min length
- Password expiration
- Number of passwords remembered
- Password complexity
- Number of account login failures before lockout
- How long to track lockouts before resetting
- How long to lockout
If you look in Books Online for CREATE LOGIN, you should see the information about password policy enforcement.
K. Brian Kelley
@kbriankelley
April 7, 2011 at 7:49 am
Many Thanks for your reply.
I saw an article :-
http://sqlserver-qa.net/blogs/perftune/archive/2008/04/30/4133.aspx
Is it possible to use my windows AD policy for my SQL server accounts like "sa"?
And how can I get notification when my password is getting expired?
April 7, 2011 at 8:54 am
sehgalnamit (4/7/2011)
Is it possible to use my windows AD policy for my SQL server accounts like "sa"?
Yes. See here Books Online: Password Policy
And how can I get notification when my password is getting expired?
As I said before, only if you write something custom yourself. To put things in perspective, you only get notified about a password expiration in Windows when you log on. Same sort of thing with SQL Server. If you get an email notification in your environment now, it's because a 3rd party product is doing it.
K. Brian Kelley
@kbriankelley
May 15, 2011 at 3:38 am
check policy is ON for my SQL Server accoutmscheck 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?
May 15, 2011 at 4:11 am
If you uncheck password expiration, no they won't expire. This is the same as in Windows where you check "Password never expires" for a Windows user account. The rest of the password policy will still be enforced, such as password complexity and user account lockout in the case of too many bad login attempts.
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply