This one is for all my DBA friends out there. I recently ran into this issue when running a security patch installation for a SQL 2005 instance on SP4. The setup failed, and when I looked into the “C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Log\Hotfix” folder (this is where the patch setup files for 2005 are to be found), here’s what I found in the latest summary.txt:-

**********************************************************************************
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 5000
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2494120_sqlrun_sql.msp.log
Error Number : 1642
Error Description : Unable to install Windows Installer MSP file
----------------------------------------------------------------------------------
Product : SQL Server Tools and Workstation Components 2005
Product Version (Previous): 5000
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB2494120_sqlrun_tools.msp.log
Error Number : 1642
Error Description : Unable to install Windows Installer MSP file
----------------------------------------------------------------------------------

“Hmmm”, I  thought to myself, “here’s one I haven’t seen before”. Since the Log file indicated was in the same folder, I pulled up the “SQL9_Hotfix_KB2494120_sqlrun_sql.msp.log” file, and here’s an extract from the time the error occurred:-

MSI (s) (D8:6C) [07:36:23:597]: File will have security applied from OpCode.
MSI (s) (D8:6C) [07:36:23:644]: Original patch ==> e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp
MSI (s) (D8:6C) [07:36:23:644]: Patch we're running from ==> C:\WINDOWS\Installer\5daea.msp
MSI (s) (D8:6C) [07:36:23:644]: SOFTWARE RESTRICTION POLICY: Verifying patch --> 'e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp' against software restriction policy
MSI (s) (D8:6C) [07:36:23:644]: Note: 1: 2262 2: DigitalSignature 3: –2147287038
MSI (s) (D8:6C) [07:36:23:644]: SOFTWARE RESTRICTION POLICY: e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp is not digitally signed
MSI (s) (D8:6C) [07:36:23:644]: SOFTWARE RESTRICTION POLICY: e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp is permitted to run at the 'unrestricted' authorization level.
MSI (s) (D8:6C) [07:36:23:660]: SequencePatches starts. Product code: {130A3BE1-85CC-4135-8EA7-5A724EE6CE2C}, Product version: 9.00.1399.06, Upgrade code: {929C9FEC-8873-4A1A-A209-9AF432E8E1D1}, Product language 1033
MSI (s) (D8:6C) [07:36:23:660]: 3.0 patch e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp is of type QFE
MSI (s) (D8:6C) [07:36:23:660]: PATCH SEQUENCER: verifying the applicability of QFE patch e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp against product code: {130A3BE1-85CC-4135-8EA7-5A724EE6CE2C}, product version: 9.00.1399.06, product language 1033 and upgrade code: {929C9FEC-8873-4A1A-A209-9AF432E8E1D1}
MSI (s) (D8:6C) [07:36:23:660]: Validating transform 'Target01ToUpgrade01' with validation bits 0x920
MSI (s) (D8:6C) [07:36:23:660]: Note: 1: 2749 2: Target01ToUpgrade01 3: C:\WINDOWS\Installer\5daea.msp 4: 9.4.5000.00 5: 9.00.1399.06
MSI (s) (D8:6C) [07:36:23:660]: 1: 2749 2: Target01ToUpgrade01 3: C:\WINDOWS\Installer\5daea.msp 4: 9.4.5000.00 5: 9.00.1399.06
MSI (s) (D8:6C) [07:36:23:660]: PATCH SEQUENCER: QFE patch e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp is not applicable.
MSI (s) (D8:6C) [07:36:23:660]: SequencePatches returns success.
MSI (s) (D8:6C) [07:36:23:660]: Final Patch Application Order:
MSI (s) (D8:6C) [07:36:23:660]: Other Patches:
MSI (s) (D8:6C) [07:36:23:660]: Unknown\Absent: {89F18EEE-A409-4B25-915A-0F03651ECF48} - e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp
MSI (s) (D8:6C) [07:36:23:660]: Product: Microsoft SQL Server 2005 - Update '{89F18EEE-A409-4B25-915A-0F03651ECF48}' could not be installed. Error code 1642. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2494120_sqlrun_sql.msp.log.
MSI (s) (D8:6C) [07:36:23:660]: Note: 1: 1708
MSI (s) (D8:6C) [07:36:23:660]: Note: 1: 2729
MSI (s) (D8:6C) [07:36:23:660]: Note: 1: 2729
MSI (s) (D8:6C) [07:36:23:660]: Product: Microsoft SQL Server 2005 -- Installation failed.

Just for kicks, I also checked out the Hotfix.log (it’s the precursor to the “Detail.txt” in SQL 2008 that we so often use). Here’s an extract from it for reference:-

