Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

What’s Going on with SQL Server Service Account Changes?

For years we’ve been told you should use Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL 2005 or higher to change the SQL Service account. Why you may ask well, because according to the documentation Configuration Manager does “magic” stuff to re-ACL things for the new account. I decided to put the notation that SQL Server files, folders or Registry keys get Re-ACL’d when Enterprise Manager (SQL 2000) or SQL Server Configuration Manager (SQL Server 2005 or higher) is used to change the SQL Server service account to a few tests…

Setup

  1. Use a test machine (even your local workstation or laptop will work)
  2. Note: Since I’m using test machines the SQL instances were setup and running under Local System account and then changed to an AD account with no assigned permissions or groups other than the default AD Users group.
  3. Create a test local or AD account, in my case I created an account named “ZZZ”
  4. We’ll need the SID for the account when we dump the ACLs, from a PowerShell host obtain the SID for the account using the following commands:
$domain = "CORP"
$user = "ZZZ"
$ntAccount = new-object System.Security.Principal.NTAccount($domain, $user)
$sid = $ntAccount.Translate([System.Security.Principal.SecurityIdentifier])
$sid.value

For my account this returns:

S-1-5-21-3799912004-3082830092-2763560395-1116

The Tests

  • For SQL Server version 2000, 2005 and 2008 I’ll use the recommended utilities to change the service account.
  • I’ll then run a the following PowerShell commands to export the ACL’s for all SQL Server files, folders and registry keys:
#FileSystem
cd "C:\program files\Microsoft SQL Server"
dir -recurse | get-acl | select pschildname, owner, sddl | export-csv -NoTypeInformation -Path $home\sqlfile.csv
#Registry
cd "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server"
dir -recurse | get-acl | select pschildname, owner, sddl | export-csv -NoTypeInformation -Path $home\sqlreg.csv

Finally I’ll look for any ACL’s assigned to the SID of my new service account (S-1-5-21-3799912004-3082830092-2763560395-1116).

SQL Server 2000 Enterprise Manager

  1. From SQL Server 2000 Enterprise Manager change the service account to the new account. The GUI will prompt for SQL service restart.
  2. After SQL Server has restarted, run the PowerShell commands to export ACL’s as instructed in “The Tests” section.

I’ve posted my output files SQL2K_sqlfilefull2.csv and SQL2K_sqlregfull2.csv in the accompanying download.

Looking at the ACL’s in the CSV file we see, yes in fact Enterprise Manager did re-ACL both folders, files and registry keys. A typical entry includes the file/folder name and the following SDDL syntax with the service account’s SID:

…(A;OICI;FA;;;S-1-5-21-3799912004-3082830092-2763560395-1116)…

Checking off Enterprise, we’ll move to SQL 2005 and 2008…

SQL Server Configuration Manager (2005/2008)

Since the results of both SQL Server 2005 and 2008 were the same, I’ll summarize the steps and results together.

  1. From SQL Server Configuration Manager change the service account to the new account. The utility will prompt for a SQL Server restart.
  2. After SQL Server restarts run the  PowerShell commands to export ACL’s as instructed in “The Tests” section.

I’ve posted my output files SQL2K5_sqlfilefull2.csv, SQL2K8_sqlfilefull2.csv, and SQL2K5_sqlregfull2.csv and SQL2K8_sqlregfull2.csv in the accompanying download.

This time, when we look at the ACL’s only two files show a change not in the permissions, but in the owner of the file ERRORLOG and log_*.trc (the current default trace file). The change in file ownership isn’t surprising as the new service account will create the files on restart which has nothing to do with re-ACL-ing. Also there aren’t any changes in ACL’s for the registry. As an additional check I verified the service account wasn’t auto-magically added to any of the built-in Windows groups SQL Server 2005 or higher creates on the local machine. So, where’s the “magic” re-ACL-ing?

Summary

According to my tests none of the ACL’s were changed in SQL 2005 or higher while in SQL 2000 they were. So is the BOL documentation wrong or am I missing something? Or is the “Only use SQL Server Configuration Manager” directive a “SQL Myth?”

Download

Comments

Posted by Darren Green on 13 December 2010

I always find the user rights are more of an issue, things like "Log on as a Service", "Replace a Process Level Token" that are easy to overlook when doing this manually. The exact details are documented for each version e.g. support.microsoft.com/.../283811

Posted by EdVassie on 13 December 2010

Changing the file ACLs is not the only change made.  

In SQL 2005 and above (until W2008 R2), windows groups created during SQL Server install that contain the service accounts.  This allows stability in the file and registry ACLs, as the primary authorisation is to the groups.  Changing the service account via Configuration Manager updates the membership of these groups.  

W2008 R2 replaces most of these groups with service SIDs, but I have not checked the impact of changing a service account on these.

Posted by cmille19 on 13 December 2010

As noted "...verified the service account wasn’t auto-magically added to any of the built-in Windows groups SQL Server 2005 or higher creates on the local machine."

I observed 0 changes in group membership.

Posted by cmille19 on 13 December 2010

Ran another quick test of SQL Configuration Manager to see if user privileges are changed. Observed no changes in the following rights described in KB article support.microsoft.com/.../283811

   * Act as Part of the Operating System

   * Bypass Traverse Checking

   * Lock Pages In Memory

   * Log on as a Batch Job

   * Log on as a Service

   * Replace a Process Level Token

Posted by volox4games on 13 December 2010

Was your ZZZ account a member of any groups in AD or on the local machine?  Because if it wasn't and it wasn't added to the groups that SQL creates and it wasn't granted any of the operating system rights, then I question how it is even running?  Have you verified in Task Manager that it is actually running under that account context?

Posted by cmille19 on 13 December 2010

ZZZ account was only a member of the AD "Users" group. The test procedure I described is very simple, repeatable and does not require an AD. Try it yourself on a standalone machine, but use a local Windows user with no group assignment (other than default Users).

Posted by jlamacchia 23271 on 13 December 2010

I suggest checking out the local windows groups with "SQL" in their names.  I would not expect ACL changes if permissions are assigned to the group and using the utilities simply adds and removes the service account to the local groups.

Posted by cmille19 on 13 December 2010

@jlmacchia

I did that as noted in the article: "... As an additional check I verified the service account wasn’t auto-magically added to any of the built-in Windows groups SQL Server 2005 or higher creates on the local machine..."

Keep in mind the testing I did is simple, fast and repeatable (takes less than 5 minutes). Try it yourself on your machine. If you see different results than mine, please post.

Posted by Jason Brimhall on 13 December 2010

Good stuff.  Thanks Chad

Posted by Mhlewis on 13 December 2010

My understanding was that it had to do with the Service Master key generation.  At least that's what my MCTS Exam book says (Mike Hotek author):  "the service master key is encrypted using the SQL Server service account and service account password. The windows Services console does not contain the code necessary to encrypt a service master key; therefore, you should never use the Windows Services console to change the service account or service account password."

Posted by cmille19 on 13 December 2010

@Mhlewis

I've tested SMK using both SQL Server Configuration Manager and Service Control both seem to update the SMK. See comments on my blog for details

sev17.com/.../whats-going-on-with-sql-server-service-account-changes

Posted by cmille19 on 15 December 2010

Dan Jones from Microsoft posted a blog entry clarifying the behavior blogs.msdn.com/.../changing-service-account-amp-service-account-password.aspx

I've tested and verified that on SQL 2005 and Windows 2003 the service account is added to the local group SQLServer2005MSSQLUser% by SQL Server Configuration Manager.

Posted by volox4games on 15 December 2010

Chad,

So by your last comment...

"I've tested and verified that on SQL 2005 and Windows 2003 the service account is added to the local group SQLServer2005MSSQLUser% by SQL Server Configuration Manager."

I would take that to mean your statement prior was incorrect...

"... As an additional check I verified the service account wasn’t auto-magically added to any of the built-in Windows groups SQL Server 2005 or higher creates on the local machine..."

Posted by cmille19 on 15 December 2010

I checked the groups on my SQL 2008/Windows 2008 R2 and SQL 2008 R2/Windows 7. And in fact there are NO changes, but after Dan's post I then went back to my SQL 2005/Windows 2003 box and verified the account was there. So, yes I missed it on SQL 2005 which is different than 2008 (depending on OS).

Posted by cmille19 on 16 December 2010

More information on "Service SID", apparently this feature was added to Windows 2008/Vista OS or higher. Allows assignment of ACL to service and not service account! Explains why SQL 2008 service account behaves the way it does.

blogs.technet.com/.../ws2008-windows-service-hardening.aspx

Leave a Comment

Please register or log in to leave a comment.