SQL Server 2005 – Express Edition – Manual Updates

Background

As promised a couple of posts back, over this last weekend we successfully patched an old SQL Server Express v2005 RTM Edition  to v2005 SP4.

In our initial post, we spoke of the difficulty we experienced trying to patch v2005 using Windows Update.

In this post we skipped having Automatic Updates apply the patch and downloaded the patch and manually applied it.

We stumbled badly but thankfully discovered workarounds through Google.

 

TroubleShooting

Event Viewer

Error 1260 – Windows cannot open this program because it has been prevented by a software restriction policy

Error Image

EventID_11260_MicrosoftSQLServerExpressEdition_Error_1260_0237PM (Brushed Up)

 

Textual

  1. Source :- MsiInstaller
  2. Type :- Error
  3. Event ID :- 11260
  4. Product :- Microsoft SQL Server Express Edition – Error 1260.  Windows cannot open this program because it has been prevented by a software restriction policy.

 

Remediation

Outline
  1. Get Application’s GUID
  2. Add it to the exception list
ScreenShot

ScreenShot – Before Adding Application GUID

WLRegistry_20170319_0243PM

ScreenShot – Before Adding Application GUID { 2AFFDD7* }

WLRegistry_20170319_0244PM

SQL Server – Error Log

Error: 17207, Severity: 16, State: 1 – FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file

Error Text

2017-03-19 14:02:52.13 spid5s      Starting up database ‘master’.
2017-03-19 14:02:52.18 spid5s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:52.18 spid5s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\master.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:52.59 spid5s      Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.

2017-03-19 14:02:52.82 spid5s      Starting up database ‘mssqlsystemresource’.
2017-03-19 14:02:53.13 spid7s      Starting up database ‘model’.

2017-03-19 14:02:53.18 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:53.18 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\model.mdf’. Diagnose and correct the operating system error, and retry the operation.

2017-03-19 14:02:54.16 spid7s      Clearing tempdb database.
2017-03-19 14:02:54.99 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:54.99 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\tempdb.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:55.97 spid7s      Starting up database ‘tempdb’.
2017-03-19 14:02:55.99 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:55.99 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\tempdb.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:56.18 spid5s      Recovery is complete. This is an informational message only. No user action is required.

Explanation

It is likely another application is interfering with the database’s engine access to the database files.

It could be Anti-Virus, OS File System drivers and filters or diagnostic tools.

In our case it could have been the fact that we were running SysInternal’s process monitor and chosen to focus in on the folder where how SQL Server data files are kept.

 

SQL Server Install – Summary.txt

Exit Code Returned: 1260

Error Text

**********************************************************************************
Product Installation Status
Product                   : Express Database Services (V2005SQLEXPRESS)
Product Version (Previous): 1399
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9Express_Hotfix_KB2463332_SQLEXPR.EXE
SQL Express Features      : SQL_Data_Files,SQL_Engine,SQL_SharedTools
Error Number              : 1260
Error Description         : Unable to install Windows Installer MSI file
———————————————————————————-

**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 1260

 

Remediation

Remediation – Change SQL Server Service Account to Domain Account

Review Account SQL Server is running under.  If it is running under “NT Authority\Network Services”, then change it to use to use a “Domain Account”.

Steps Outline

  1. Launch SQL Server Configuration Manager
  2. On the left panel, access the SQL Server Services Node
  3. On the right panel, review the list of services
  4. In our case, our targeted instance is v2005 SQLExpress
    • We reviewed all of the corresponding services for that instance
    • Currently, they are set to NT AUTHORITY\NETWORK SERVICES
    • We changed them to run under a least privileged domain account

ScreenShot

Sql Server Configuration Manager – List of Services

SQLServerConfigurationManager-NetworkService-20170319-0119PM (Brushed Up)

 

Sql Server Configuration Manager – Changing Service Account from Network Service to ….

changeServiceAccount_20170319_0110PM (Brushed Up)

 

Sql Server Configuration Manager – Changed Service Account from Network Service to Domain Account

SQLServerConfigurationManager-ChangedToDomainAccount-Services-20170319-0113PM (Brushed Up)

 

 

