Post reply

Connection and Querying TDE Databases

  • May 26, 2010 at 8:29 am

    #235552

    I need to make use of the SQL Server 2008 TDE feature. I have performed the following steps.

    1) Created a Master Key

    2) Created a certificate in the Master database

    3) Created a database encryption key or DEK

    4) Altered the database to set Encryption ON

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'

    CREATE CERTIFICATE db1 WITH SUBJECT = 'DB1 Certificate'

    USE SomeDB

    CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE db1

    ALTER DATABASE SomeDB SET ENCRYPTION ON

    SELECT name, database_id, create_date, is_encrypted FROM SYS.databases

    comes back with an encryption_state of 3 (encrypted) and a percent_complete of 0 which indicates the encryption is complete according to the documentation I have seen.

    If I connect to the serve with sa and issue

    USE SomeDB

    SELECT * FROM SomeTable

    I get an error that indicates an OS level error.

    Msg 823, Level 24, State 2, Line 2

    The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000d60000 in file 'D:\Q2\MSSQL\SystemDatabases\Temp\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.

    I have run dbcc checkDB on all of the user and system databases and there are no errors.

    Before encypting the DB, I have no issues querying data. This error only occurs after the encyption implemented. Am I missing a step here? This server has been stress tested successfully so I am confident there are no OS or hardware issues.

    Am I missing a step?

    Thanks in advance.

    Daniel

  • May 27, 2010 at 6:47 am

    #1173282

    Is anyone using TDE?

  • June 8, 2010 at 8:32 am

    #1177504

    In the event anyone else runs into this...

    Apparently there is an issue with the tempdb becoming corrupted as soon as the database encryption is set to ON. Simply restarting the service (recreating the tempdb) is all that is required to resolve the issue.

    I have verified that the condition is present in MS SQL Server 2008 and 2008 Release 2. Not certain if it goes back farther.

  • February 28, 2012 at 12:19 pm

    #1452773

    There's a fix available for it:-

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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply