August 12, 2015 at 6:42 am
I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account. It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective. Several still advocate multiple accounts. I don't know if they are doing so because that's the way it's always been done or if there is still some compelling reason to do so. I don't want to create unnecessary accounts simply because something is "ideal." I'd like to understand the reasoning. Unfortunately I can't query books or msdn articles. What's everyone's take on this? Thanks,
August 12, 2015 at 8:33 am
RonKyle (8/12/2015)
I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account.
in an ideal world you would separate accounts, yes, although you could use the same account for database engine and agent if you wanted to.
RonKyle (8/12/2015)
It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective.
Ah, now there could be some confusion here,
a single domain account is required to run the sql server service for multiple instances when using a listener in an always on availability group, this is because the SPN for the listeners virtual networkname is bound to the user account running the service. If different instances use a different account the SPN binding for the VNN is lost.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2015 at 2:06 pm
Let's try this from another angle. I've reviewed your answer and done further reading.
With a SQL Server 2014 installation there is the option for the service accounts to:
1. Create a managed service account. This is created at the domain level and assigned to a single computer to run a service. The name has a $ sign at the end.
2. Create a virtual account. This is automatically managed by the computer on which it's created. Unlike traditional local accounts, it can access network resources within the computer's domain. By default if will be in the format of NT SERVICE\<SERVICENAME>.
Neither account requires password management.
Assuming I've understood this correctly, why wouldn't I opt for the virtual account? They don't require an account to be created at the domain level. Every service will have a different account and they will be able to access network resources. It seems the only limitation is that they can't access resources across domains. In my case, this is meaningless.
Is there another reason to choose a managed service account over a virtual account that I'm missing?
Thanks,
August 14, 2015 at 4:32 am
RonKyle (8/13/2015)
1. Create a managed service account. This is created at the domain level and assigned to a single computer to run a service. The name has a $ sign at the end.
These are fairly new in AD, they are fully supported for sql server. This type of account would be one option to ensure that all replicas use the same account for their service startup and ensure that SPN issues do not arise. This link details MSA's and virtual accounts in the context of SPN registration but nevertheless confirm suitability as follows;
MSDN-Register an SPN
When SQL Server is running on the Windows 7 or Windows Server 2008 R2 operating system, you can run SQL Server using a virtual account or a managed service account (MSA). Both virtual accounts and MSA’s can register an SPN. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.
RonKyle (8/13/2015)
2. Create a virtual account. This is automatically managed by the computer on which it's created. Unlike traditional local accounts, it can access network resources within the computer's domain. By default if will be in the format of NT SERVICE\<SERVICENAME>.Neither account requires password management.
Local accounts such as NT AUTHORITY\NetworkService and NT Authority\System can also access network resources, they act as the computer account on the network.
The virtual accounts are a new feature introduced in Windows 2008 on, they are designed to provide separation between the domain account running the sql server service and the user mapping inside the sql server instance (which is a sysadmin), there is no longer a direct relation so authentication as sysadmin cannot occur.
RonKyle (8/13/2015)
Assuming I've understood this correctly, why wouldn't I opt for the virtual account?
VAs allow registration of an SPN but will not provide authentication across instances such as in an AO group.
MSAs are a better option.
RonKyle (8/13/2015)
They don't require an account to be created at the domain level. Every service will have a different account and they will be able to access network resources. It seems the only limitation is that they can't access resources across domains. In my case, this is meaningless.Is there another reason to choose a managed service account over a virtual account that I'm missing?
Thanks,
Low privilege domain accounts are the recognised security standard for securing sql server and providing access to network wide resources, VAs are really only useful for localised systems.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 14, 2015 at 7:04 am
Thanks for this. I think I'm closing in for what I need. A couple of follow ups:
VAs allow registration of an SPN but will not provide authentication across instances such as in an AO group.
By instance I assume you mean communication across default and named instances. You're saying that it wouldn't be possible to pass information etc among the instances. What's an AO group?
VAs are really only useful for localised systems.
I have a single server running a single instances. It retrieves the data primarily through system level ODBC connections located on the server. Is this localized?
It might be helpful to know that I'm trying to understand this from the point of setting up an database server a data warehouse and its supporting databases. I'm not trying to set up one for a transaction server. The only connection the server will make is through the system ODBC connection. So while I understand the point that MSAs are better inasmuch as they can do more, and certainly anything a VSA can do. But if a VSA will do for my circumstance, I'd prefer to do the minimum.
August 14, 2015 at 7:44 am
AO group = AlwaysOn Availability Group.
August 14, 2015 at 7:51 am
Thanks for that clarification. So if I don't need the instance to act within an Always On group or cross domains, a virtual service account should be sufficient, right?
August 14, 2015 at 9:18 am
RonKyle (8/14/2015)
Thanks for that clarification. So if I don't need the instance to act within an Always On group or cross domains, a virtual service account should be sufficient, right?
correct, when using database mirroring or AlwaysOn groups the TCP endpoints need to have a valid user account for authentication, it sounds like the VAs will be just fine for what you're doing.
When I say localised I mean not venturing outside the host for any network related access or services
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2015 at 7:20 am
Just adding that I've set up two SQL Server 2014 accounts using the Virtual Service Accounts that go with each service. They have worked very well and I would highly recommend learning and using them if there is not some aspect in your environment that requires a domain account.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply