Security Update MS14-066 causes major performance problems in Microsoft Access / SQL Server applications

http://www.microsoft.com/en-us/download/details.aspx?id=44618

This week Microsoft released a number of security updates this week to patch an issue with schannel as described in this article:  When the update is installed to a server running Microsoft SQL Server (So far, confirmed as issue with SQL Server 2008 R2, SQL Server 2012, SQL Server 2014) client applications that access the database via ODBC such as Microsoft Access clients pointing to SQL Tables encounter a major performance hit.

https://technet.microsoft.com/library/security/MS14-066

Our customers are reporting that this security update causes MAJOR performance problems in any Microsoft Access application with a SQL Server backend (any version).  For example, a simple operation such as clicking from one line of an order to another (without performing ANY data updates) can take from 5 to 15 seconds!  For users having to update hundreds of lines of orders, the application becomes nearly unusable – an activity that used to take 5 minutes could take hours.to complete.

Please, if you have not installed this update yet – DO NOT INSTALL IT to the SQL Server machine (it can be installed to clients or other servers).

If you have installed the update and are experiencing this issue, please remove the following specific update from the computer running Microsoft SQL Server to get your system back to performing normally:

Click Start, then type: “Update”, click “View installed updates”

clip_image002

In the list of updates scroll down until you find the list of updates installed recently (your exact date may differ).  Select “Security Update for Microsoft Windows (KB2992611)” and click Uninstall.  After a reboot application performance restores to normal.

image

As of November 13th at 11:49 AM we know that if you select each one of these under this group, and uninstall, then reboot that performance returns to normal.

KB3010788, KB3008627, KB3006226, KB3005607, KB3003743, KB3003057, KB3002885, KB2993958, KB2992611, KB2991963, KB2978120

Update: as of 2014-11-13 3:20PM our most recent test looks like it may only be KB2992611 as the root cause of this performance problem.  The client machine does not appear to have to have the update removed.

Update: as of 2014-11-13 5:00PM we have a case open with Microsoft to figure out the underlying performance problem.  Obviously we want our customers to be able to install security updates, but it can’t be at the expense of being able to use the software that runs your company.  I’ll post additional updates when we hear back from the Windows Team.

Update: as of 2014-11-14 1:30PM: Update from Microsoft on our open case: The SQL Team is working directly with the Windows team and have been able to reproduce performance issues.  They’ve created a specific tool to gather performance stats related to the issue and are working with one of my techs to gather the stats in our lab environment with both the patch installed and with it removed.

Update as of 2014-11-17 2:00 PM: Microsoft has completed data capture of several traces of client to server communication with the patch applied and removed.  Status: Ball in Microsoft’s court.  Waiting on analysis.

Update: Per the user comments below, this performance issue may affect any client (not just MSAccess clients) that happen to use the built-into-Windows SQL Server Driver: 

image

Put differently, you may be affected if your connection string looks like this:

Driver={SQL Server};Server=ComputerNameRunningSQL;Database=SQLDBName;Uid=SQLAuthUser;
Pwd=SQLAuthPass;

Or like this:

Driver={SQL Server};Server=ComputerNameRunningSQL;Database=SQLDBName;
Trusted_Connection=Yes;

Update: 2014-11-28 1PM – Just received this notification e-mail from Microsoft:

The following bulletins have undergone a major revision increment.

* MS14-066 – Critical

Bulletin Information:

=====================

MS14-066 – Critical

https://technet.microsoft.com/library/security/ms14-066

– Reason for Revision: V2.0 (November 18, 2014): Bulletin revised to announce the reoffering of the 2992611 update to systems running Windows Server 2008 R2 and Windows Server 2012. The reoffering addresses known issues that a small number of customers experienced with the new TLS cipher suites that were included in the original release. Customers running Windows Server 2008 R2 or Windows Server 2012 who installed the 2992611 update prior to the November 18 reoffering should reapply the update. See Microsoft Knowledge Base Article 2992611 for more information.

– Originally posted: November 11, 2014
– Updated: November 18, 2014
– Bulletin Severity Rating: Critical
– Version: 2.0

We have not tested this yet, but will shortly. 

