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 Server 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 Server 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 Server 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?
