asked on 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.
SELECT *
FROM OLTP.Membership.dbo.Compan
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'
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..
Tony.
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.
SELECT *
FROM OLTP.Membership.dbo.Compan
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'
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..
Tony.
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
Tony303
I would check the health of my host system first, especially RAM and network components.
ASKER
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?
Thanks
Tony
Thanks
Tony
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.
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.
ASKER
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.
T
We plan to run each one in turn and see which particular one it is.
Will post the findings later.
T
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).
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).
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.
ASKER
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.
Will post a finding as soon as I know.
ASKER
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.
T
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.
T
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I guess we'll never know. I've moved organisations!!