Service account setup for SQL Services

Question

Service account setup for SQL Services

TangoVictor

SSChampion

Points: 10969
More actions
June 3, 2016 at 4:32 pm

#316903

Mixed environment 2008R2, 2012, 2014 all running on VMWare hypervisor. No clustering or HA... yet
Currently each instance runs under its own unique service accounts (AD accounts).
We had a consultant in and he recommended setting each service across the organization to the same credentials. So one AD account for all MSSQL services, one for all Agent services, SSIS, SSAS, etc...
This seems to be a potential security risk, although the AD accounts we use have no permissions other than read/write to the backup location and nothing on the windows server other than the built-in local SQL groups.
just looking for thoughts on making this change and whether benefit/risk is justified.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 1

if you assign the single service account for all your SQL service account. it will be an single point of failure if you have any password policy or account locked out then your all your sql service will be down

if you have separate policy for your production,Dev,Test and DR then you have Separate account for each o your environment.

if you have different account for each service will have easy identification/manageable.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

I agree. It's not only a security risk (think payload from each server), it would also prevent you from customizing what each server can see based on it's functionality. I usually use 1 AD account as a service account for each server. Sometimes, I use 2... one for the SQL Service and 1 for the SQL Agent Service.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 3

Hi Tom.

You are free to implement a solution that best fits your circumstances but one account for all instances is not one that I would recommend.

Microsoft's recommendation for standalone Instances to to use the virtual "NT SERVICE" accounts - these are the defaults when installing SQL Server. These are no access accounts with no passwords so score highly for good security.

Permissions are assigned at install time or through Configuration Manager. You should only need to manually apply permissions if SQL Server uses additional drives or directories.

If you are looking to backup to a remove file share, assign permissions to the server account that is running the instance.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

It's been pointed out this is a bad idea. Some companies do this, even large ones, to make AD admin easier.

However, this is an issue for me. If I need to change this password because an admin quits, I've got to update this on a lot of machines. On the flip side, I can change the password in AD once and no former admin can connect. This is in contrast to worrying about changing the password on multiple accounts

IDeally, you have strong, unique passwords that are not stored or written down for each service. If you have an issue, you can change one password for one service to test things.

Of course, if you have an issue and every system is set to the same password, you can use a new account for one service to test.

It's a bit of a tradeoff. However, I'd say that as Jeff pointed out, this is a security risk. If someone were to crack this password, and remember, it's possible, then they have access to every SQL Server. Very low probability but not worth the chance for me.

TangoVictor SSChampion Points: 10969 More actions · Answer 5

Thanks for the thoughts on this. I was in same boat regarding security, just wanted some feedback from the community.

There are no compelling reasons to change this so we'll keep it as separate accounts.

EdVassie SSC Guru Points: 60445 More actions · Answer 6

Are you able to post the consultant's reasons for wanting to make this change. It would be interesting to see what reasons are given, and what the risk/benefit analysis shows.

Whenever I have looked at this issue, I have not seen a situation where any potential benefits of using a single password-protected account outweigh the risks of a) propagation of unwanted access, and b) potential unwanted downtime if password change is not synchronised across multiple servers (gMSA accounts do alter this situation a bit).

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

TangoVictor SSChampion Points: 10969 More actions · Answer 7

The main reason was security for powershell across multiple servers from the central management server. The powershell was intended to do health/performance monitoring. I'd need to verify this as this discussion was a while back and had it on a list of items. The reason i ask is to check on whether this is something others do that may be considered standard or even best practice. The security implications seemed pretty clear to me and that was confirmed by the comments.