question

ColmdeCleir-6279 avatar image
0 Votes"
ColmdeCleir-6279 asked LimitlessTechnology-2700 answered

Sysprep deletes all SQL user logons.

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...

windows-10-setup
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ColmdeCleir-6279 avatar image
0 Votes"
ColmdeCleir-6279 answered

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.

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello,

Thank you for your question.

Additionally,

Its better to sysprep SQL without Databases mounted or its unconfigured.
I would like to suggest to have a look on below Microsoft article if you want to sysprep Windows system with MS SQL installed.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/considerations-for-installing-sql-server-using-sysprep?view=sql-server-ver15



If the reply was helpful, please don’t forget to upvote or accept as answer.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.