Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Account type for SQL server services Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 2:52 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,754, Visits: 3,161
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,
Post #1503310
Posted Wednesday, October 9, 2013 10:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:26 PM
Points: 2,926, Visits: 2,529
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.


Post #1503365
Posted Friday, October 11, 2013 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:21 AM
Points: 26, Visits: 117
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?
Post #1504063
Posted Friday, October 11, 2013 10:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,754, Visits: 3,161
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

Post #1504079
Posted Friday, October 11, 2013 11:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 861, Visits: 2,360
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.
Post #1504111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse