Sysprep removes all SQL users

autounattendsql serversysprep

I have two instances running on SQL Server (2017) – SQLEXPRESS and MYINSTANCE

I've added a few users to MYINSTANCE

However when I run Sysprep on the device, it deletes all but sa and Builtin\Users.

It appears to leave the users on SQLEXPRESS alone however.

How can I prevent this removal?

Here is my unattend file: (some redacted for security)

<?xml version="1.0" encoding="utf-8"?>
<unattend xmlns="urn:schemas-microsoft-com:unattend">
    <servicing>
        <package action="configure">
            <assemblyIdentity name="Microsoft-Windows-EnterpriseSEdition" version="10.0.14393.0" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="" />
            <selection name="Microsoft-Hyper-V-ClientEdition-Package" state="false" />
        </package>
    </servicing>
    <settings pass="specialize">
        <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <AutoLogon>
                <Password>
                    <Value>#####################################</Value>
                    <PlainText>false</PlainText>
                </Password>
                <Username>#########</Username>
                <LogonCount>4294967290</LogonCount>
                <Enabled>true</Enabled>
            </AutoLogon>
            <ComputerName>COMPACT-PC</ComputerName>
            <ProductKey>###########################</ProductKey>
            <TimeZone>GMT Standard Time</TimeZone>
        </component>
        <component name="Microsoft-Windows-SystemRestore-Main" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <DisableSR>1</DisableSR>
        </component>
    </settings>
    <settings pass="oobeSystem">
        <component name="Microsoft-Windows-International-Core" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <InputLocale>en-GB;</InputLocale>
            <SystemLocale>en-GB</SystemLocale>
            <UILanguage>en-GB</UILanguage>
            <UILanguageFallback>en-GB</UILanguageFallback>
            <UserLocale>en-GB</UserLocale>
        </component>
        <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <AutoLogon>
                <Password>
                    <Value>##################################</Value>
                    <PlainText>false</PlainText>
                </Password>
                <LogonCount>4294967290</LogonCount>
                <Username>#########</Username>
                <Enabled>true</Enabled>
            </AutoLogon>
            <FirstLogonCommands>
                <SynchronousCommand wcm:action="add">
                    <Order>9</Order>
                    <CommandLine>powercfg.exe -setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c</CommandLine>
                </SynchronousCommand>
                <SynchronousCommand wcm:action="add">
                    <CommandLine>powercfg.exe  -SETACVALUEINDEX ########################## #################### #####################</CommandLine>
                    <Order>10</Order>
                </SynchronousCommand>
                <SynchronousCommand wcm:action="add">
                    <Order>1</Order>
                    <Description>bcdedit.exe /set {current} bootstatuspolicy ignoreallfailures</Description>
                </SynchronousCommand>
                <SynchronousCommand wcm:action="add">
                    <CommandLine>&quot;C:\Windows\System32\sysprep\sidFoldRename.vbs&quot;</CommandLine>
                    <Order>12</Order>
                </SynchronousCommand>
            </FirstLogonCommands>
            <OEMInformation>
                <Manufacturer>###########</Manufacturer>
                <SupportURL>####################</SupportURL>
                <Model>#################</Model>
            </OEMInformation>
            <OOBE>
                <NetworkLocation>Home</NetworkLocation>
                <ProtectYourPC>3</ProtectYourPC>
                <HideEULAPage>true</HideEULAPage>
                <HideWirelessSetupInOOBE>true</HideWirelessSetupInOOBE>
                <SkipUserOOBE>true</SkipUserOOBE>
                <SkipMachineOOBE>true</SkipMachineOOBE>
            </OOBE>
            <UserAccounts>
                <LocalAccounts>
                    <LocalAccount wcm:action="add">
                        <Password>
                            <Value>#############################</Value>
                            <PlainText>false</PlainText>
                        </Password>
                        <Group>Administrators</Group>
                        <Description>######</Description>
                        <DisplayName>######</DisplayName>
                        <Name>#####</Name>
                    </LocalAccount>
                </LocalAccounts>
            </UserAccounts>
            <WindowsFeatures>
                <ShowMediaCenter>false</ShowMediaCenter>
                <ShowWindowsMail>false</ShowWindowsMail>
                <ShowWindowsMediaPlayer>false</ShowWindowsMediaPlayer>
                <ShowInternetExplorer>true</ShowInternetExplorer>
            </WindowsFeatures>
            <BluetoothTaskbarIconEnabled>true</BluetoothTaskbarIconEnabled>
        </component>
    </settings>
    <settings pass="generalize">
        <component name="Microsoft-Windows-PnpSysprep" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <PersistAllDeviceInstalls>true</PersistAllDeviceInstalls>
            <DoNotCleanUpNonPresentDevices>true</DoNotCleanUpNonPresentDevices>
        </component>
    </settings>
    <cpi:offlineImage cpi:source="wim:e:/sources/install.wim#Windows 10 Enterprise 2016 LTSB" xmlns:cpi="urn:schemas-microsoft-com:cpi" />
</unattend>

(The username & password are the same in each section)

Here is the command used to run sysprep…

c:\windows\system32\sysprep\sysprep.exe /generalize /oobe /shutdown /unattend:c:\windows\system32\sysprep\myAnswerFile.xml

But it even does this in audit mode without an answer file.

Thanks for any help…

Edit

I've just noticed something strange – if I start the service Single User Mode, the users all come back!

Best Answer

This is by design : Sysprep takes the state of an installation back to what it was when first installed: no user accounts, no configuration, no IP address, no name, no domain membership. The last step of Sysprep is the generalization, ensuring that the version installed from the image gets its own SIDs for the user IDs etc. and the OOBE (Out of the Box Experience) is run during installation.

Although the official documentation is not very detailed, you will find an explicit warning of this in the Amazon AWS notes for Create a custom Windows AMI:

Before you begin

  • Before performing Sysprep, we recommend that you remove all local user accounts and all account profiles other than a single administrator account under which Sysprep will be run. If you perform Sysprep with additional accounts and profiles, unexpected behavior could result, including loss of profile data or failure to complete Sysprep.

Microsoft has tweaked Sysprep to recognize an SQL Server installation, to the extent that you have found, but it cannot keep user logins, except for the administrator that uses a known SID, because of the reasons mentioned in the above quote.

SQLEXPRESS, being an old product, may not be influenced by this.