Enabling Transparent Data Encryption causes query error - by patrickk72

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 683966 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/12/2011 9:58:49 AM
Access Restriction Public


I've enabled Transparent Data Encryption on a SQL 2008 R2 database.  When checking some tables in the database after enabling, I get the following error:

Msg 5180, Level 22, State 1, Line 2
Could not open File Control Bank (FCB) for invalid file ID 0 in database 'tempdb'. Verify the file location. Execute DBCC CHECKDB.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I've run DBCC CHECKDB against tempdb and the database where encryption has been turned on.  It finds no errors.

I also receive this error when querying another table:
Location:	 AppendOnlyVerStoreMgr.cpp:777
Expression:	 0
SPID:		 67
Process ID:	 7688
Description:	 Cannot locate version record and page is not allocated. Status = 3
Msg 3624, Level 20, State 1, Line 2
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

There are many entries in the SQL Logs with stack dump information.
Sign in to post a comment.
Posted by Microsoft on 12/5/2011 at 2:40 PM
As you noted, there is a workaround in the KB article, and the bug has been fixed in SQL Server 2008 R2 Cumulative Update 4.

Thanks for your report,
Kevin Farlee
Posted by Microsoft on 10/14/2011 at 10:14 AM
Thanks Patrik for following up. We will evaluate if we can fix this problem without needing the workaround.
Posted by patrickk72 on 9/15/2011 at 2:36 PM
I found the answer to this bug. Even though I wasn't seeing the same errors at first, I tested encrypting databases on separate sql instances and found this Microsoft bug fix:

FIX: Error message when you enable TDE on a database that has the snapshot isolation enabled in SQL Server 2008 or in SQL Server 2008 R2


I found this article when I received this error:

Msg 823, Level 24, State 2, Line 1 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000df130000 in file '<Driver>:\<folder>...\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

By turning off READ_COMMITTED_SNAPSHOT on the database before encrypting, I received no errors after encrypting.