Account type for SQL server services

Question

Account type for SQL server services

sqlfriend

SSC Guru

Points: 52781
More actions
October 9, 2013 at 2:52 pm

#281119

When you setup a service account for sql server services, do you use a domain account or MSA account or virtual account. Most of our SQl servers has SSIS, so it needs to access other resources on other SQL servers.
In this case what is the best option for the service, a domain account, msa or virtual account.
Currently we are using SQL 2008, we use domain account for the SQL server serivce.
http://technet.microsoft.com/en-us/library/ms143504.aspx
I am a little confused a bout MSA account, it says :
When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary.
it also says:
You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service.
So if it cannot login to a computer, how the SSIS to connect to another server using SQL agent service using a msa account?
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

The accounts you use for the services should not be a problem. You can specify credentials within the SSIS packages as part of the connection manager. Additionally, you can setup the sql agent job to use a proxy account which is separate to the service accounts used for SSIS and SQL Agent.

William Clardy SSChasing Mays Points: 627 More actions · Answer 2

sqlfriends (10/9/2013)
http://technet.microsoft.com/en-us/library/ms143504.aspx
I am a little confused a bout MSA account, it says :
When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary.
it also says:
You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service.
So if it cannot login to a computer, how the SSIS to connect to another server using SQL agent service using a msa account?
Thanks,

A properly managed service account will have permission to log in as a service but not permission to log in interactively on the console. In other words, it can be authenticated to execute on a server as a service, but a person cannot use those same credentials to RDP into one of your systems.

As to a service account's ability to connect to other servers, that is dependent upon the necessary permissions being granted -- e.g., did you create a SQL Server login with select permissions for DatabaseB.SchemaC.TableD running on ServerZ for the service account that the SQL Servere Agent process on ServerA is running under, or does it have the file permissions necessary to read the tab-delimited file on \\ServerN\ShareY? (At one job, the domain-level service account used to run SQL Server Agent got denied permission to even read the directory where backups were saved.)

Does that make sense?

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Thanks, yes, that makes sense.

So that means if I gives the MSA account the appropriate permissions to the databases on the other sevver, or file directory on the other server, it should be able to log in as a service to do the task for example SSIS package.

So I see the msa account is the same as a domain account. I don't see the difference.

When we create a domain account for SQL server services, we also register the SPN.

Is there a reason MSA account as the first choice other than a domain account?

Thanks

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Thank you for bringing this up - MSA's look to be very interesting once servers and the domain are at 2008R2 functional level or higher, and at least somewhat interesting even when the domain is at a lower functional level.

I've always used domain user accounts (not admin anywhere), and specifically granted permissions via gpedit.msc (Perform volume maintenance, for instance, to enable instant file initialization) and the certificate manager snapin (to allow Read to the SSL Certificate's private key when forcing encryption on).

From what I read, the MSA account is an easy way to have a lower-permissioned service account, which should reduce the secondary vulnerability of your network if, say, a SQL Injection attack succeeds.