UPDATE: 2014-11-18 1:50PM: Part 2 of the Rev 2 patch worked!

In my example, I went back to the URL: https://technet.microsoft.com/library/security/ms14-068, selected the Windows Server 2008 R2 patch (for the test machine I was using this time I needed that one), then when I clicked download I now see a second option:
 image

Windows6.1-KB3018238-x64.msu

On installing that 2nd file listed the problem was resolved.  Performance is back to normal for this particular Windows Server 2008 R2 server with SQL installed!

UPDATE: 2014-12-01: Windows 7 SP1 No patch available.

If you are running SQL Server on your laptop for example to demo your application or as a developer we’ve discovered that there is no KB3018238 available.  Attempting to install that KB on Windows 7 simply results in an error: The update is not applicable to your computer:

image

I’ve contacted Microsoft under my original case on this issue. Their reply so far: “KB3018238 is not available in Windows 7.” – Yes, that would be the point of my call…

So for those of you who do development on Windows 7, or those of you who are salespeople with copies of your company’s ERP database installed locally are in the same situation – either do not install KB2992611 and have a working application, or do not install it, have a working application but be at risk.

Fixing Major Hyper-V Virtual Machine Performance Problem on Server 2012 R2

Recently I implemented a brand new, very powerful server with solid state disks running Windows Server 2012 R2.  But something shocking happened when I did: Every VM that I moved to that new server performed so poorly that users could not stand to use the machine. 

File transfers to or from the server were also taking an unacceptably long time to complete.

After some searching we figured out that the “Virtual Machine Queues” advanced driver option of the various network adapters in the server had defaulted to True. 

Changing these settings to Disabled instantly restored performance of the server back to normal / acceptable levels and user complaints instantly went away.

Here are the step-by-step instructions to changing this setting on Windows Server 2012 R2 machine’s network adapters:

Open the Network Connections panel (right-click start, choose run… type: ncpa.cpl hit enter):

image

Right-click a network adapter and choose properties.

image

Click the Configure… button

Click Advanced, then scroll down to Virtual Machine Queues and change the value from Enabled to Disabled:

image

Here it is again on an Intel NIC:

clip_image002

Changing this setting for me at least made a dramatic improvement in network throughput and in Microsoft Hyper-V guest OS performance.

New Text Document option missing from right-click context menu

For some reason the “New Text Document” option went missing from my right-click context sensitive menu in Windows Explorer.  Here’s how I went about fixing it.

First of all, what you need to know about the right-click menus is that they are mostly maintained in the registry under the HKCR key.

In the case of .txt files, you’re looking for HKCR\.txt:

image

When the context menu option to create new text documents was missing, on my machine there weren’t any sub-keys under this .txt key.

To find the correct values I simply connected to another computer where the key was working and then copied the values from there, but to save you time, here are the values that worked for me:

image

There should be a Key “ShellNew” under the .txt key containing these values:

Expandable String Value: “ItemName” with value: @%SystemRoot%\system32\notepad.exe,-470

String Value: “NullFile” without any configuration setting

image

There should be a Key: “PersistentHandler” also under .txt

with default value set to: {5e941d80-bf96-11cd-b579-08002b30bfeb}

Once those items are in place, the right-click, new, Text Document option should re-appear on the explorer context sensitive menu.

Visio Stencil for Drobo B1200i

After spending way too much time searching for a Stencil for the Drobo B1200i I finally gave in and created one myself.

image

You can download a copy of the Drobo.vss file here: http://1drv.ms/1cHycKv

This stencil includes a Front View of the Drobo B1200i Rack Mount iSCSI disk array with cover on, and a back view also with Fan cover on.
image

Both the Front and Back views includes these Shape Data Elements:

image

On the back of the unit I’ve included connection points on each of the Power Supplies, and each Network Adapter port:
image

If you find these shapes useful, leave a comment below.

How to record greetings for Avaya ipOffice 500 voicemail system

Easiest way for best possible quality:

1. Go find a Windows XP or Server 2003 machine – you know you have one somewhere – and copy this file C:\Windows\System32\sndrec32.exe and copy it to your Windows 7 or 8 machines.

