Category Archives: Hotfix

How to extract the contents of .MSP files

I had encountered an issue while installing a SQL Server service pack. The resolution for it was to replace the existing SQL Server binaries from that of a Service Pack. To extract the contents of a Service Pack executable we need to just type “ServicePackExeName /extract” as I explained in this post.

I was looking for the sqlservr.exe but it wasn’t directly available in the extracted folder. The folder structure looked something like this.

ExtractedFolder

From my past experience I knew that the Service Pack installer would copy sqlservr.exe by extracting sqlrun_sql.msp file.

sqlrun_sql.msp

Now how do I get the sqlservr.exe from the .msp file? The command “sqlrun_sql.msp /extract” wouldn’t work here.

One of the options is to extract the contents of the .msp file using a file archiver like 7-zip. After extracting the contents, look for a file which has the name CAB in it. In this case, the file name is “PCW_CAB_Family01”.

extracted

Again extract this file to a folder and the contents would look something like this.

sqlservr.exe

Now just rename the sqlservr.exe.******** to sqlservr.exe. That’s all. You have just extracted the requisite file from an .msp file.

The other alternative is to use the MsiX utility by Heath Stewart. The usage of this tool is very straight forward. Just type the following command in the Command Prompt and it would extract the contents of the .msp file

MsiX sqlrun_sql.msp

MsiX_cmdprompt

After this navigate to the folder where the file was extracted and extract the contents of the *CAB* file.

MsiX_extractedFollow the same steps and rename the file that you need (sqlservr.exe.****) to the requisite file.

Mission accomplished!

Add node fails with Attempted to read or write protected memory error

Recently I had worked on an issue where the end user was trying to add a third Node to an existing 2 node cluster. Soon after the setup is intiated, it would fail with the following error message (snippet from the Summary.txt).