03/29/2012 07:36:17.986 Copy Engine: Creating MSP install log file at: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2494120_sqlrun_sql.msp.log
03/29/2012 07:36:17.986 Registry: Opened registry key "Software\Policies\Microsoft\Windows\Installer"
03/29/2012 07:36:17.986 Registry: Cannot read registry key value "Debug", error 0
03/29/2012 07:36:23.785 MSP returned 1642: The installer cannot install the upgrade patch because the program being upgraded may be missing or the upgrade patch updates a different version of the program. Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch.
03/29/2012 07:36:23.785 Registry: Opened registry key "Software\Policies\Microsoft\Windows\Installer"
03/29/2012 07:36:23.785 Registry: Cannot read registry key value "Debug", error 997
03/29/2012 07:36:23.801 Copy Engine: Error, unable to install MSP file: e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixSQL\Files\sqlrun_sql.msp
03/29/2012 07:36:23.801 The following exception occurred: Unable to install Windows Installer MSP file Date: 03/29/2012 07:36:23.801 File: \depot\sqlvault\stable\setupmainl1\setup\sqlse\sqlsedll\copyengine.cpp Line: 807
03/29/2012 07:36:24.066 Watson: Param1 = Unknown
03/29/2012 07:36:24.066 Watson: Param2 = 0x66a
03/29/2012 07:36:24.066 Watson: Param3 = Unknown
03/29/2012 07:36:24.066 Watson: Param4 = 0x66a
03/29/2012 07:36:24.066 Watson: Param5 = copyengine.cpp@807
03/29/2012 07:36:24.066 Watson: Param6 = Unknown
03/29/2012 07:36:24.066 Watson: Param7 = SQL9
03/29/2012 07:36:24.066 Watson: Param8 = @
03/29/2012 07:36:24.066 Watson: Param9 = x86
03/29/2012 07:36:24.066 Watson: Param10 = 5057
03/29/2012 07:36:24.066 Installed product: SQL9
03/29/2012 07:36:24.066 Installing product: SQLTools9
03/29/2012 07:36:24.285 Registry: Opened registry key "Software\Microsoft\Windows\CurrentVersion\Uninstall"
03/29/2012 07:36:24.301 Installing instance: SQL Tools
03/29/2012 07:36:24.301 Installing target: SPJP063
03/29/2012 07:36:24.301 Installing file: sqlrun_tools.msp
03/29/2012 07:36:24.332 Copy Engine: Creating MSP install log file at: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB2494120_sqlrun_tools.msp.log
03/29/2012 07:36:24.332 Registry: Opened registry key "Software\Policies\Microsoft\Windows\Installer"
03/29/2012 07:36:24.332 Registry: Cannot read registry key value "Debug", error 0
03/29/2012 07:36:38.930 MSP returned 1642: The installer cannot install the upgrade patch because the program being upgraded may be missing or the upgrade patch updates a different version of the program. Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch.
03/29/2012 07:36:38.930 Registry: Opened registry key "Software\Policies\Microsoft\Windows\Installer"
03/29/2012 07:36:38.930 Registry: Cannot read registry key value "Debug", error 997
03/29/2012 07:36:38.930 Copy Engine: Error, unable to install MSP file: e:\1d8e62c6a0cf9250ed0fe97eebe1\HotFixTools\Files\sqlrun_tools.msp
03/29/2012 07:36:38.930 The following exception occurred: Unable to install Windows Installer MSP file Date: 03/29/2012 07:36:38.930 File: \depot\sqlvault\stable\setupmainl1\setup\sqlse\sqlsedll\copyengine.cpp Line: 807

No clues, right? So finally, in a desperate attempt, I decided to capture a Process Monitor trace (available on Technet, see here ). And whoa, look what I found there:-

00:24:41.4884798 msiexec.exe 6764 RegEnumKey HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\<SQL GUID>\Patches NO MORE ENTRIES Index: 0, Length: 288

“Aha”, I thought to myself, “so this is the problem”. Basically (and you can check this on a normal/healthy installation), the Patches key is supposed to have subkeys (1 for each patch) for all the patches applied to the SQL Server instance so far; and those keys seem to be missing in this case.

So what happened? God knows. Could be anything, could be someone deleted them manually, or some cleanup programs “Cleaned” them up by mistake, etc.

The main question, though, is how do we fix it? Simple enough, removing and re-installing the previous Service Pack should recreate the registry keys, and should thus fix it, right? Wrong. Service Pack uninstallation was introduced from SQL 2008 onwards, so that’s not possible. So what else?

Warning : This is one of those “weird” solutions. Some might even call it a hack, though I just call it exploiting a loophole in the service pack installer. Here are the steps:-

  1. Rename the “sqlservr.exe” in the Binn folder of your instance.
  2. Copy the sqlservr.exe from another instance, that’s on a lower SP/RTM build than the your target instance (in my case, the target was an instance on SP4, so I used the sqlservr.exe from an instance on SP3)
  3. Paste the exe into the Binn folder of your instance.
  4. Now run the SP setup (in my case, it was the SP4 setup), and it should be able to detect SQL on the lower build and allow you to proceed with the install, thereby creating the missing registry entries in the process.

Yes, you could say this is a loophole in the Service Pack install process, that it only checks the build of the sqlservr.exe to determine what build the instance is on, and I would actually agree with you. But in situations like this, it’s these “loopholes” that come in handy.
As always, any comments/feedback/questions are both welcome and solicited.