January 16, 2015 at 10:32 am
We are in the discussion of enforcing password policy to our SQL accounts so that none of the accounts can have blank/easy password and follow our domain account policies. However, we don't want to inherit account polices where user has to renew the password after 90 days.
Question I have:
a. Can I achieve that by checking 'Enfore password policy' and unchecking 'Enforce password expiration'?
b. If we enforce password policies on all the accounts, what happens to exsisting local sql accounts that don't meet the password complexity criteria of network account policy? Do they get deactivated or can we keep them as is? We have quite a few of service accounts those have very simple password accounts and we are not ready yet to change their passwords (application dependecies).
Thanks in advance for the help!
-A
January 16, 2015 at 10:35 am
asc45 (1/16/2015)
We are in the discussion of enforcing password policy to our SQL accounts so that none of the accounts can have blank/easy password and follow our domain account policies. However, we don't want to inherit account polices where user has to renew the password after 90 days.Question I have:
a. Can I achieve that by checking 'Enfore password policy' and unchecking 'Enforce password expiration'?
b. If we enforce password policies on all the accounts, what happens to exsisting local sql accounts that don't meet the password complexity criteria of network account policy? Do they get deactivated or can we keep them as is? We have quite a few of service accounts those have very simple password accounts and we are not ready yet to change their passwords (application dependecies).
Thanks in advance for the help!
-A
The better answer is to not using sql authentication. Use windows authentication instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 16, 2015 at 10:49 am
That will be ideal. But for the interim, do you have any suggestions to the questions above?
-A
January 16, 2015 at 4:56 pm
I looked at this awhile ago, so this from memory. I don't have time to test, but I'd be sure if I were you, I put up a VM and tested this. Create dummy accounts with 1 day expiration and test.
b. IIRC, checking this box does nothing immediately. Only when you change the pwd does this matter.
a. see b, but I think this accomplishes things. However really the way to do this is to run a cracker on the accounts, and just change the accounts with bad pwds to require change at next login.
November 30, 2016 at 8:13 pm
Steve Jones so I have a question on your testing. When you checked the password policy on did you happen to do a restart of SQL Server or a gpupdate/force on the DB server to see if the windows complexity kicks in and comply's to that policy? I'm not sure just throwing an idea out there. I'm at a loss as there is no concrete documentation on if you check that option after the login is already created then it is just merely a login with a password that does not adhere to the windows policy. If you get audited and all they are looking for is that checkbox being enabled it shouldn't pass. I think the BPA tool in SQL 2012 can tell you all the logins that do not comply to the windows policy but I don't think they have a tool for SQL 2014/2016 yet.
December 1, 2016 at 5:39 am
@dpatel, Answer to A is yes, for question B the only time an account password is checked against the GOP policy is when the password is changed or you create an account and both occurrences have the "Enforce Password Policy" checked for the accounts.
You can use the following query to check to see if an account has the "Enforce Password Policy" applied to it;
Select name, is_policy_checked From sys.sql_logins.
I hope this helps.
Curtis
December 1, 2016 at 5:44 am
@dpatel, I forgot to add this part; you don't have to restart the SQL Server instance or reboot the server in order for this to take effect. The SQL Server Engine checks to see if the account has the is_policy_checked flag on, and if it is, it checks the current GPO on the system to see if the password meets it's criteria's
December 1, 2016 at 6:14 am
I can confirm that what you think will happen, happens.
Setting "Enforce password policy" will cause SQL to "inherit" password complexity settings from the OS (whether set there through local policy or through Domain Group Policy.) Not checking "Enforce password expiration" will keep those account passwords from expiring.
As for what happens to existing accounts with passwords that do not meet the complexity requirements, as others said, nothing will happen until you change the password, at which time the complexity requirements will be enforced.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply