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