2. Open the XP / 2003 Sound Recorder App:

image

3. Click File, Properties:
image

4. Click Convert Now…

image

5. Change your settings to

Format: PCM (that’s the default, so nothing to change here)

Attributes: 8.000 kHz, 16 Bit, Mono

image

To save yourself some time, after you make that selection do like I did and click the Save As… button and give it an easy to remember name such as “Avaya IP Office”.  Next time you can just select that item from the list instead of picking from the long list of possible attributes.

Record your greeting

At this point you’re recording DIRECTLY into the correct format.  Doing this provides a far superior recording in my opinion to recording in some other format and trying to convert it down to this format.

Remember to change the setting EVERY TIME you record a new recording

This setting doesn’t “Stick” so remember every time you save the file and create a new one to go back into the properties and change it again to these settings.

Recording Tip: Avoid “Popping” sound with a piece of paper

The biggest problem I had with recording at this sampling rate is that “pop” sounds are exaggerated even more than normal.

The easiest way to avoid this problem: hold a piece of paper between your mouth and the microphone.  In my case I just held the mic and the paper with my left hand – holding the paper between index and thumb, and the mic itself between index and middle fingers (it was a small, high-quality Sony lapel mic I purchased a few years ago).

Tip #2: Take advantage of multiple files

It can be a lot of work to record an entire introduction as a single “work”.  Instead, take advantage of the fact that the system will seamlessly play one file after the other and break it up into discrete different files:

image

In this screen shot of the Avaya Voicemail Pro Client day routing you can see I’ve got 5 files listed.  I saved them with number prefixes while I was recording them to make it easy to selected, and put the first part of what I actually said directly in the filename to make it easy to find later.

How to edit an Outlook 2010 contact email address

Lately with every version of Office Microsoft seems to be making it more and more difficult to perform tasks that were previously simple.

Take for example the simple editing of an email address.  Say you have a typo in the e-mail address that you want to fix:
image

Your instinct is to click at the cursor position where you want to fix the address:

image

and you expect an I bar to appear to allow you to edit the address.  That doesn’t work – you notice that the cursor is an arrow, so you think OK maybe it works like other Office Apps: Excel, Word – maybe I have to tell it I want to “Edit” the cell.  There’s a keyboard shortcut of F2 for that in other Office apps so you try that and nothing happens.

Then you realize you can double-click the field.

image

So you try to click on the e-mail address here, but realize that just opens a new e-mail.  But that is NOT what you want to do.  By now you’re getting pretty frustrated.  You just want to edit the damn e-mail field and type ONE extra character to fix your typo.

So now you resort to Google and start searching the Internet.  Eventually you find the solution:

image

Click the little drop down menu on the right side of the dialog, choose Outlook Properties.

FINALLY:

image

A screen appears that allows you to edit the darn e-mail address.  But unlike previous Outlook versions, after editing the e-mail address it doesn’t automatically also update the display name, so now you have to make your correction in two places – on both the E-mail address line AND on the Display name line.

In Outlook 2013 this is made even worse. 

I don’t understand what happened to Microsoft’s usability labs.  They used to pride themselves on having real end users spend hours using the software and incorporating that feedback into products.  In recent years though product usability seems to have taken a back seat to other objectives, some of which are helpful, but some of which make previously simple tasks ORDERS OF MAGNITUDE more difficult and LESS DISCOVERABLE than ever before.

What used to be 1 click, type a character is now:

1. Double click e-mail address.

2. Click on Drop down.

3. Click on E-Mail Properties

4. Click in E-Mail address field and make correction.

5. Click in Display name field and make correction.

6. Click OK

Why do simple things have to be so much harder to do?

rsReportServerNotActivated

Recently, I had to downgrade from SQL Server Enterprise Edition to Standard.

When I re-installed SQL Standard with the generic reporting services database, I restored the encryption key first, then restored the Enterprise edition reporting services database over the Standard Edition one.

When I tried to access the reporting services web site I got a rsReportServerNotActivated message and a click here message that brought up these search terms:

