SQL Server 2005 Service Pack does not Update all the Installed Components
Some weeks ago I received a call from one of our customers concerning the SP3 installation on SQL Server 2005. The customer was choosing to install the Service Pack from Microsoft Update (as you probably know, Microsoft Update is a superset of Windows Update that takes care of updating not only Windows OS components but other Microsoft software as Office or SQL Server) and the installation completed successfully as far as he can see however, Windows Update site was still reporting SP3 for SQL Server as a required update. No matter how many times he tried to install SP3, the installation from Microsoft Update always completed successfully but always offered SP3 as required afterwards.
In our case the original SQL Server build was on SP2 (build 2005.90.3042) so it was clear that the error was not on the Microsoft Update detection engine. I asked the customer to download the SP3 from the Microsoft Download site and check what were the results in this case, installing the SP this way we have more control on each one of the steps performed by the setup. The customer reported that again the SP installation completed without errors but the SQL Server version was still build 3042.
We looked into the Service Pack summary.txt installation file, located by default under the C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Log directory, and found the following information:
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.3.4035 x86
SQL Server Native Client ENU 9.00.4035.00 x86
MSXML 6.0 Parser ENU x86
SQLXML4 ENU 9.00.4035.00 x86
Microsoft SQL Server VSS Writer ENU 9.00.4035.00 x86
As you may have noticed there is a missing component on this list: Database Services. The SQL Server number that we can see in SQL Server Management Studio or by querying the built-in function @@version corresponds with the version of this component (the same version shown by the sqlservr.exe executable under the SQL Server program directory). The SP3 (build 4035) setup was therefore reporting a correct installation for all the shared SQL Server components, that were the only components detected in our case were.
Running a Process Monitor while executing the SP3 setup process we discovered several ACCESS DENIED processes that I could not reproduce on my test environment, most of this events were related to the Wmiprvse.exe component. We decided to check the different WMI (Windows Management Instrumentation) repositories status with the help of WMI Diagnostics Utility WMIDiag; as expected, WMIDiag reported several errors with the WMI service configuration:
34153 10:34:38 (0) ** WMIDiag v2.0 started on dinsdag 21 juli 2009 at 10:25.
34154 10:34:38 (0) **
34155 10:34:38 (0) ** Copyright (c) Microsoft Corporation. All rights reserved - January 2007.
[...]
35235 10:34:39 (0) ** DCOM security warning(s) detected: ........... 0.
35236 10:34:39 (0) ** DCOM security error(s) detected: ............. 2.
35237 10:34:39 (0) ** WMI security warning(s) detected: ............ 0.
35238 10:34:39 (0) ** WMI security error(s) detected: .............. 52.
35239 10:34:39 (0) **
35240 10:34:39 (1) !! ERROR: Overall DCOM security status: ......... ERROR!
35241 10:34:39 (1) !! ERROR: Overall WMI security status: .......... ERROR!
[...]
35401 10:34:39 (0) ** ERROR: WMIDiag detected issues that could prevent WMI to work properly!. Check 'C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\LOCAL SETTINGS\TEMP\WMIDIAG-V2.0_2003_.SRV.RTM.32_NF04_2009.07.21_10.24.40.LOG' for details.
35402 10:34:39 (0) **
35403 10:34:39 (0) ** WMIDiag v2.0 ended on dinsdag 21 juli 2009 at 10:34 (W:181 E:57 S:1).
At this point we were pretty sure the error was in the WMI service configuration. Thanks to the help of Carlos Carrolo from the Microsoft Platform Support Team we were able to trace down the problem to a known issue in SQL Server:
The fact that none of the SQL Server service components was listed under the SQL Server Configuration Manager console confirmed our finding (this is described as one of the Symptoms on the KB).
This Knowledge Base article did not mention our setup scenario, but the workaround provided worked in our case. This KB goes very specific explaining the root-cause of the issue: during the very beginning of the Service Pack setup process, the existing SQL Server services are discovered via WMI queries by the means of Wmiprvse.exe process; without the proper permissions, the process will fail to discover the installed components. This is further discussed at the end of this Knowledge Base article:
[…] These tools iterate through the services collection to obtain the information about the SQL Server 2005 services. When these tools iterate through the services collection, these tools generate the following Windows Management Instrumentation (WMI) queries:
SELECT * FROM RegServices
SELECT * FROM SqlService
When these tools generate the WMI queries, the SQL Server Web-Based Enterprise Management (WBEM) provider (Sqlmgmprovider.dll) is loaded into the Wmiprvse.exe process. Then, the SQL Server WBEM provider extracts and processes the information about the services of every instance of SQL Server 2005. […] The Wmiprvse.exe process in which the Sqlmgmprovider.dll file is loaded runs under the security context of the NETWORK SERVICE account. […]
It seemed that at some point after the SQL Server SP2 installation, the server was updated with a new Security Policy that broke the Discretionary Access Control List (DACL) for SQL Server leading to a broken WMI detection mechanism.