Changing Service Account Model

Question

Changing Service Account Model

illini1223

SSC Veteran

Points: 211
More actions
January 26, 2015 at 10:00 am

#296937

Hello,
I am currently running a clustered instance (2 nodes running 3 instances). The current service account for these instanced is a single Active Directory (AD) user who is in the Administrators group on the Windows node machines.
I would like to change the security model to use one AD user per service that are not Adiministrators on the server. For example, I want to run the SQL engine to a different AD account that is NOT an administrator. Something like MyDomain\SQLEngine.
How do I assign permissions to MyDomain\SQLEngine. I believe I need the permissions I need to assign for the SQL Engine are:
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to start SQL Server Active Directory Helper
Permission to start SQL Writer
Permission to read the Event Log service
Permission to read the Remote Procedure Call service
Here are more specifics
Operating System: Windows 2008R2 Enterprise with sp1
SQL Version: 2008R2 Enterprise Edition with sp3.
I do not see groups like this created for the cluster, only for stand alone instances.
SQLServerMSSQLUser$ComputerName$InstanceName
Thanks in advance for any websites or advice.
Alan

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

When you change the service account in the SQL Server Configuration Manager, it will assign the required windows permissions to the account.

However, there are two permissions that are not granted, since they are not required - but still very important. One is "Lock pages in memory", which prevents SQL Server from being swapped out in times of memory pressuere. The other is called something like "Perform volume maintenance operations". This permission is needed for Instant File Initialisation. That is, SQL Server can allocate a data file with having to zero it all. (Log files still have to be zeroed though.)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

illini1223 SSC Veteran Points: 211 More actions · Answer 2

I remote hosted to the node where the instance whose SQL Engine service account I wanted to change was running.

In SSCM, I put in the new domain account and password and confirmed I was OK with restarting the service.

The first message I received was

"Unspecified error (0x80004005)" - The title of this dialog box was WMI Provider Error.

I clicked OK and tried again and received

"The server threw an exception. (0x80010105)"

This method works fine on my non-clustered instances.

Thanks for your help!

Alan

illini1223 SSC Veteran Points: 211 More actions · Answer 3

I was able to get past the exceptions by restarting the Windows Management Instrumentation service on the each of the nodes of the cluster.

I'm not sure I needed to do both nodes, but wanted it to work.

I followed the instructions I found here.

http://developerseer.blogspot.com/2011/02/error-0x80010105-on-sql-2008-r2.html

Thanks,

Alan