Summary

As always took the long way home.

Here are all the things we did:

  1. Whitelisted SQL Server v2005 SP4
    • Captured App GUID through Event Viewer or summary.txt ( SQL Server Log File )
  2. Experienced “Error: 17207, Severity: 16, State: 1 – FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file”
    • Stopped SysInternal’s Process Monitor
  3. Ran into Error Code 1260
    • Changed Service Account from “NT Authority\Network Services” to Domain Account
    • Ensured that the Service Account has full permissions on all folders that SQL Server has data and log files

Maintaining Windows Installer SecureRepairWhitelist through Powershell

Background

Here is a quick follow-up to a recent post, where we discussed options for getting rid of a pesky MS Windows Installer error “Product: Google Update Helper — Error 1260. Windows cannot open this program because it has been prevented by a
software restriction policy“.

Original Remediation

The redemptive process involved setting RemappedElevatedProxiesPolicy to 1.

 

Re-Install/Install Microsoft Hotfixes

Let us go ahead and install the “problematic” Microsoft hot-fixes that we studiously avoided last time.

  1. Security Update for Windows Server 2003 ( KB3072630 ) [ released on 2015-July -15 ]
    https://www.microsoft.com/en-us/download/details.aspx?id=47959

 

Opt-Out Affected Programs

Forward

Another option is to opt-out selected applications.

 

Code Analysis

  1. Launch Registry
  2. Transverse to
    HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer
  3. Add / Update new key named SecureRepairPolicy to 2
  4. Add the MSI’s product code for each application that you will like skip

 

Configuration File and Code

Configuration File


<?xml version="1.0"?>
<?xml-stylesheet type='text/xsl' href='style.xsl'?>
<!--Product Key List-->
<ProductKeys>
 <!--Google Update Helper 1.3.26.9-->
 <Product GUID="Google Update Helper 1.3.26.9">
  <Vendor>Google Inc.</Vendor> 
  <ProductName>Google Update Helper 1.3.26.9</ProductName>
  <ProductGUID>{60EC980A-BDA2-4CB6-A427-B07A5498B4CA}</ProductGUID>
  <CodeSegment info3="another attribute">
	<![CDATA[this is untouched code and can contain special characters /\@<>]]>
  </CodeSegment>
 </Product>
</ProductKeys>

Powershell Script

The Powershell script has been been uploaded to https://github.com/DanielAdeniji/SecureRepairWhitelist.

Summary

I have yet to sufficiently test out this code.

In fact, it has been only been minimally tested on two machines running MS Windows 2003.

We fixed our problem using the option described earlier.

In retrospect, the approach Microsoft offered as a workaround is likely a better option as it balances MSFT offering of a stronger system with the needs of individual Vendors and Applications.

 

Addendum

2015-Sept-26

  1. Added some bug fixes
  2. Last Google Chrome update gave us an actual opportunity to see bug recurrence and thus actually test code
  3. Removed code from posting and posted to GitHub

Google Update Helper – Error 1260 – Windows cannot open this program because it has been prevented by a software restriction policy

Background

While chasing down another bug returned back to my Event Viewer and noticed many entries bearing failed “Google Updates“.

Error Message

Listing:

EventViewer-Errors

 

Explanation:

  1. So basically every hour, specifically at the 55th minute, we are registering an error.

Textual:


The description for Event ID ( 11260 ) in Source 
( MsiInstaller ) cannot be found. 

Product: Google Update Helper -- Error 1260. Windows cannot open this program because it has been prevented by a 
software restriction policy.

 

Visual:

EventViewer-Error-EventID-11260

 

Google Search

Google Matches

Performed the per-functional Google Search, and here is what keep came up:

  1. “Windows cannot open this program because it has been prevented by a software restriction policy” error message when a user tries to open a file in Windows Server 2003
    https://support.microsoft.com/en-us/kb/873419

    • Advapi32.dll
      • 5.2.3790.199
      • File Date :- 17-Aug-2004

 

Download & Install

Downloaded and attempted install of the KB 873419.

Receive the message pasted below.

Textual:

Setup has detected that the Service Pack version of this system is newer than the update you are applying.

