January 12, 2015 at 12:34 pm
I'm working on cleaning up some bad choices made during SQL setup on a few boxes of mine, and one of the items on the list is getting the SQL Server service account out of the local Administrators group.
My first "victim" is a SQL 2008 R2 SP2 server, the service account is a domain account (which also may not be required,) and was added during the SQL Setup.
This is a virtual machine, so I can snapshot the VM to give myself a "bailout" button, as well as being a QA box.
So, I see one of two ways to do this:
1. Just remove the account from the local Admin group, restart SQL and make sure it comes up OK
2. Switch SQL to something like LocalService, remove the current account from local Admins, then re-add it as the service account.
Obviously, any changes to the service account will be done in the SQL Configuration Manager.
Right track, wrong track?
Which of the two options seems less likely to cause headaches for me? I'm leaning towards #1 should work OK, and worst case if it fails I try #2.
Thanks all,
Jason
January 13, 2015 at 7:17 am
jasona.work (1/12/2015)
I'm working on cleaning up some bad choices made during SQL setup on a few boxes of mine, and one of the items on the list is getting the SQL Server service account out of the local Administrators group.My first "victim" is a SQL 2008 R2 SP2 server, the service account is a domain account (which also may not be required,) and was added during the SQL Setup.
This is a virtual machine, so I can snapshot the VM to give myself a "bailout" button, as well as being a QA box.
So, I see one of two ways to do this:
1. Just remove the account from the local Admin group, restart SQL and make sure it comes up OK
2. Switch SQL to something like LocalService, remove the current account from local Admins, then re-add it as the service account.
Obviously, any changes to the service account will be done in the SQL Configuration Manager.
Right track, wrong track?
Which of the two options seems less likely to cause headaches for me? I'm leaning towards #1 should work OK, and worst case if it fails I try #2.
Thanks all,
Jason
Sorry. Is all broke. Try again later. @=)
EDIT: Sorry. I misread your original post. I thought you were going to leave it off the domain account under Local Service. Actually I think choice 2 is the better of both options, but I think regardless something may break. Let me poke around a bit and see what our local server admin would do in a situation like this.
Actually, before you remove all those permissions... Are you sure the Service Account doesn't need access to things it can only get from being part of the Local Admin group?
And when you say the account is domain account, it absolutely should be a plain vanilla domain account (not an Administrator domain account, but still a domain account) if it is accessing resources beyond the local box.
What is the box doing that you feel confident in cutting if off from the rest of the world?
January 13, 2015 at 8:58 am
Jason,
Is this production or a non-prod environment?
January 13, 2015 at 9:03 am
Brandie Tarvin (1/13/2015)
Jason,Is this production or a non-prod environment?
The server in question is a QA, so if it drops off the network it's not going to cause a crisis.
Plus, it's a VM, so I'm going to snapshot it to give me a rollback method to exactly where it was when I started.
January 13, 2015 at 9:05 am
Excellent.
Apparently option 1 is the option of choice. My only worry was that if you were in production, it might stop stuff in the middle of processing.
Does that help?
January 13, 2015 at 9:55 am
jasona.work (1/12/2015)
I'm working on cleaning up some bad choices made during SQL setup on a few boxes of mine, and one of the items on the list is getting the SQL Server service account out of the local Administrators group.My first "victim" is a SQL 2008 R2 SP2 server, the service account is a domain account (which also may not be required,) and was added during the SQL Setup.
This is a virtual machine, so I can snapshot the VM to give myself a "bailout" button, as well as being a QA box.
the service account should use a low privilege domain user account and doesnt need to be a member of the local admins group.
jasona.work (1/12/2015)
So, I see one of two ways to do this:1. Just remove the account from the local Admin group, restart SQL and make sure it comes up OK
2. Switch SQL to something like LocalService, remove the current account from local Admins, then re-add it as the service account.
Obviously, any changes to the service account will be done in the SQL Configuration Manager.
Right track, wrong track?
Which of the two options seems less likely to cause headaches for me? I'm leaning towards #1 should work OK, and worst case if it fails I try #2.
Thanks all,
Jason
Just remove the account from the local admins, all permissions like run as a service, etc are granted at install time.
You might just check first whether any of the SQL server directories have been secured for administrator access only, if so give the SQL instance account access to these locations. If necessary give the agent account access too.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 6, 2015 at 1:03 pm
So, to follow up on this with what worked for anyone else who runs into something similar:
I went with option 2, and had no problems across the board. Switched the service account to Local Service, let it restart, removed the AD account from local Administrators, switched the service account back to the AD account and let the service restart again.
OK, actually I removed the account from Local Administrator before switching the service to Local Service, but that wouldn't have taken effect anyways until I restarted the service, so it all evened out.
Thanks again for the suggestions / concerns!
February 9, 2015 at 4:38 am
Glad it worked without breaking anything.
February 13, 2015 at 4:18 am
If you have any SQL 2012 or above instances running on Windows 2012 or above then you really should look at using Group Managed Service Accounts, at https://technet.microsoft.com/en-gb/library/hh831782.aspx
gMSAs have the normal MSA advantage of not needing any password management, but can also be used for clusters and on multiple servers.
It has long been best practice that each SQL Service on each server should have its own service account, in order to tailor privileges to each service, to stop contagion if a password gets misused, and to simplify the process of password change. IMHO gMSAs change this for a lot of organisations. There are no knowable passwords to be misused and Active Directory deals with password change automatically. If you have high security needs such as HIPAA or PCI you still need separate accounts for everything, but organisations with lower security needs can much more safely consider one account per service to be used on all instances. Even the low-security approach of one account for all services everywhere has got a lot safer if the account is a gMSA.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 13, 2015 at 4:54 am
EdVassie (2/13/2015)
If you have any SQL 2012 or above instances running on Windows 2012 or above then you really should look at using Group Managed Service Accounts, at https://technet.microsoft.com/en-gb/library/hh831782.aspxgMSAs have the normal MSA advantage of not needing any password management, but can also be used for clusters and on multiple servers.
It has long been best practice that each SQL Service on each server should have its own service account, in order to tailor privileges to each service, to stop contagion if a password gets misused, and to simplify the process of password change. IMHO gMSAs change this for a lot of organisations. There are no knowable passwords to be misused and Active Directory deals with password change automatically. If you have high security needs such as HIPAA or PCI you still need separate accounts for everything, but organisations with lower security needs can much more safely consider one account per service to be used on all instances. Even the low-security approach of one account for all services everywhere has got a lot safer if the account is a gMSA.
Interesting article, but failover clusters don't support gMSAs, so the tool won't work for everyone.
And here I was getting all excited about using this for our new deployment... @=(
February 13, 2015 at 5:26 am
EdVassie (2/13/2015)
If you have any SQL 2012 or above instances running on Windows 2012 or above then you really should look at using Group Managed Service Accounts, at https://technet.microsoft.com/en-gb/library/hh831782.aspxgMSAs have the normal MSA advantage of not needing any password management, but can also be used for clusters and on multiple servers.
It has long been best practice that each SQL Service on each server should have its own service account, in order to tailor privileges to each service, to stop contagion if a password gets misused, and to simplify the process of password change. IMHO gMSAs change this for a lot of organisations. There are no knowable passwords to be misused and Active Directory deals with password change automatically. If you have high security needs such as HIPAA or PCI you still need separate accounts for everything, but organisations with lower security needs can much more safely consider one account per service to be used on all instances. Even the low-security approach of one account for all services everywhere has got a lot safer if the account is a gMSA.
Nice idea and I appreciate the pointer, but we're just now standing up our first SQL2012 servers, and because one of our security tools doesn't work with Server 2012, any deployments of that have been put on hold...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply