So I'm enjoying a cup of tea and decide, wouldn't it be nice to upgrade 1000's of instances of MSDE sp3a to sp4? Nice, i love unsupported products, let's do it !
Ugh, I know, but somethings aren't up to me, I deliver the results, this was the task, it's in the skill set, get it done. I ran into several issues where I had to upgrade a MSDE instance from SP3a to SP4. This should be a simple exercise, and it was for many installations, but there is a consistent theme where the upgrade fails and no manner of hitting, kicking or screaming helped.
Uninstalling the original installation and installing the SP4 version of MSDE was successful, but then the master database, logins, msdb all has to be restored to get the instance back to a working state.
I'd be fine with the uninstall and reinstall, but we're talking 1000's of installations, how many are going to have a problem ? Surely the solution can't be to uninstall and reinstall.
I found some good documentation on troubleshooting sp3 to sp4 for MSDE (remember it's been around awhile and is out of support). The best link I found was http://support.microsoft.com/kb/901163, unfortunately it didn't resolve the problem.
Consistently the error that keeps happening is UpgDetectBlankSaLogin Return value 3.
---------Actual Log File-----------
Starting custom action UpgDetectBlankSaLogin()
Entering Function MyMsiEvaluateCondition
MsiEvaluateCondition for VersionNT returned TRUE
End Function MyMsiEvaluateCondition
An error was encountered during connection to server: 3
Ending custom action UpgDetectBlankSaLogin().
Action ended 16:20:07: UpgDetectBlankSaLogin.2D02443E_7002_4C0B_ABC9_EAB2C064397B. Return value 3.
Action ended 16:20:07: INSTALL. Return value 3.
----------END Actual Log file----------
The sa password is definitely not blank, no matter how much the sa password was changed or it's complexity it always failed on this.
Creativity is needed to get around this, enter the windows software development kit (SDK) and Orca. Orca is an MSI Editor, and is found in the Windows SDK. The SP4 Service pack contains the MSI Files, and I decided to remove the step that runs the UpgDetectBlankSaLogin. Simply find the step in the MSI File (as shown below) and Delete (drop) the row associated with UpgDetectBlankSaLogin. Save and use it !
Now this is a calculated risk and requires testing. But it works, and on everyone of these installations that was failing the now modified SP4 runs fine.
This MSDE task took 3 days of my life away from me, hopefully you'll be more fortunate!
MSDE is on life support, and so is this solution, but if you can't solve the problem, go around the problem.