Warning: Windows update KB2494088 can break SQL Server

Question

It’s 1:47AM and I just got done fixing a problem on a customer's production SQL Server.  Normally SQL Server is not updated by windows updates, but a recent security patch for SQL (KB2494088) was released this week.  The network team installed it along with other patches, and it “appeared" to kill the master database of their most important SQL instance.   

We have fixed the problem (so this is just an FYI).  Just be aware that this potential issue can arise and the events in the Application log are quite alarming.  It turns out the fix is pretty straightforward because this was a known issue for patching SQL instances of a certain build and fortunately there was a workaround.  I was able to correct the problem and allow the upgrade to finish, and everything seems fine now.  You can head this off by configuring the SQL Server to allow “Agent XPs” before installing the hotfix. 

Per the KB article KB2163980, this is a known issue for SQL Server instances that:

·         Are running SQL 2008 R2 RTM

·         Have a “UCP” database – the Utility Control Point that monitors SQL Server performance

http://support.microsoft.com/kb/2163980

 Note that if you read the KB article for the hotfix (KB2494088), this UCP problem is NOT listed as a “known issue”.  But I’m sure Microsoft is going to catch a lot of heat for this over the next week or two.  It’s really unusual for them to roll out a SQL Server patch as a Windows update, and I’m sure it caught a lot of DBAs by surprise.

Answer 1

Interesting however I have 1 question (and I'm not trying to justify Microsoft's actions).

Does your client know anything about testing patches etc in test environments or do they usually just deploy patches to their most important SQL instance without testing?

Sounds like the network team should all be sacked for letting this happen in the first place :-)

 

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Answer 2

It is nice that I know what I should have done before the update had been installed, but it installed itself automatically and I can't use SQL Server currently. How should I repair it when you can not start the server? I tried the trick as described in kb2163980 with the restart parameter T902, but that did not work.

Danny Springer

---

Verne

Answer 3

 

It’s 1:47AM and I just got done fixing a problem on a customer's production SQL Server.  Normally SQL Server is not updated by windows updates, but a recent security patch for SQL (KB2494088) was released this week.  The network team installed it along with other patches, and it “appeared" to kill the master database of their most important SQL instance.   

We have fixed the problem (so this is just an FYI).  Just be aware that this potential issue can arise and the events in the Application log are quite alarming.  It turns out the fix is pretty straightforward because this was a known issue for patching SQL instances of a certain build and fortunately there was a workaround.  I was able to correct the problem and allow the upgrade to finish, and everything seems fine now.  You can head this off by configuring the SQL Server to allow “Agent XPs” before installing the hotfix. 

 

Per the KB article KB2163980, this is a known issue for SQL Server instances that:

 ·         Are running SQL 2008 R2 RTM

 ·         Have a “UCP” database – the Utility Control Point that monitors SQL Server performance

 

http://support.microsoft.com/kb/2163980

 

 Note that if you read the KB article for the hotfix (KB2494088), this UCP problem is NOT listed as a “known issue”.  But I’m sure Microsoft is going to catch a lot of heat for this over the next week or two.  It’s really unusual for them to roll out a SQL Server patch as a Windows update, and I’m sure it caught a lot of DBAs by surprise.

 

Did you get any option to uninstall this patch?

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 4

@Jeff:

Yes, obviously a crack team of experts would have a complete testbed of servers standing by to run through every patch and every configuration.  Don't you? 

I did, in fact, do a dry run of the patch on a test instance, and it went fine.  My instance did not, however, have an MDW database, nor did I think to test it.  Why not?  Because when I read the KB article for 2494088, it made no mention of it. 

I consider myself extremely fortunate to have Binged the right error message, which led me to KB 2163980, which had a clear solution.  Even this article made no specific mention of 2494088; it was written about a Cumulative Update.  I had to compare the error messages (which were identical) and draw my own conclusions.

Most DBAs aren't well-versed in CUs because technically we're not supposed to be installing them unless we're trying to resolve an issue. 

Two things I found frustrating about this:

1. I took the time to read the KB article and test the patch on a generic SQL instance.  Nothing I read or saw would have led me to believe that there would be a risk of this happening.
2. I did warn the customer about this patch, specifically because Microsoft took the time to mention there were some known issues (just not the one we ran into).  Even so, they should have rolled it out a little more cautiously.

