A few weeks ago (or longer, I’m behind on email again) Nate Palm (LinkedIn) messaged me after the most recent SQL PASS virtualization webinar and asked me a fantastic question. He asked how to get rid of the orphaned account SIDs that sometimes come about after a SQL Server virtual machine template has been Sysprepped after the template deployment process.
If a VM was joined to a domain before the template conversion, or if the SQL Server services were not set to use a domain service account during installation, orphaned SIDs can occur. These are not detrimental to the services functioning normally when you grant proper access to your service accounts, but it’s always great to have a clean system.
After the sysprep process, the following groups were showing some orphaned SIDs.
- SQLServerMSSQLUser$ComputerName$MSSQLSERVER (ComputerName\SQLServerMSSQLUser$ComputerName$MSSQLSERVER
- SQLServerSQLAgentUser$ComputerName$MSSQLSERVER (ComputerName\SQLServerAgentUser$ComputerName$MSSQLSERVER
The process that I normally use after a sysprep involves a free program from Microsoft called SubInACL.exe. It is a command-line program that helps you deal with security details for programs, services, and can help us clean up the orphaned SIDs across the server. It’s crazy powerful, and can be used to do all sorts of things. I build this into my SQL Server VM templates.
Once a server has been deployed from a previously configured SQL Server template, I have a script in the works that is configured to clean up certain things. The key command is as follows.
- subinacl /samobject /cleandeletedsidsfrom=domainname=all
That should take care of it, and if it does not in your case, check out all the different operations that can be executed with various parameters.
