SQL 2005 Service Accounts / Windows Groups

Question

SQL 2005 Service Accounts / Windows Groups

Steve Cornwell-278514

SSChasing Mays

Points: 652
More actions
August 19, 2009 at 12:40 pm

#214012

MSSQL 2005 has the following 3-SQL logins for a given SQL instance:
ComputerName\SQLServer2005MSSQLUser$ComputerName$InstanceName
ComputerName\SQLServer2005SQLAgentUser$ComputerName$InstanceName
ComputerName\SQLServer2005MSFTEUser$ComputerName$InstanceName
I understand that these are service accounts which map to Windows local group accounts for OS level rights and privileges. Although these logins are minimally documented, I acknowledge they are important and actually required for SQL Server to function.
On one of my instances, the ComputerName segment of the login name is now obsolete (i.e. this SQL instance was created on a server that no longer exists). Is there any risk to dropping and recreating the login with the correct ComputerName value?
Background: I am supporting an HP Polyserve (3.6.1) database cluster with 2-nodes. Each node has a dormant SQL environment called a "local profile." The SQL logins associated with these local profiles are fine. The ComputerName of the "ACTIVE" profile is the issue as it became obsolete due to a node replacement. Thus, the new node had a different machine name than the original node.

Viewing 7 posts - 1 through 6 (of 6 total)

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

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

These are actually local security groups. Unfortunately, I don't know PolyServe, so I can't answer your question, you're going to need to check with someone at HP, most likely. When Windows clustering is used, SQL Server actually relies on domain groups. But this is specific to Windows clustering.

K. Brian Kelley
@kbriankelley

Steve Cornwell-278514 SSChasing Mays Points: 652 More actions · Answer 2

Brian,

Thanks for the quick response. I didn't mean to confuse the issue by introducing Polyserve. I just want to know dropping and recreating these groups will cause any harm to SQL Server. I.E. Special permissions issues, service account access issues, etc.,

Thanks

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 3

Inside SQL Server, these groups are given permissions. On the OS, these groups have permissions to the file directories. I'm sure PolyServe has some way of handling it, because of the way it's architected, but unfortunately, I don't know what that is. In a normal situation, yes, you could be cuasing your SQL Server to fail.

K. Brian Kelley
@kbriankelley

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 4

I am in the process of implementing a four-node PolyServe cluster. What I have found is that the installation in PolyServe is not the same as MSCS clustering installation. Basically, the installation is a single instance install on each node - and with that, you end up with the first nodes groups setup in SQL Server.

Since you can't use or rely on the local group from a single node when failed over to any other node, these groups are rather useless. You can remove them from SQL Server, but make sure the service account is added independent from the group and granted sysadmin rights.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 5

Jeffrey Williams (8/25/2009)
I am in the process of implementing a four-node PolyServe cluster. What I have found is that the installation in PolyServe is not the same as MSCS clustering installation. Basically, the installation is a single instance install on each node - and with that, you end up with the first nodes groups setup in SQL Server.
Since you can't use or rely on the local group from a single node when failed over to any other node, these groups are rather useless. You can remove them from SQL Server, but make sure the service account is added independent from the group and granted sysadmin rights.

And I'm assuming you need to verify registry and file permissions, too, right?

K. Brian Kelley
@kbriankelley

Steve Cornwell-278514 SSChasing Mays Points: 652 More actions · Answer 6

Brian >> My SQL Server service runs under a domain account with membership in the Windows Administrators group. Thought this measure would alleviate any impact to SQL Server due to dropping/readding these 3-SQL logins.

I have the list of OS level rights/permissions allocated to these local groups. The only SQL level permission that I could identify is "SQL CONNECT" at the instance level. Does MS publish an article or script to pull any hidden permissions granted to these SQL logins?

Thanks for you help!

Regarding Polyserve...

This product performs a SID swap for the AGTGroup, SQLGroup, FTSGroup, etc., keys in the registry. Thus, the SQL login name remains the same as the active SQL instance is failed over/back, however, the SQL Server SID reflects the local Windows group SID. Polyserve overlays the Setup keys (i.e. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\Setup) as part of the failover. Just FYI.