I came across a peculiar issue a few days ago while patching SQL Server. A security scan alleged that one of the SQL Servers was under patched, meaning it was missing the latest required hotfix. A colleague jumped on the problem and began researching.
Probably the first thing we do to verify that SQL Server is running the correct version is to do a simple
This of course returns the current version that your server is patched to.
SQL 2014 Test Lab Query
I apologize for the sparse lab screenshots, but I was unable to recreate this issue quickly in a lab.
Everything looked great from the query, it matched the version that all of the other boxes were running, and the server was reporting that it had the mandated hotfix. Obviously this was a case of a scan that has just failed to connect to the box. A network glitch causing a false positive? Not the first time, won’t be the last. However, we have to prove beyond a shadow of a doubt that everything is in order, so the next step was jumping on the box itself and investigating.
The hotfix was confirmed to be in the installed updates. For good measure, my colleague verified that the SQL Installation Manager agreed. Booting up the hotfix installation file revealed that the SQL Instance was not reporting that the hotfix was installed, nor would it let us install it, but the Shared Services were fine, showing the correct recent patch. Whoa, what’s going on here?
By this time, things were starting to look dicey, and my colleague’s shift was ending. There was talk of doing horrifying things the next day if nothing was resolved. Things like repairing the SQL Installation or even a full Reinstallation. I offered to take a look as a fresh pair of eyes, not expecting to find anything, but just to get up to date on what was going on and as a last-ditch effort before wiping the world clean.
I started poking around and realized that the SQL Shared Services were registering as a different version than the SQL Server Instance. Well, that sounded bad. I thought that if we at least matched those versions up, we could get somewhere after that. It was agreed that this couldn’t hurt anything, so I uninstalled the more recent patch on the Shared Services to match with what the SQL Instance should have been.
Ok, obviously there is something very wrong now. There had to be something we were missing.
And there was. Something simple and obvious.
In defeat, I finally took the time to just stare at the Installation Manager. I took a few moments to really read the whole screen.
That’s when I saw it. There is a box detailing the installed version of each SQL component on the box, and somehow everyone who had touched it overlooked the message here. The SQL Instance was reporting an incomplete update. Of course it would not let us patch the hotfix because it did not have the Cumulative Update required! It refused to install the Cumulative Update because it was reporting that SQL Server RTM was installed. No hot fixes. No service pack. Nothing. What?? How did no one catch this!
SQL Update for SQL 2014 as reference
Once I saw that, I immediately started a service pack installation, if only to verify that it would be accepted. The Installation Manager was more than happy to oblige now that I wasn’t skipping versions. The necessary cumulative updates and hot fixes went smoothly after that.
In summation, please remember to slow down and really assess the problem at hand. Don’t jump into a problem trying to fix it before you know what is going on and possibly make matters worse. SQL is very good at providing hints, or in this case, blatant answers to your problems. You just need to step back and know where to look. Yes, we all had done SQL installs before, and yes, we all knew about the version information. In the heat of the moment though, we all tried to fire first and ask questions later for something that appeared like a simple problem.
I do find it disconcerting that a SQL Instance would report a patch level of the Shared Instances instead of the instance itself through SELECT @@VERSION. I tried to recreate the scenario in a lab, but SQL responded with the correct version each time. Whatever caused the incomplete installation caused the version information to report back incorrectly through SQL.