Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Enabling "Enforce password expiration" on an account with an old password Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 11:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 730, Visits: 5,333
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
Post #1508197
Posted Wednesday, October 30, 2013 5:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 284, Visits: 864
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 :) If this is a genuine hack attempt your are just giving the person\application trying to hack into you more attempts.
Post #1509725
Posted Friday, November 1, 2013 11:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
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.
Post #1510725
Posted Friday, November 1, 2013 11:25 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 730, Visits: 5,333
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.
Post #1510727
Posted Friday, November 1, 2013 11:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
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."
Post #1510734
Posted Monday, November 4, 2013 7:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 284, Visits: 864
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.
Post #1511099
Posted Monday, November 4, 2013 7:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 5,430, Visits: 10,099
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
Post #1511108
Posted Tuesday, November 5, 2013 1:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 730, Visits: 5,333
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...
Post #1511612
Posted Tuesday, November 5, 2013 1:50 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
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.
Post #1511622
Posted Wednesday, November 6, 2013 8:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 387, Visits: 367
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
Post #1511900
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse