SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Account type for SQL server services


Account type for SQL server services

Author
Message
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10023 Visits: 4195
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,
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8435 Visits: 3281
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-700725
william-700725
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 190
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?
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10023 Visits: 4195
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
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4388 Visits: 2741
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search