5

Besides the Database Engine, SQL Server has some additional Services, namely:

  • SQL Server Integration Services
  • SQL Server Analysis Services
  • SQL Server Reporting Services
  • SQL Server Agent

Should each one of them have separate Windows accounts?

If so, what are the benefits of using this more finegrained account setup? Is it practical?

If not, what would be a good, recommended account setup for the SQL Server and its additional services?

7

Typically they aren't run under a different account, but all under the same Windows account. This makes the management much simpler as there is only one account's password to change. The downside to this is that if the password needs to be changed for the account all the servers need to be restarted at once.

As for permissions, just set the SQL account as a normal domain user, and let the SQL installer modify the rights on the machine that are needed. It will give the account the rights that it needs to run SQL.

The benefit to having one account per service is that you get a more fine grained control of what objects on the domain each account gets rights to. While more secure in larger environments it often isn't practical to control things at this level as you'll quickly end up with hundreds or thousands of accounts just for running SQL Services.

3

The different accounts per instance are suggested more for security reasons and maintenance. If an operator needs to run SQL agent jobs but should not have access to other parts of the SQL server, then separating the accounts will help.

Also if one of the accounts get locked out, it will not lock out the other services that are under the other accounts. This comes more into play during clustering.

All being said, I use one account for the services as it is easier to maintain. Plus it is the DBA (me) who has to look into the matter if any issues arise. There are also granular permissions available to granting users rights to SQL agent jobs.

1
  • 3
    +1 but I will (and have, and always will) argue that administrative burden is irrelevant if your company is breached. No one will want to hear that you made the choice to reduce administrative overhead if your customers' data is compromised. If you do everything else right in your business and don't do security right, you will lose your customers, and have no business to support or administrative burden to contemplate. Security is priority #1. Mar 19 '12 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.