The customer panicked when they saw the message that said the Master database had to be restored.  That just has "bad day" written all over it.  Fortunately I was able to fix it without having to restore anything.

 

@Verne: It does work.  Two things you should know about adding the trace flag:

1. I had to type in the "-T902" flag manually.  I had originally pasted the value in from the webpage at http://support.microsoft.com/kb/2163980, but that used a "long dash", not a "hyphen", so SQL Server ignored the value when it started.
2. The parameters in the startup string are delimted by semicolons.  The KB article does not mention this.  So you should add a semicolon the end of the string, and add -T902. You can verify the SQL instance has started with these parameters by checking the event log. Look for a 17110 event in the Application log.

The -T902 trace flag prevents scripts from running when SQL starts, which will give you the opportunity to connect via SSMS and run the TSQL commands to enable Agent XPs.

 

Answer 5

 

It’s 1:47AM and I just got done fixing a problem on a customer's production SQL Server.  Normally SQL Server is not updated by windows updates, but a recent security patch for SQL (KB2494088) was released this week.  The network team installed it along with other patches, and it “appeared" to kill the master database of their most important SQL instance.   

We have fixed the problem (so this is just an FYI).  Just be aware that this potential issue can arise and the events in the Application log are quite alarming.  It turns out the fix is pretty straightforward because this was a known issue for patching SQL instances of a certain build and fortunately there was a workaround.  I was able to correct the problem and allow the upgrade to finish, and everything seems fine now.  You can head this off by configuring the SQL Server to allow “Agent XPs” before installing the hotfix. 

 

Per the KB article KB2163980, this is a known issue for SQL Server instances that:

 ·         Are running SQL 2008 R2 RTM

 ·         Have a “UCP” database – the Utility Control Point that monitors SQL Server performance

 

http://support.microsoft.com/kb/2163980

 

 Note that if you read the KB article for the hotfix (KB2494088), this UCP problem is NOT listed as a “known issue”.  But I’m sure Microsoft is going to catch a lot of heat for this over the next week or two.  It’s really unusual for them to roll out a SQL Server patch as a Windows update, and I’m sure it caught a lot of DBAs by surprise.

 

Did you get any option to uninstall this patch?

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Seeing as it updates the schema of system databases, I don't see how it could be uninstalled.  Anyway, I was able to fix it.

Answer 6

Seeing as it updates the schema of system databases, I don't see how it could be uninstalled.  Anyway, I was able to fix it.

Okay. Let me try to repro the issue and see what I can do. Thanks for raising the flag.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 7

The semicolon and the hyphen I found out already, when checking the logfile I saw that there was a space before the hyphen. Removed that one and I could fix the problem.

---

Verne

Answer 8

Don't you? 

Yes.  We have Dev, 2 x Test (internal and external) and Prod environment and we are only a business of 14 staff! 

Our whole business model relies on our website so I never let ANY changes into production until it has been through DEV and the 2 test environments.

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Answer 9

Don't you? 

Yes.  We have Dev, 2 x Test (internal and external) and Prod environment and we are only a business of 14 staff! 

Our whole business model relies on our website so I never let ANY changes into production until it has been through DEV and the 2 test environments.

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

I was teasing you, but you're right, and some good will come of this.  My role is just as a consultant. I have the job of guiding the customer toward best practices (or at least, better practices).  The customer has decided to implement a test platform (we'll be using NetApp Flexclone to test against a near-production environment without actually impacting production).  All it costs is time, money, and forethought.  We'll get there.

Answer 10

:-) 

BTW, thanks for sharing this (I should have said that earlier).  I have actually sent a copy of this to our hosting partner making it very clear that they are NOT to implement this under any circumstances.  At least I know who to call if they do......

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Answer 11

Just to update the thread that kb http://support.microsoft.com/kb/2494088 has been updated to list this as a known issue.

Thanks everyone.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 12

 

@Verne: It does work.  Two things you should know about adding the trace flag:

1. I had to type in the "-T902" flag manually.  I had originally pasted the value in from the webpage at http://support.microsoft.com/kb/2163980, but that used a "long dash", not a "hyphen", so SQL Server ignored the value when it started.
2. The parameters in the startup string are delimted by semicolons.  The KB article does not mention this.  So you should add a semicolon the end of the string, and add -T902. You can verify the SQL instance has started with these parameters by checking the event log. Look for a 17110 event in the Application log.

