February 25, 2014 at 10:00 am
I've read that it's best practice that every SQL Server service account on every server have its own domain account. No explanation as to WHY, however. This method in a DEV, TEST, PROD environment could result in a great number of accounts.
A comment in another thread that DEV and TEST services should have different accounts than PROD makes sense. But should each production server have its own domain account sets? I don't want to mindlessly follow a "best practice" and ask for a large number of new accounts without understanding why it's a best practice. If there is a good reason, however, I don't want to be responsible for a security hole or something similar that would have been prevented by following the best practice.
What are people doing in their shops? Thanks,
February 25, 2014 at 10:30 am
i can't say which is a best practice, but i'll throw out what strategy we use.
for startup services, we create a domain account mydomain\SQLMaster, and that is the user used across all sql servers for the startup account of the sql service, sql browser, and sql agent.
that user has access to some specific shares that are created for log files, ETL and other functions.
we have some similar users, mydomain\DWMaster, mydomain\SQLReporting and mydomain\RedGate, which are used for datawarehousing, reporting service account, and backups respectively.
having one account per server means you'd have to update multiple accounts if you wanted to add, say a new shared network location, so based on that alone, i'd say one account per server is not a best practice.
Lowell
February 25, 2014 at 11:40 am
I was a sysadmin on multiple operating system prior to starting my life as a DBA. The standard practice is to run each application or serivce under its own authoriy (login) to provide the minimum level of permissions required for each individual service. This was from the days of using one system for many applications, but it holds true in todays security requirements.
February 25, 2014 at 11:55 am
So just to be clear, you mean a single service is joined with a single domain account, but that combination is used across all servers. Right?
February 25, 2014 at 12:00 pm
No, every install of SQL should have its own service account. The only exception is failover clustering which shares services.
February 25, 2014 at 12:07 pm
This is partly helpful, as I've read this, but I would like to understand why. What's the risk if this isn't followed? Thanks,
February 25, 2014 at 12:15 pm
The risks vary depending on other settings and configs but some are:
1) If you lock out a login, you only effect a single instance
2) if a password is leaked, they only gain access to one instance
benefits:
1) if you need to grant an OS level permission, like a network share, you only give it to one instance
2) if you have an instance that is attempting to access a resource, you have better info since the login is unique
February 26, 2014 at 3:27 am
My 2 pennies worth... it depends.
Using a separate account for each SQL Service on each SQL instance gives maximum security and maximum maintenance overhead. Using the same account for all SQL services and instances gives minimum security and minimum maintenance overhead (until you HAVE to change the password then you get the worst migraine possible).
You need to talk to to your site's security people to get an understanding of the risks deemed acceptable by your management.
Many organisations will enforce separate accounts for Dev, Test, Prod, but not require separate accounts for each service or instance within each environment. Others will add to this the need for a separate account for web and non-web facing SQL instances.
If you have data that requires a high level of confidentiality (eg compliance with PCI or HIPAA) then for the affected servers the use of separate accounts for each service and instance is an important part of risk minimisation. If you did use a common account for data covered by PCI and you got hacked and the hacking was made easier because you had a common account, then do not be surprised if a court awards greater damages against your organisation because of their lax attitude to security.
With SQL2012 and above running on Windows 2012 and above then consider using Domain Managed Accounts if you need to use separate accounts for each service and instance. These offer enhanced security compared to normal domain accounts, and have the advantage they have no password to manage.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply