Lately I was asked about service SID during the installation
of SQL 2008 Cluster.
So I thought about it and wrote this blog post:
During the times of SQL 2005, while installing SQL in a cluster configuration we needed to assign elevated permission to a domain group that
contained the SQL Service account name. If an external user was a member of this group he had automatically elevated permission on the SQL instance.
SQL 2008 introduced the new option of SID. The first time I encountered the SID in SQL, was while installing my first SQL 2008 Cluster,
quite a while ago and it looked like this:
So what is the SID?
Basically it is a mechanism that assigns privileges to the service itself, rather than to the account under which the service runs.
Service SIDs managed to improve our security because they enable you to use the user Service Account with the least privileges required.
For Clustered installations
It enables you to use a domain account with minimal privileges on the box, which also improves your security because people end up knowing the domain service
For non-clustered installations
Per-Service SID, it allows you to use Network Service as the service account, improving security by
getting automatic password management, but without the traditional downside of having Network Service accumulate excessive privileges from multiple services.