September 8, 2017 at 5:25 am
Hi
I am looking into changing the AD account passwords running SQL services for all SQL server installations in our sqlserver estate, 100's. A monumental task given the amount of servers and different windows AD accounts running those SQL services.
Firstly I am wondering is there any quick way to do this? i.e wintel team change the password and it is auto changed in SQL configuration manager and anywhere else it is used by sqlserver or cluster services etc?
And secondly on AD level is it possible to limit these accounts so that they can be deemed non-interactive...ie not accounts where people can login to the database and therefore can be excluded from a password change.
Thanks in Advance
September 8, 2017 at 6:54 am
caz100 - Friday, September 8, 2017 5:25 AMHi
I am looking into changing the AD account passwords running SQL services for all SQL server installations in our sqlserver estate, 100's. A monumental task given the amount of servers and different windows AD accounts running those SQL services.Firstly I am wondering is there any quick way to do this? i.e wintel team change the password and it is auto changed in SQL configuration manager and anywhere else it is used by sqlserver or cluster services etc?
And secondly on AD level is it possible to limit these accounts so that they can be deemed non-interactive...ie not accounts where people can login to the database and therefore can be excluded from a password change.
Thanks in Advance
It's a system admin question I suppose. Didn't hear about any possibility of auto update on the SQL server services from the domain controller. Not sure if there's rationale as it becomes a vulnerable DB from security perspective.
For the second query , the passwords can be set to no-expiry mode .
September 8, 2017 at 4:25 pm
If the audit cannot include this in their exception list (which they did in my case) then you've got to change the SLA ..I still don't think there is any such thing like auto propagation of the passwords to the binary locations of the service.. but best to take more opinions from null more knowledgeable people on the forum.. Your idea of thinking out-of-the -box is good though!
September 10, 2017 at 3:14 am
If the instances are at least SQL Server 2012 or newer and the SQL Server instances are not clustered or part of an AG you can look into using default virtual accounts. The local computer handles the password changes. If they are (I believe) SQL Server 2012 or newer you can look into stand-alone managed service accounts. These are AD accounts but the computer object controls the password and updates in AD automatically and takes care of the update in SQL too. If you are running AGs or an FCI and need domain accounts and running SQL Server 2014 or higher you can look into group managed service accounts. These allow a group of computers to use a single domain-based account, and again the password is managed automatically.
Configure Windows Service Accounts and Permissions
Managed Service Accounts (MSA) and SQL 2012: Practical Tips
Group Managed Service Accounts (gMSA) and SQL Server 2016
Joie Andrew
"Since 1982"
September 10, 2017 at 7:36 pm
caz100 - Friday, September 8, 2017 6:58 AM@arsh-2 - Thanks but for the second question passwords are already set to never expire. The issue is for some crazy reason auditors want them changed...every account password.
I hate the ignorance of auditors sometimes.
Let's look at passwords. In the ASCII table for the normal things (letters, digits, symbols) with an ASCII value < 128 (In other words, the normal things you would enter by keyboard), there are 94 characters that can be used in a password (excludes all control characters, spaces, and the DEL character).
If you use as little as a 13 character random password, you get a total number of possible passwords (even if I tell them I have a 13 character password!) of (94^13)-(94^12)-1 or 44,260,589,277,725,564,012,187,647 possible passwords. If a brute force password attempt takes place and it only has to go through 10% of all possibilities to guess the password, that's still 4,426,058,927,772,556,401,218,764 possible passwords. At a rate of, say, 1 million passwords per second, that would be 4,426,058,927,772,556,401 seconds to make a guess on 10%. That's 51,227,533,886,256 days or 140,253,343,973 YEARS to brute force guess a 13 character random password in just 10% of the total guesses. If I were to reject any password with a letter that starts at less than ASCII Character 80 (Capital "P"), that would ensure that a brute force attack that "starts at the beginning" would be guaranteed to take at least 70,126,671,986 years. If someone put a million computers on the task, it would still take 70,126 YEARS for them to get lucky at a 10% guess. Of course, like any password, they could get extremely lucky and hit it on the first guess whether I've just changed it or not.
If you limit the guesses to 3 before the account locks out, that limits the attacker to just 2 guesses before someone else has to log in correctly to reset the guess counter. That's not going to happen at the rate of a million guesses per second.
So my dear Auditor "friend", tell me again why in the hell you want me to change my 13 character, randomly generated passwords every 3 bloody months?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2017 at 2:18 am
@Joie Andrew - that's a good option for sqlserver 2012 or sqlserver 2014. We have hundreds of instances on 2008, 2005 and some 200 too, a real mixed bag.
@Jeff Moden - excellent point, but will the auditors listen to reason...
September 11, 2017 at 7:38 am
caz100 - Monday, September 11, 2017 2:18 AM@Jeff Moden - excellent point, but will the auditors listen to reason...
Dunno... they have some really silly list of supposed "Best Practices" they follow but can't answer any questions about them so, probably not because they don't know what they're talking about to begin with. 😉 They just blindly "follow the rules". After all, if a million people are doing it wrong, it's gotta be right, right? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2017 at 8:18 am
You need to either convince the auditors that these don't need to be changed (good luck) or change them. We used to have a script that would alter passwords on machines and in AD. You don't need to change config manager, just the service password. Config manage is needed for permission changes, but you aren't do that here.
We used VBScript (long list of servers, some randomized passwords, change in AD then in service) , but today I'd use PoSh. This might help: http://windowsitpro.com/powershell/changing-service-credentials-using-powershell
September 11, 2017 at 8:32 am
@steve-2 Jones - it is the changing of the password in config manager that is going to be the issue, the windows server admins should be able to script the password change of the AD accounts BUT on a sqlserver side changing of the password in config manager + restart services to test - we are talking about hundreds of different AD accounts running different types of sql installations, different versions etc.
September 11, 2017 at 8:38 am
The Config Manager is nothing magic. You don't need to use this to set a SQL Server service account. Config manager sets
- the service properties as you would in the Services applet
- The ACL permissions for folders.
If you are changing a service account password, you don't need the second. The account retains ACLs and other permissions. you're just changing the password. You can do this programmatically.
If you are changing accounts, meaning a new domain account, you can still do this, but you need to do file/folder permissions and potentially local/AD group changes as well.
September 11, 2017 at 8:39 am
Sorry, I think I left something out.
You need to programmatically:
1. change the pwd in AD
2. change the pwd for the local service(s)
Both need to happen. Config manager is not needed here.
September 11, 2017 at 9:13 am
@ Ok thanks Steve....so if through windows programming we change the password in AD and then in all local services, we don't need to touch configuration manager? correct?
September 11, 2017 at 12:17 pm
That's correct. Test for your situation, but you should be able to change the pwd in AD, change the pwd in Services applet (or both programmatically) and then restart SQL without an issue.
I'll try to test in a bit.
September 11, 2017 at 12:46 pm
Which is why things like virtual accounts and managed service accounts are great. You set them up and let the computer accounts manage the passwords.
Joie Andrew
"Since 1982"
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply