1

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?

3

Service SIDs aren't a SQL Server thing. It's a technic inside windows to isolate different services from another. In the basic example where sql server is using NetworkService as account, many other services could have access to the SQL Server or its files.

Service SIDs and domain accounts have another importang aspect: if you change the domain account, all ACL secured objects are still valid and don't have to be updated.

See also http://support.microsoft.com/kb/2620201

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.