Attempting to install security update KB948109 on a Windows Server 2003 SP2 machine with SQL Server 2005 SP2 fails with error code 0x7342 from the Microsoft Update web site. Downloading the update and attempting to install it manually results in this error message:
SQL Server Setup failed to modify security permissions on file Drive:Program FilesMicrosoft SQL ServerMSSQL.xMSSQLData for user %user%
Make sure that the account that you are running the installation under has appropriate permissions on the “Drive:Program FilesMicrosoft SQL ServerMSSQL.xMSSQLData” folder and all of the files within that folder. Read this KB article.
The Long Story:
I’m beginning to dread patching and updating my Windows machines. It’s not a matter of if something will fail, but how many failures await me and how long will it take to fix them. Maybe it’s common to all platforms, but I digress. After this latest Windows update installation failure, I Googled around to find out what the deal was. The “Known Issues” portion of the security bulletin did not make me feel warm and fuzzy. Apparently uninstalling the security update removes the Windows Internal Database (WYukon)?! After looking into it a bit more, it sounded like removing WYukon was not a can of worms I wanted to open up.
My first attempt at fixing the situation was (as usual) to download the update straight from Microsoft and attempt a manual installation of the update. Launching the freshly downloaded update installer offered me several choices. First, it wanted to know which SQL Server 2005 features I wanted to update. Hmmm… only the “SQL Server Databases Services 2005” option was checked by default. Shouldn’t all features be updated if possible? Google offered no guidance. After furrowing my brow for a few moments I decided to go all in.
Next I was alerted to a process that was locking a file that needed to be updated. Ah ha! Maybe that was the problem all along. I recall reading in the known issues section of the security bulletin that for this update to work the SQL VSS (Volume Shadow copy Service) had to be running. Maybe it wasn’t and that was what tangled the original update up. (NOTE: After this saga ended, I realized that the bulletin mentioned the SQL VSS Writer service and NOT simply the VSS service. Here’s a huge article about it written for backup application vendors. The following flailing around with the VSS service was probably completely useless, especially considering it didn’t work. =) )
I checked the service and indeed the VSS was not on and was set to manual startup type. Shouldn’t this be set to automatic startup? After thinking about the benefits of using VSS on this server, I decided to cancel the manual installation of the security update, start the VSS and then retry the installation of the security patch from within Windows’s automatic updates control panel. It still didn’t work. Maybe I needed to set the service to automatic and then reboot? Unlikely, but fine. The office is closed so I can reboot to my heart’s desire! However, after a reboot with the VSS set to auto the update still would not work. Maybe the VSS thing was a dead-end. I stopped the service just to be cautious and remove any new and uncertain changes from the equation.
My next step was to try the manual installation and stop any locking processes. To my surprise the installer said that there were no locking process! The reboot must have taken care of that. The installation of the patch started.
I nervously watched the installation progress and noticed one of the status messages said “Rolling back changes”. That doesn’t sound good. Soon thereafter I was met with another lovely failure message:
Strangely enough the installation continued. The check boxes that designated which product had been updated continued to be systematically checked off. I looked again at the failure message and noted that it didn’t actually say that the installation itself was a failure, but that a recently applied update failed to install. Maybe it simply detected one of my other failed attempts but this one currently under way was fine. Could my fears have been premature? The first installation completion message looked good. The completion message that I saw after clicking ‘next’ did not look so good.
After the installation finished, I clicked the ‘View Summary” button. Each database component had a separate section in the summary file. Each component was successfully updated except for the “SQL Server Database Services 2005” portion:
I noticed the location of a log file in the Details portion of the hotfix’s installation summary. Checking it out, I found the following error:
SQL Server Database Services 2005; Status: Failure; Error number: 29506; Error description: MSP Error: 29506 SQL Server Setup failed to modify security permissions on file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exists, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
I pondered the advice in the Error Description portion of the summary. The account/domain that I’m using does exist, the account that I’m launching the setup program under does have admin rights to the folder (verified through effective permissions), and the folder does exist. However, after finding and reading Microsoft KB916766 I found that it was not enough to have admin permissions on the folder. You also need admin rights on each file in the folder. I hunted through the data folder for files that did not include the local administrators group in its permissions. (Incidentally, I’m sure there is a command line / programmatic way of searching through a directory looking to see if a certain user is on the ACL, but alas I’m woefully unaware of it. CACLS maybe?) Sure enough, two of the databases only included the creating user’s credentials but not the administrators group or the local administrator user account. After adding the administrators group to the permissions (and making sure to select ‘Full Control’ as the privilege level) I reran the update, except this time I only selected to update the database services portion.
Success at last! At least until the next Patch Tuesday…