Account type for SQL server services

  • 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,

  • 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.

  • 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?

  • 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

  • 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.

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

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