There is no need to install this update.

 

Image:

KB873419Cropped

 

Google Some More

After a while googled some more.  And, found more promising leads:

  1. ECI DDMS – Removing Windows Security Update KB2918614 & KB3072630
    http://support.ecisolutions.com/doc-ddms/keyop/setup/RemovingWinSecurityUpdateWinServer2003.pdf
  2. IBM – Error 1260. Windows cannot open this program because it has been prevented by a software restriction policy.
    http://www-01.ibm.com/support/docview.wss?uid=swg21690353
  3. SolidWorks

 

Steps

Overview

So following the instructions carefully detailed in the posting by ECI DDMS, here is what we did:

  1. Accessed Windows Registry and enable RemappedElevatedProxiesPolicy
  2. Ensure that hotfix KB3072630 and corresponding hotfixes are no longer offered

 

Windows Registry Change

Accessed registry branch “HKLM\SOFTWARE\Policies\Microsoft\Windows\Installer” and added/set registry entry for RemappedElevatedProxiesPolicy to 1

Script

Read Value


@REM Reg query
@REM https://technet.microsoft.com/en-us/library/Cc742028.aspx
@REM HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer RemappedElevatedProxiesPolicy
set "keyName=HKLM\SOFTWARE\Policies\Microsoft\Windows\Installer"
set "valueName=RemappedElevatedProxiesPolicy"
reg query %keyName% /v %valueName%


Output:

readRegistryOriginal

Effect Change


@REM Reg add
@REM https://technet.microsoft.com/en-us/library/Cc742162.aspx
@REM KeyName = HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer 
@REM Item = RemappedElevatedProxiesPolicy
set "keyName=HKLM\SOFTWARE\Policies\Microsoft\Windows\Installer"
set "valueName=RemappedElevatedProxiesPolicy"
set "valueType=REG_DWORD"
set "value=1"
reg add %keyName% /v %valueName% /t %valueType% /d %value%

 

Output:

RegistryOriginalWrite

 

GUI

GUI Original

GUIOriginal

 

GUI Revised

GUIRevised

 

Say No to KB2918614

There are a couple of avenues that are available for availing KB2918614.  Inclusive are:

  1. Desktop Automatic Updates
  2. Microsoft Update Web Site
  3. Corporate

KB Hotfixes

There are corresponding updates that we need to say No to, as well.  Here is the current list:

KB Title Date
KB3072630 MS15-074: Vulnerability in Windows Installer service could allow elevation of privilege: July 14, 2015 2015-July-14
KB2918614 MS14-049: Description of the security update for Windows Installer Service: August 12, 2014 014-August-12

 

 

Desktop Automatic Updates

Access “Automatic Updates” via your Desktop status panel.

StatusPanel

Automatic Updates

Choose updates to install

Here is the “Choose updates to install” window once we unchecked “Updates for Windows Server 2003 (KB2661254)”.

HideUpdates

Hide Updates

Please check “Don’t notify me about these updates again”.

HideUpdatesAfter

 

Repeat

Please repeat same for hot-fixes listed in this post.

 

Issues

Microsoft’s acknowledges there are issues with the KB hotfix.

  1. After you install this security update and try to install any MSI package that uses a mandatory or temporary user profile, the MSI package installation fails, and you receive an error message that resembles the following:
    • The profile for the user is a temporary profile
    • MSI Log :- SECREPAIR: A general error running CryptAcquireContext / Crypt Provider not initialized. Error:-2146893813
  2. After you install this security update, you may receive a User Account Control (UAC) prompt when you try to use remote deployments, centralized deployments, or other local methods to reinstall a program that was already installed before the security update was installed.

 

Workaround

Microsoft’s workaround includes using a tool such as ORCA to get the application’s product code.

Once the code is known one can register that product and others under the SecureRepairWhitelist key.

The operability of this approach is a bit reliant on vendors ensuring that their Application’s product code stays same through maintenance and patches.

 

Summary

MSFT continues to be under intense pressure to protect its surface area. Unfortunately, sometimes that will involve breaking working approaches and applications.

And, force vendors to return to Redmond and work towards new working API and understanding.