products ee transform aspx EvtSrc Microsoft ReportingServices Diagnostics Utilities ErrorStrings EvtID rsReportServerNotActivated ProdName Microsoft SQL Server Reporting Services ProdVer 11 0 3128 0

The way I solved this problem was to restore the Encryption Key again:

image

Now when I did that I ended up with a second error relating to having too many keys and that not being supported in Standard Edition.  The exact error was:

The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

To solve that problem, you have to delete one or more of the keys:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe –l

imageThen run this command to delete all but the last key (which should be the one you restored):

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe -r 132b8974-1503-4682-ae12-a1d836e3526e

Substitute the keys from your first command – but remember to keep the last key.

When done, run the first command again

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe –l

And make sure that only one key is listed.

Downgrading Reporting Services from SQL 2012 Enterprise to SQL 2012 Standard

Companies like mine that provide customers SQL Server access under a Service Provider License Agreement (SPLA) saw our costs go up almost 10,000% or more in 2013.  What used to cost under $20 per user per month for a SQL Enterprise license suddenly jumped to the equivalent of hundreds of dollars per user per month.  This required that we immediately downgrade dozens of customers from SQL Enterprise to SQL Standard.

Fortunately for me, we weren’t actually taking advantage of any SQL Enterprise-Edition specific datatypes so I didn’t have any structural database issues to deal with.  Of course Microsoft does NOT provide a way to gracefully downgrade an existing instance, so my steps had to include a complete un-install of SQL Server Enterprise Database Engine and Reporting Services, and re-install of those components.  I did not have Analysis services installed or that would have had to have been uninstalled as well.  (The other tools could remain – reducing the time for this process by about an hour).

If you read other blog posts about things to do first you’ll see things like checking the server’s settings for AWE, memory etc.  In my case I all really cared about were the following:

  • SQL Users and their permissions
  • Maintenance Plans
  • SQL Server Agent Jobs
  • Reporting Services databases and subscriptions – there were a lot of them.

For SQL Users, I created a script to re-create those by right-clicking each user and scripting create to a new window.

On this particular server, no specific securables were defined.  if you have securables defined, then you’ll also need to script those out.

SQL Auth users get assigned random passwords by this process.  If you know the passwords for those accounts, you’ll need to script them out.

I DID NOT TRY to restore the Enterprise Edition Master / Model / MSDB databases that would have allowed me to avoid this step.  I just figure that they are incompatible between editions so I didn’t even try.

Since SSMS doesn’t seem to include an easy way to script out maintenance plans, I used a 3rd party tool to do that step: Toad for SQL Server – formerly from Quest software, now part of the Dell family of products.  (By the way, if you haven’t moved to using Toad for SQL Server, I STONGLY recommend it.  It’s worth the license price in terms of the productivity you’ll gain.)

In Toad for SQL Server, you double-click the Maintenance Plan, then click the Export button:

image

Later, after you’ve restored your databases, you can create a “new” maintenance plan then click the “Import” button from the toolbar to import the file you previously exported.  All dimensions of the maintenance plan get re-created this way including the schedules.  This is a massive time saver.

In terms of SQL Server Reporting Services, here’s what I did:

  • Open Reporting Services Configuration Manager, Encryption Keys, Click Backup:
    image
  • Export the key with a .snk extension to make it easier to restore later:
    image
  • Backup the ReportServer and ReportServerTempDB databases
  • Stop Reporting Services
  • Uninstall SQL Server Enterprise Edition
  • RENAME THE DATABASES BEFORE YOU RUN SQL Standard setup or setup WILL FAIL
  • Install SQL Server Standard Edition

At this point I backed up the new ReportingServer databases just in case I needed them.

  • Restore the original Enterprise Edition ReportServer and ReportServerTempDB databases, choosing the option to overwrite the original.
  • Open the Reporting Services Configuration Manager
  • Restore the key you exported in the previous step.
  • Try to start the reporting server and open the site.  If you get an error about rsReportServerNotActivated then restore the key again – you may have restored the key in the wrong order.
  • If you get an error about The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) then you need to run a command line tool to list the keys and delete the oldest ones:

image

Run this command to list the Keys:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe –l

