Database Administrators Asked by Stuart J Cuthbertson on January 4, 2021
In Microsoft’s SQL Server documentation on Windows service account configuration, the decision to use either a VA (virtual account) or a MSA (managed service account) hinges on whether
resources external to the SQL Server computer are needed
What exactly does this phrase mean here? I’m seeking an explanation that makes sense to an ‘accidental DBA’ with minimal experience of SQL Server, or Windows Server, configuration. What counts as a ‘resource’ here, and what kinds of ‘need’ are relevant?
Context: I have a fresh SQL Server 2019 VM on Azure, which was configured with VAs out-of-the-box, and I’m trying to decide whether we need to switch to using MSAs. I have found multiple other questions concerning this same documentation page and/or the same basic decision between VAs or MSAs (or regular AD Accounts) – but none really explain this specific phrase in a way that helps me apply it to my particular scenario. Which is essentially a data warehouse use-case: data will be coming into this SQL instance from external sources, but that’ll be managed by a third-party DW automation application running SSIS scripts, not directly by the SQL engine. (This application has its own AD service accounts.)
Whilst I’ve explained my specific scenario here to try to clarify the question, I’m keen for a generic answer so anyone with any SQL Server use case can evaluate this "resources external to the SQL Server" phrase for their needs. Specific answers for my use case are also welcome.
In the context of SQL Server, external resources are any service, computer, fileshare, etc that are not a part of the SQL Server installation and process.
For example, the INSERT BULK command needs access to a file location to load the data from. If this was on a different computer than that running SQL Server, then the SQL Server process somehow needs to have permissions to access that location.
A local account normally doesn't have access outside its own computer boundary and so wouldn't have access. You'd need to consider the machine account in that case. Whereas a MSA is still an AD account which can be granted permissions elsewhere (some config needed at the AD level as well though IIRC).
Correct answer by Martin Cairney on January 4, 2021
This documentation page gives an explanation for those curious enough to scroll down:
A Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service.
Virtual accounts [...] are managed local accounts. [...] Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.
Emphasis above is mine.
In other words, virtual accounts are local to one server and cannot control resources on other servers, while managed service account are domain-scoped and can, therefore, be given access to resources on multiple servers.
For a standalone SQL Server instance there is no difference in functionality. In a cluster, however, the account should have authority to access and/or modify various resources on more than one server, so a domain account would be appropriate.
Answered by mustaccio on January 4, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP