2005 Service Accounts - help 1

[katgirl]

Hi,

This is a topic that I've researched and researched and still can't find a decent answer to. That is, what permissions are needed for the SQL Server and SQL Server Agent accounts??

To make things more complicated, we are running a 2005 cluster. However, I am assuming you change the service accounts using the Configuration Manager tool on the virtual server. I can't find an answer to that anywhere either, in all the piles and piles of clustering documentation I have on my desk. Sigh. And the services were not set up properly during the time of install, another sigh.

From what I can determine, the SQL Server Agent service must run under an account with the "ability to log on as a service" AND needs the "sysadmin fixed server role on the local instance of SQL Server" - however, I don't know how this applies to our cluster.

Is the "sysadmin fixed server role" also needed for the SQL Server service? I've never heard of this until 2005. Doesn't this totally fly in the face of security having admin privileges on the Agent account?

From 2005 BOL:

This account. Lets you specify the Windows domain account in which the SQL Server Agent service runs. The domain account that you specify must be a member of the sysadmin fixed server role on the local instance of SQL Server. We recommend that the Windows user account you choose is not a member of the Windows Administrators group.

...

Members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator's group, are members of the sysadmin fixed server role.

I am waiting to talk to our clustering consultant at Microsoft, but until then, I need to sort this out the best I can. I find it terrifically confusing... Microsoft does not make it easy at all.

Thank you

 

[Catadmin]

Microsoft doesn't make anything easy. If they did, they'd be out of business because the rest of us would be able to offer our own solutions. @=/

What you need is a plain ole vanilla Domain User account. Not a Domain Admin account, not an account that is part of the actual Windows Admin group on the Server (I'm talking OS, not SQL). And on that Domain User account, you need to add granular permissions like "Log on as a Service", which is an actual permission you get through Group Policies. See your network / Active Directory admins for more details on these permissions.

There are other network/domain permissions that this account might need, like "Process Token" and a few other things, but it really depends on whether you implement Replication or a dozen other SQL features.

Anyway, add the Domain User as a mapped login to SQL Server. I recommend turning off the Enforce Password Policy on both the Domain and on SQL Server. The Domain changes will have to be done via the network admins unless you have access to a Domain Controller. The reason you don't want to enforce the password policies is that if the account is forced to change its pwd, it could break a lot of stuff in SQL Server. So do NOT use a current user's login. Make up a completely new and separate Domain User account that will only ever be used by the SQL Server Service and/or the SQL Server Agent Service.

Once the login is mapped to SQL, go to ServerRoles and choose SysAdmin. This is NOT the same thing as being a Windows Administrator or a part of the BUILTIN admin group. SysAdmin can only do stuff to SQL. The Windows Admin or BuiltIn Admin groups can do anything to EVERYTHING on the server, which is a world of difference.

Make sure the services on all cluster nodes are using the exact same account. After that, you should be set.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katgirl]

thanks catadmin

yes, after re-reading bol, i came to the same conclusions you explain above

sysadmin fixed server role - didn't quite register the first time... duh

 

[Catadmin]

Glad I could help. Permissions get seriously tricky, especially when you expand into networking and domain perms.

Let us know if you need any more assistance.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katgirl]

yes, tricky

even the microsoft guy i spoke with today told me to set up the services domain user as an administrator - funny eh.

i find that once you understand something in sql server, it's simple - but before that - horribly complicated to the point of wanting to strangle the person who designed the nightmare you are living