1

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!

komodosp
  • 353

2 Answers2

1

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.

harrymc
  • 498,455
0

The issue here is that the Sysprep changes the SIDs of the Windows users, but does not affect the logins in SQL Server

This means that the SIDs that SQL Server has stored matching each SQL Login to a Windows User is no longer valid - it's still the old SID.

The solution here was to create a script to run in <FirstLogonCommands> to...

  1. Stop SQL Server service
  2. Restart it in Single User Mode
  3. Execute a script that removes each SQL Login and then adds it again, using SQLCMD.
  4. Stop SQL Server service
  5. Restart in normal mode.

I had this as a .BAT file although there may be a more elegant way of doing it. Putting the server into Single User mode is necessary because at this point there are no accounts with permission to add/remove logins.

I am not sure why SQLEXPRESS was not affected. However I'm guessing that SQL was a bit confused and different versions just handled the confusion differently. e.g. when I recreated one logon this way in MYINSTANCE it didn't appear. When I recreated another, they all appeared in the "Security" list, but only the one I recreated actually worked. I suspect that while SQLEXPRESS was displaying all the Logins, they probably didn't work.

komodosp
  • 353