SQL 2012 Intermittant problem Msg 33094, Level 16, State 1, Line 1 An error occurred during Service Master Key decryption

Hi everyone,

2 SQL 2012 Standard Edition servers in a VM environment. SQL2 (OLTP) and SQL3 (DATAWAREHOUSE).

Let me run you through the situation.
Error above, with some other errors as well, has become an issue since the weekend.
The problem is intermittent, but can be repeated.

This happens almost every time we use a linked server and connect to views.
It has started happening after a windows update and a server restart last weekend.

Now, it seems to me like a sql statement optimising issue.

FROM OLTP.Membership.dbo.Companies

We can call the following statement in SQL03 where the "OLTP" component is a linked server back to SQL02 where the Membership DB resides. The Companies table isn't a table it is a view. (I am not impressed either with the naming). Now comes the tricky bit.
The Companies view has in it's From statement references to both data on SQL02 (ie a local call) and via a linked server connection to SQL03 as well.

I think that suddenly the SQL Query Optimiser is having issues around the "double hop" Linked Server calls. I don't know how to prove or disprove that.

Anyway, if when I am executing the statement in SSMS I can execute the query say 4 times and get the 33094 error, and 2 others...

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'Membership.dbo.Companies' because of binding errors.
Msg 33094, Level 16, State 1, Procedure Members, Line 57
An error occurred during Service Master Key decryption

On the 5th and 6th refresh, I may get only the 4413 and 33094 errors only.
On the 7th refresh, I just get the 33094 error.
On the 8th time say...bingo, I get my data.
On the 9th + time...data is good.

Now, that is fine running statements by hand in SSMS, when I have calls in apps, or in SSIS packages, I get failure because it craps out on the first attempt.

Steps to rectify so far.
1. Regenerate Service Master Key on SQL02 and SQL03. Same problem.
2. Tried creating a new linked server. Same problem.
3. Restarted both SQL services, Same problem.
4. Restarted both Windows machines. Same problem.

Is there anything you all can suggest. I can't see why it would be OK Friday and then not on Monday.

Frustratedly yours..

LVL 12
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
I would check the health of my host system first, especially RAM and network components.
Tony303Author Commented:
Yeah, the machines are VM's. The engineers have the 2 vm's as "close together as possible" and given the 2 servers "better NIC's", whatever that means. As for the RAM, yeah, the page life expectancy on 02 is not great, I may get them to assign more RAM to the VM. So you're thinking the decryption error is because the server hasn't enough RAM to get the job done first up?

ste5anSenior DeveloperCommented:
I had a more low-level check in mind. Cause I think it's caused by your host system and possible it's hardware.

Such intermittend problems are often caused by the underlying hardware. So a memcheck and a inspection of the NICs and switches and cables is imho an appropriate action.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Tony303Author Commented:
Roll back of the 6 patches installed at the weekend. Presto!!!
We plan to run each one in turn and see which particular one it is.

Will post the findings later.

Anthony PerkinsCommented:
The problem is intermittent, but can be repeated.
We had the same problem.  I created a do nothing loop, that very rarely succeeded more than 10 or 20 times before failing with that same error.

I think that suddenly the SQL Query Optimiser is having issues around the "double hop" Linked Server calls.
I don't believe that is the case.  It certainly was not our situation as for better or worse we use SQL Server Authentication, so double-hop does not come into play.

Roll back of the 6 patches installed at the weekend. Presto!!!
Yep.  After rolling them back and reapplying one by one we narrowed it down to KB3004375.  However when you install KB3000483 it also requires KB3004375.  So in a sense both are a problem.

I have been in contact for the last two weeks with Microsoft with this problem.

We actually updated Windows on around 30 SQL Servers.  Not all of them presented this problem.  Not sure if it is the version of the O/S, the version of SQL Server, VMWare/Hyper-V or something else.  One factor in common is that they are all named instances (and using VMWare).
Anthony PerkinsCommented:
Please confirm the patches involved were the same ones I reported in the previous comment, so that I can report back to Microsoft PSS appropriately.
Tony303Author Commented:
I am arranging an outage during a weeknight now. Our problem SQL server is a named instance too!!! My only named instance server.

Will post a finding as soon as I know.
Tony303Author Commented:
The problem is KB3031432 and the associated KB3004375 patch that get installed together.
Fault occurs and can be repeated.
Removed these patches. No Problem.
Installed all other patches. No Problem.

We have still these variables.
1.  The SQL Instance that falters is a Named Instance, our only Named Instance.
2.  The SQL Instance that falters is on RTM 2012 SQL. The other non Named Instance servers are SP1.
3.  The OS on the machine that falters is Windows Server 2012 Standard 6.2.9200 Build 9200 (Although, this machine has 2 instances, 1 at RTM and the other non named instance at SP1, there are no linked servers between these 2 SQL's) vs. Windows Server 2012 R2 Standard 6.3.9600 Build 9600 on the machine that is OK.

I hope this helps, but I suspect not.

Anthony PerkinsCommented:
It is interesting that you found a different patch than I did KB3031432  vs. KB3000483  and that they both require KB3004375

In our case, it was only SQL Server 2012 (2008-R2 and 2005 were OK, but may not need these patches either).  In our case, they are Enterprise Edition one of them RTM and the other two SP1 and all Windows 2012 Enterprise.
Tony303Author Commented:
I have been told by the engineer it is common to see a patch number change over time as that patch "evolves". I'm not so sure, what would I know, I'm just a data monkey.

I suspect we won't know the true answer. You have nothing in common, except SQL 2012 and Windows 2012 and VMware.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony303Author Commented:
I guess we'll never know. I've moved organisations!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.