The -T902 trace flag prevents scripts from running when SQL starts, which will give you the opportunity to connect via SSMS and run the TSQL commands to enable Agent XPs.

 

Just to update the thread... I have raised request to modify the KB and its revised today. both points, semicolon and "long dash" has been taken care.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 13

I'm stymied. Leaving aside my utter frustration that this was applied through automatic Window Update, and has resulted in a totally borked development week, this fix is not working.

Before I found this thread, I found another that suggested uninstalling the update as a first step, which I did. When that path didn't fix things--because I later found out, the master db structure was changed--I found this. So,  here's where I am.

1. I restored from a restore I made before uninstalling the update.
2. Check in updates, and it was back.
3. When thru the steps in http://support.microsoft.com/kb/2163980.
4. Note: I had to change the order of the statements in step 5 to 'allow updates' first. I got an error otherwise that updates weren't allowed.
5. When I had removed the T902 parameter, restarted the service and tried to connect to the instance I get the same error I was getting originally (is the problem that I'm using a named instance instead of the default?):

TITLE: Connect to Server
------------------------------

Cannot connect to server\namedinstance.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

Answer 14

I'm stymied. Leaving aside my utter frustration that this was applied through automatic Window Update, and has resulted in a totally borked development week, this fix is not working.

Before I found this thread, I found another that suggested uninstalling the update as a first step, which I did. When that path didn't fix things--because I later found out, the master db structure was changed--I found this. So,  here's where I am.

1. I restored from a restore I made before uninstalling the update.
2. Check in updates, and it was back.
3. When thru the steps in http://support.microsoft.com/kb/2163980.
4. Note: I had to change the order of the statements in step 5 to 'allow updates' first. I got an error otherwise that updates weren't allowed.
5. When I had removed the T902 parameter, restarted the service and tried to connect to the instance I get the same error I was getting originally (is the problem that I'm using a named instance instead of the default?):

TITLE: Connect to Server
------------------------------

Cannot connect to server\namedinstance.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

"Could not open a connection to SQL Server" means that it is unable to connect to SQL Server. If you are hitting into the KB known issue then you would see login failed for user error.
We need to do basic troubleshooting like "Can you please make sure SQL Service is running?"

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 15

Just to update the thread that kb http://support.microsoft.com/kb/2494088 has been updated to list this as a known issue.

Thanks everyone.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Thanks so much, Balmukund!  That was the only thing I was really hoping for.  I appreciate the time and attention you and your colleagues put into this forum, and it is one of my most valuable research tools as a consultant.  Thanks again.

Answer 16

Hi All

We too are having problems with the workaround

Basically this is a SBS 2011 box and only SharePoint instance is affected.

While running step 5 mentioned on KB 2163980 we are getting the following error

 

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 5808, Level 16, State 1, Line 3

Ad hoc update to system catalogs is not supported.

 

Please do let us know how to troubleshoot the above mentioned.

 

Thanks

Inbay

Answer 17

Run below to get rid of "Ad hoc update to system catalogs is not supported." error

sp_configure 'allow updates', 0
reconfigure with override

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 18

Hi Balmukund

Ran the script as listed below

 

EXEC sp_configure 'show advanced', 1;

RECONFIGURE with override;

EXEC sp_configure 'allow updates', 0;

RECONFIGURE;

EXEC sp_configure 'Agent XPs', 1;

RECONFIGURE;

GO

 

And got the following as the result

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

 

But when i remove the -T902 trace flag Sharepoint instance is failing again with the same error message.

 

Please do let me know if you have any further suggestions

 

Thanks

Inbay

Answer 19

Hi All

Managed to rectify the issue with the assistance from Microsft SQL engineers and it was nothing to do with the mentioned hotfix though it suddenly appeard soon after applying the hofix and displayed the exact symptoms.

Regards

Inbay

Answer 20

Hi folks,

 

I had this same issue for a long time and the only way to solve it was removing the 2008 r2 sp1 and without it i was able to install the pact and after that the sp1 was installed sucessufuly.

Answer 21

Try that same (T902) trick and install 2494086 instead. That will restore functionality. Then you can proceed from there. That's what got me back up and running.