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…
- Use a test machine (even your local workstation or laptop will work)
- 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.
- Create a test local or AD account, in my case I created an account named “ZZZ”
- 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:
- 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 filesMicrosoft SQL Server" dir -recurse | get-acl | select pschildname, owner, sddl | export-csv -NoTypeInformation -Path $homesqlfile.csv #Registry cd "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server" dir -recurse | get-acl | select pschildname, owner, sddl | export-csv -NoTypeInformation -Path $homesqlreg.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
- From SQL Server 2000 Enterprise Manager change the service account to the new account. The GUI will prompt for SQL service restart.
- 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:
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.
- From SQL Server Configuration Manager change the service account to the new account. The utility will prompt for a SQL Server restart.
- 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. EDIT: Dan Jones posted a blog entry clarifying the behavior which I verified in SQL Server 2005/Windows 2003 the account is added to SQLServer2005MSSQLUser% local group by SQLCM. As noted by Dan this is OS and SQL version specific behavior where if you are running pre-Vista (Windows 2003/XP) or SQL 2005 group membership is modified. So, where’s the “magic” re-ACL-ing?
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?”