Exception type: System.AccessViolationException
    Message: 
        Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
    Data: 
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Chainer.Infrastructure.MsiNativeMethods.MsiOpenPackageEx(String szPackagePath, UInt32 dwOptions, UInt32& hProduct)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.SetCommonProperties()
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiCore(String commandLine)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiWithRetry(String commandline)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsi(IEnumerable`1 commandLineProps)
        at Microsoft.SqlServer.Configuration.SetupExtension.MSIInstallerEngine.InstallPackage(PackageId pkg, InstallAction pkgAction)
        at Microsoft.SqlServer.Configuration.MsiExtension.PackageInstallAction.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

“Attempted to read or write protected memory. This is often an indication that other memory is corrupt.” Scary little error message! Since this is a setup related issue, the best place to start troubleshooting it is from the Setup Logs.  This article has more details on how to read SQL Server Setup logs.

Detail.txt pointed in the right direction as seen below.

Opening existing patch 'c:WindowsInstaller97363c.msp'.
Couldn't find local patch 'c:WindowsInstaller97363c.msp'. Looking for it at its source.
Resolving Patch source.
SOURCEMGMT: Looking for sourcelist for product {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
SOURCEMGMT: Trying source c:7cb5fee2d4775f9d53c7f95659x64setup.
Note: 1: 2203 2: c:7cb5fee2d4775f9d53c7f95659x64setupsql_ssms.msp 3: -2147287037 
SOURCEMGMT: Source is invalid due to missing/inaccessible package.
Unable to create a temp copy of patch 'sql_ssms.msp'.
Searching provided command line patches for patch code {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
Could not find source for missing patch {D7C6A337-F6BB-46CB-AE32-204DD6A8825D} -- orphaning this patch
SequencePatches starts. Product code: {72AB7E6F-BC24-481E-8C45-1AB

Now it was very clear that the issue is the classic missing MSI/MSP file issue. It was just matter of copying the right files to C:WindowsInstaller as explained here and here.

Once the missing files were replaced, the setup completed without any further errors.

MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service exclusion path

Yet another post related to SQL Server patching, eh? Yes. I feel I have a lot more lined up because in the next couple of months we will be applying Service Packs on close to 200 SQL Server instances.

Last Sunday, Service Pack 4 was supposed to be applied on one of the SQL Server 2005 instance. All components of SQL Server were successfully patched except for SQL Server Reporting Services. The following error was logged in the Summary.txt file.

Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\RS9_Hotfix_KB2463332_sqlrun_rs.msp.log
Error Number : 29528
Error Description : MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.

The RS9_Hotfix_KB2463332_sqlrun_rs.msp.log file was 6MB in size and quickly scrolling through it did not provide much useful information. The Event Logs also did not have much information apart from the message indicating the Service Pack installation failure. Instead of scrolling through the log file which had 61037 lines (yes, you read it right), I took a step back and analyzed the prerequisites.

Since it is SQL Server 2005, the Reporting Service depends on Internet Information Services (IIS). The Reporting Service Configuration Manager makes use of IIS to create the Virtual Directories along with the database hosted in SQL Server, to render the reports. My plan was to check IIS for any issues and then move on to look at the Reporting Services configuration.

Hence I opened up the Service Manager (services.msc). Surprise! Here is what I saw.

The IIS Admin Service was disabled. Obviously the Service Pack installer was unable to fetch the settings on IIS. Immediately, I enabled the IIS Admin Service and launched the Service Pack installer again and this is what I got.

Nice! Mission accomplished.

Why was the IIS service disabled? It was a test box. Do I need to say anything more?

Mising MSI or MSP files while installing SQL Server service packs

Last week we were applying SQL Server 2005 Service Pack 4 (SP4) on one of the instances. While patching the Database Services, the following screen popped up.

This issue is very well known by know. SQL Server update fails because of missing cached .msi files in C:\WINDOWS\Installer folder. In this post I had explained how can we resolve this issue by manually copying the files.

Then why am I posting about the same issue again? Earlier whenever SQL Server update installer did not find relevant files in C:\WINDOWS\Installer folder, it would fail silently without giving any clue as to why it failed. Then one had to manually go through the error logs in SetupBootStrap folder to understand the exact reason for the failure.

Starting SQL Server 2005 SP4, the installer pops up a dialogue box as seen in the above screenshot. With this the DBA can locate the path of the missing file (SQLRun_SQL.msi in this case), so that the patching progresses without interruption.

This is a very useful new feature in SQL Server service pack installer. I hope in the coming days, the installer lists all the missing .msi/.msp files in the initial stage so that the DBA can resolve the issues and then resume with the patch installation.

SQL Service Pack installation through Automatic Updates

My responses to 3 questions in the past 2 weeks has prompted this post. The issue is something like this.

  • Windows Automatic Updates is enabled on an instance running SQL Server
  • Windows Update prompts that a new SQL Service Pack/Hotfix is available to be installed and the user installs it through Windows Update.
  • Installation completes successfully and the correct version is reflected in SQL Server
  • After the next reboot, again the Windows Automatic Updates prompts to install the Service Pack which was previously installed. The installation would obviously fail since it is already applied.
  • Windows Update keeps prompting for the Service Pack to be installed.

This issue happens because the some of the cached files of the Windows Automatic Updates are corrupt. These files are located in [Default Windows Directory]\SoftwareDistribution\Download folder.

The following steps are useful to workaround this issue.

  • Stop the Automatic Updates service in the Service Control Manager (services.msc)
  • Open the folder C:\WINDOWS\SoftwareDistribution\Downloads
  • Delete all the files in this folder
  • Start the Automatic Update service

Since the cached files were cleared manually, when the Automatic Update service starts for the next time, it will recreate all the files required for its functionality and builds the cache afresh.

Server is in script upgrade mode. Only administrator can connect at this time

Yesterday I replied to a question in which the user was not able to connect to the SQL Server 2008 instance after installing Service Pack 2. The error message is

Login failed for user ‘LoginName’
Reason: Server is in script upgrade mode.
Only administrator can connect at this time.

I was able to quickly respond to this question because I had faced this situation when I had installed SQL Server 2008 SP2 on my VM. Here is what I had done during the installation of SP2.

  • SQL Server service was stopped prior to SP2 installation (this service is in manual mode in my VM, hence I forgot to start it)
  • SP2 was successfully installed on the instance
  • I started the SQL Server service
  • Immediately I launched SSMS to connect to the instance and I got the above error.

This issue happens because the SQL Server service was stopped when the SP2 installation was in progress. Service Pack installation completes successfully but certain scripts (most of the times sqlagent100_msdb_upgrade.sql) in the Service Pack will be applied only after the SQL Server service starts the next time. Hence when I started the SQL Server service, these scripts were still being applied to the instance and I was trying to connect to the instance during that time. Hence the error message.

What is the resolution? Just wait! Wait for few minutes after the SP is installed and you start the SQL Server service for the first time. Once these scripts are applied to the instance, all the users will be able to connect to the instance normally.

There is already a BUG reported for this issue and it is under “consideration”. Even I feel that the installer should finish its work before reporting that it is “successful”.

I have seen many DBAs stopping the SQL Server services before applying any Service Packs/hotfixes. In fact there is no need to do it. The Service Pack installer will take care of stopping and starting the instance as and when required. This issue is a reason for not stopping the service before applying the SP. Even though there is not much difference, for sure it will prevent a few anxious moments while trying to connect to the instance for the first after installing the SP

Authentication mode changes to Windows Only after installing CU

After installing SQL Server 2005 SP3 + CU4 on a Named Instance running on Windows Cluster, authentication mode changes to Windows Only from Mixed Authentication. This was an issue posted by someone yesterday.

About 4 months back, I remember facing the same issue when we had installed CU1 on a SQL Server 2005 (SP3) Named Instance running in Clustered environment. After installing the CU, the nodes were rebooted. When the nodes came back online, all the SQL Server Logins started failing to authenticate. On checking the settings, the authentication mode had changed to Windows Only from the earlier setting of Mixed Authentication. Then the authentication mode was changed back to Mixed Authentication manually.

This is a bug in SQL Server 2005 which is closed as “Won’t fix”. After installing a hotfix on SQL Server 2005 Named Instance on Cluster, ensure that the Authentication Mode has not changed.