As you know there are so many security measures are taken during every release of SQL Server. This version of SQL Server will be using Managed Service account to run SQL server related services. In other terms earlier versions we used Local system account as service account for all SQL Server related services however in this version we be will using individual local accounts for all SQL server related services. In this case whatever application runs on local system are allowed to access SQL Server. So to overcome this MS team has started using managed service accounts in SQL Server.
Let me give you a brief about Manage service account then we will discuss about the new changes related to service account in SQL Server 2012.
What is Managed Service Account?
Managed Service Account (MSA) is a new type of account that’s supported in Windows 7 & Windows 2008 R2. Main reason for this new account type is to isolate one service from another. In earlier operating systems we will run the services either in Localsystem or Domain accounts. Let’s assume you run SQL Server using Local system that means any other service running in Local system can connect to SQL Server and retrieve the data, this seems to be a loop hole when you run it in localsystem. We can use domain account to avoid this scenario however maintaining these domain accounts will take considerable time. In addition to this most of the organizations won’t change domain service account password in a period of time that means if someone knows the password he will be able to connect to SQL Server anytime as the password remains the same forever.
Considering all these things in mind they have added the new account feature in Windows 7 & Windows 2008 R2. Below are the advantages of Managed service account- Automatic password management. Password for MSA will be automatically changed every 30 days. However there is a known issue when the password gets changed it can cause a failed authentication attempt using old password, to overcome this you need to install a patch, refer KB article http://support.microsoft.com/kb/2494158 for more details.
- SPN management is made simpler (domain need to be in Windows 2008 R2 functional level) for these accounts, which allows service administrators to set SPN’s for these accounts. For windows 2003 and windows 2008 functional level, you can update the schema to support this.
http://technet.microsoft.com/en-us/library/dd548356.aspx
http://technet.microsoft.com/en-us/library/ff641729%28v=ws.10%29.aspx
What’s added in SQL Server 2012
In earlier versions (from SQL 2005) of SQL Server, as part of Service account security standards they created local groups in the computer and granted necessary permission for the groups where ever applicable. However in SQL Server 2012 that’s not applicable, they will create individual managed service account for each SQL Server service. This means all SQL Server related services will run on their own service account there by isolating each services. You can see from the image below each service have different service account and each of these account have their own SID
Naming convention for the service accounts is as below
Service Name | Instance | Service Account Naming Convention |
| Database Engine | Default | MSSQLServer |
| Agent Service | Default | SQLSERVERAgent |
| Reporting Server | Default | ReportServer |
| Analysis Service | Default | MSSQLServerOLAPService |
| Fulltext Service | Default | MSSQLFDLauncher |
| Database Engine | InstanceName | MSSQL$InstanceName |
| Agent Service | InstanceName | SQLAgent$InstanceName |
| Reporting Server | InstanceName | ReportServer$InstanceName |
| Analysis Service | InstanceName | MSOLAP$InstanceName |
| Fulltext Service | InstanceName | MSSQLFDLauncher$InstanceName |
| Integration Service | Not applicable | MSDtsServer110 |
Finally I’ve checked the administrators group in computer to check is any SQL related groups got created. Yes there are couple of groups still get created in computer for Browser service and analysis services. Analysis service account and browser service account are part of analysis group & browser group respectively.
From the screenshot above it’s clear that analysis groups are created per instance. Thus MSA is a added security hardening step in SQL Server 2012 to isolate all SQL server related services, thus no more access using local system account.