Then run this command to delete all but the last key (which should be the one you restored):

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe -r 132b8974-1503-4682-ae12-a1d836e3526e

Substitute the keys from your first command – but remember to keep the last key.

When done, run the first command again

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe –l

And make sure that only one key is listed.

When done, refresh your reporting services web site and it should load successfully with everything in tact – Data sources, reports, subscriptions, etc.

CTRL+s stopped working on my computer–turns out Skype was the reason

When working in Office programs such as Microsoft Word or Microsoft Excel I frequently press CTRL+S to save my progress.  A few months ago that stopped working!  I had to manually click the Save button to be able to save my work.

I figured some other application was grabbing the keyboard shortcut from windows so I set out to find which application was intercepting the CTRL+S keyboard shortcut, or how to find the application grabbing keyboard input.

After quite a bit of searching I finally discovered a free application called “Windows Hotkey Explorer” available here: http://hkcmdr.anymania.com/index.html that when loaded shows you a list of all the applications that have hotkeys mapped:

image

This application showed me that CTRL+S was in fact being intercepted by Skype!

image

Sure enough, in Skype’s Advanced options for Hotkeys there is an option: “Take a snapshot during video calls” with a default hotkey set of CTRL+S.  Unchecking the box and clicking save immediately restored my CTRL+S shortcut in all other applications!

Technical note: For some reason after removing the hotkey, clicking refresh in the Windows Hotkey Explorer application caused the application to stop responding.  Subsequent loads of the application also went to not responding.  Still, it helped me figure out the problem.

Drobo B1200i cannot be used on same iSCSI servers as Dell EqualLogic SAN using Dell’s MPIO

Drobo’s B1200i is a great value – lots of flexible storage at a great price.  Performance is certainly good enough for backup and that was my intended use.  I love the idea of being able to remove lower capacity disks and replace them with higher capacity disks in future as I need additional storage without having to destroy and re-create a RAID array manually.

So I purchased two 27TB units (about 22 TB Usable after redundancy) for use as a destination for  Veeam to backup my Hyper-V network.

When it came time to implement the unit however I immediately hit a major snag which makes the Drobo unusable.  My production SAN for Hyper-V is based on Dell EqualLogic SAN products.  In the iSCSI Initiator you see a custom Dell EqualLogic MPIO tab with all the production LUNs:

image

Prior to making this Drobo purchase, I was under the impression that iSCSI was a standard no different than USB or eSATA and that it wouldn’t matter if I mixed vendors or not.  Apparently I was mistaken.

While you CAN add the Drobo Target to the Targets tab of the iSCSI Initiator:
image

The disk volumes represented by these LUNs never actually appear in the host operating system Disk Management console, meaning that you cannot actually use any of the storage on the Drobo on the same cluster node that is also able to communicate with the Dell EqualLogic equipment.

image

A call to Dell’s tech support basically confirms this.  If you have the Dell High Availability Hit Kit installed, you cannot use other SAN vendor’s equipment.

There is a work around available that according to Dell would work: Uninstall Dell’s hit kit and go back to using the base Microsoft stack.  However doing that will lower the performance of your Dell EqualLogic SAN so that solution isn’t really a solution at all.

Another option: Since this is a Hyper-V environment, I could spin up a Hyper-V VM, and INSIDE THAT VM create a new iSCSI initiator that connects to the SAN fabric.  But doing that won’t provide optimal performance and also risks causing different issues.  I would either have to allow my iSCSI NICs to also be bound to a Hyper-V machine (which I really don’t want to try in a production environment) or I’d have to purchase another physical multi-port NIC, install that into the HyperV host, plug it physically into the iSCSI fabric and assign all of those NICs exclusively to this one VM.

In the end I’ve decided to purchase a new 1U server install my Veeam backup software on that dedicated node and “not” install the Dell Hit Kit on that machine since that new node won’t be a member of the cluster.

All in all, this was very disappointing.  If you’re in a situation like mine, where you’re looking for very large, very affordable storage to backup your SAN, the Drobo is still a great solution, but only if you have a dedicated physical server that you can assign to the task of processing the backups.

If any of you have tried some of these other solutions and got them to work please feel free to post your comments below.