how service sids are used ?

Question

how service sids are used ?

Oracle_91

SSChampion

Points: 14548
More actions
March 8, 2014 at 3:24 am

#284313

Hi Everyone,
This question is out of curiosity and it left me in confusion whether service account as any role to play in sql server startup and shutdown?
Can anybody explain how sql server 2008 Service Sids work and what is difference between Service SID and Service Account?
Little background :
From sql 2008, I see the service sid's are added to the SQLServer Groups which gets created on the computer.
For example, I have installed SQL Server 2008 instance on my Desktop PC and when open Computer management and check the sql server groups for my newly installed instance,
I see that Service SID is added to the group.
Start -> Run -> compmgmt.msc
I used the below command at command prompt to view the SID. I see the same SID is getting added to the windows groups created by sql server instance.
sc showsid MSSQLSERVER
Now my actual question is , prior to sql 2008, we used have domain account used as service account and this is one which gets added to the Groups which are created during sql server installation.
But from sql 2008, it is the service SID which is being added to the Groups which gets created.
If the service SID is now assigned the necessary permissions, what is the need for the Service account then? Is is just a container for the service?
It makes even more interesting if we consider Windows Server 2008 and sql server 2008 clusters.
On windows server 2008 cluster, the cluster service runs as Local system and considering I am using service sids instead of domain group, how come WFCS is able to communicate with Active Directory to bring the CNO's (sql network name) online and offline.
Any comments on that? Basically i wanted to know how these SID's works under the covers and what is the use service account from sql server 2008 onwards?
Thanks in advance.

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

Oracle_91 (3/8/2014)
Hi Everyone,
This question is out of curiosity and it left me in confusion whether service account as any role to play in sql server startup and shutdown?
Can anybody explain how sql server 2008 Service Sids work and what is difference between Service SID and Service Account?
Little background :
From sql 2008, I see the service sid's are added to the SQLServer Groups which gets created on the computer.
For example, I have installed SQL Server 2008 instance on my Desktop PC and when open Computer management and check the sql server groups for my newly installed instance,
I see that Service SID is added to the group.
Start -> Run -> compmgmt.msc
I used the below command at command prompt to view the SID. I see the same SID is getting added to the windows groups created by sql server instance.
sc showsid MSSQLSERVER
Now my actual question is , prior to sql 2008, we used have domain account used as service account and this is one which gets added to the Groups which are created during sql server installation.
But from sql 2008, it is the service SID which is being added to the Groups which gets created.
If the service SID is now assigned the necessary permissions, what is the need for the Service account then? Is is just a container for the service?
It makes even more interesting if we consider Windows Server 2008 and sql server 2008 clusters.
On windows server 2008 cluster, the cluster service runs as Local system and considering I am using service sids instead of domain group, how come WFCS is able to communicate with Active Directory to bring the CNO's (sql network name) online and offline.
Any comments on that? Basically i wanted to know how these SID's works under the covers and what is the use service account from sql server 2008 onwards?
Thanks in advance.

The service SIDs are used purely by the SQL Server instance and do not relate to the Windows accounts used. You should still use a domain user account so that the service may access network resources when it has to.

Pre SQL 2008 used local groups (domain groups in a cluster) and it was extremely easy to put a windows account into one of the groups and obtain SYSADMIN access. The service SIDs are virtual and not manageable, you cant change\add to the membership.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

Thank you very much Perry. It caused me a lot of confusion and it was eating my head for a long time.. No documentation provide better explanation. Everyone is providing urls but no one was able to provide proper explaination. Now I feel better.

Thanks a ton 🙂