SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enabling "Enforce password expiration" on an account with an old password


Enabling "Enforce password expiration" on an account with an old password

Author
Message
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 11749
It's required at my employer to have password policy and expiration enforced for all logins. Well, turns out there's a *LOT* of SQL Logins that don't have password expiration checked.

This could come back to bite me.

So, my question, to which I suspect I know the answer is:
If I go through and check this, when the account in question tries to login to the SQL server the next time, the user will be informed that their password has expired and must be changed, correct?

I should clarify, though, that the SQL Logins are used by applications, not actual users, so potentially no one will actually see a "password expired" message, only that their app isn't working...

IF those accounts will get locked out at the next login (likely due to either the SQL being restarted from OS updates, or the app server for the same reason,) I know there's a way to unlock the account without actually changing the password that I could use...

Thanks,
Jason
SQLAssAS
SQLAssAS
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 1222
easy way to unlock an account without actually knowing the password is to

1) be alerted the the account is locked
2) Take password policy off
3) put password policy on

This does the trick. But be careful with this Smile If this is a genuine hack attempt your are just giving the person\application trying to hack into you more attempts.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1941 Visits: 2729
Be aware that SQL Server has no mechanism that I'm aware of that can guarantee that existing passwords actually comply with the password policy.

Any password may be entered when the password policy is off, and when you turn the password policy on, SQL Server does not (and cannot thoroughly, due to the hashing of passwords, albeit in a very primitive way) validate whether or not the prior password complied with the policy.
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 11749
Steve & Nadrek,

I appreciate the information, but it still hasn't answered my question:
If I enable "check password expiration", when the account in question tries to login to the SQL server the next time, the user will be informed that their password has expired and must be changed, correct?

Again, I do appreciate the responses.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1941 Visits: 2729
I've never tried password expiration within SQL (we use AD accounts when we need to expire passwords), but technet has http://technet.microsoft.com/en-us/library/ms161959.aspx

"Password expiration policies are used to manage the lifespan of a password. When SQL Server enforces password expiration policy, users are reminded to change old passwords, and accounts that have expired passwords are disabled."
SQLAssAS
SQLAssAS
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 1222
jasona.work (11/1/2013)
Steve & Nadrek,

I appreciate the information, but it still hasn't answered my question:
If I enable "check password expiration", when the account in question tries to login to the SQL server the next time, the user will be informed that their password has expired and must be changed, correct?

Again, I do appreciate the responses.


Yes you are right. If you don't know where the password would need to be changed, I would never put expiration on. For SQL accounts used as service accounts for applications, I personally would never expire the password. It is inevitable that one day you will get disruption to the service, whether that is in 1 day or 1 month.

What is the reason that you need to make the password expire? Just local policy? If it is some security person telling you to do so, explain the situation above, service disruption will occur.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14502 Visits: 15980
Sounds like your employer is putting the cart before the horse. If your applications don't have the facility to reset expired passwords then things are going to start breaking when passwords expire. Explain that, for this to work, they need to rewrite those applications. As soon as they realise how expensive that is, they'll allow exceptions so that it becomes workable. For example, how about a policy that only the sa password (which nobody uses to connect, do they?) expires?

John
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 11749
Steve, John, just noticed your replies.

Steve, the good news is, the service accounts for SQL Server / Agent are controlled at the domain level, and do not expire.

As for the SQL Logins, my employer treats the DOD STIG documents as bibles. The STIG for SQL Server mandates expiring passwords, therefore passwords *must* expire. Sucks, really. Worse, the Oracle passwords are controlled by Oracle, so there's a profile set up to only expire the passwords every 365, but with SQL in a domain, the domain policies apply. Which means every 60 days...

So far, I've not enabled the check password expiration, both because I'm in the midst of migrating DBs to a new server, and because I don't want a sudden mad swarm of angry users...
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1941 Visits: 2729
If you're actually going to consistently follow one pattern (i.e. the domain 60 day password expiration), then I'd suggest mandating a simple SQL Server login policy. For instance:

All SQL passwords change every 45 days (i.e. just over 2 weeks before they expire, in case of delays from vacations or whatnot).

Abandon all efforts towards detection/notification of expired passwords - that's entirely the app's problem, and if you change passwords every 45 days, they'll never hit the 60 day expiration!

Then automate the heck out of it. You can get (or have coded) a cryptographic pseudorandom number generator to generate passwords as part of ALTER LOGIN statements, then a script using sqlcmd to load them into the database, and some way of securely passing them out to the people who need them (this is likely to be the hardest and most painful part) for the applications.

Note also that when you really get into the automation, the length no longer matters - a program comes up with them, a program changes them in SQL, a program changes them in the apps - no human even needs to know them. If you have a problem with one, tell the program to regenerate that one (instead of all of them) and update everything!

Alternately, work with the app developers so that the apps have some sort of encrypted password/connection string stored, and the automation that gives SQL Server the new passwords also automatically updates the app passwords.
Noetic DBA
Noetic DBA
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 420
Jason,

I am not sure if you got a complete answer yet, but I had the same issue last year. The auditors required that the SQL logins have an expiration for a custom application. The application was using SQL logins. I went into Test and checked Enforce Password Policy and Enforce Password Expiration. This caused the SQL logins to adhere the the domain password policies, including expiration and complexity. If you want to check what your policy is, just hit Start and type secpol.msc and check the local policies.

Long story short, SQL did throw an error for those users' passwords that were beyond the expiration date. We were able to capture that error in the application and force a password change when they logged in the first time.

The best thing is probably single sign on, but that was not an option for us and it sounds like it may not be an option for you. Either way you should be aware that turning that on will give you problems.

CHECK_POLICY = { ON | OFF }
Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
http://msdn.microsoft.com/en-us/library/ms189828.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search