October 24, 2013 at 11:39 am
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
October 30, 2013 at 5:31 am
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.
November 1, 2013 at 11:16 am
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.
November 1, 2013 at 11:25 am
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.
November 1, 2013 at 11:47 am
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."
November 4, 2013 at 7:21 am
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.
November 4, 2013 at 7:33 am
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
November 5, 2013 at 1:19 pm
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...
November 5, 2013 at 1:50 pm
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.
November 6, 2013 at 8:11 am
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.
November 6, 2013 at 8:19 am
Noetic,
That's what I was afraid would happen. I'll have to talk this over with my boss, see how he wants to tackle it. It was like this when I started, despite the requirement, so maybe someone either was gaming the system (auditor is coming! Enable it!) or they got a "pass" on having it enabled.
August 8, 2014 at 7:16 am
Well you can alter all the current users by running below code...
USE master
SELECT 'ALTER LOGIN ' + '[' + name + ']'+ ' WITH
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON; 'FROM sys.sql_logins
WHERE is_policy_checked = 0
or is_expiration_checked = 0
Execute your results and you should be fine!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply