none
TDE Side effect - why is TempDB database still encrypted ?

    Question

  • Hello Everyone,

    SQL Server encrypts the tempdb database automatically when a user database is encrypted using TDE. Looks good till this point. But when encryption is disabled for all user databases in question, tempdb still remain encrypted. I even tried bouncing the service but no luck. Does it make sense to you to have tempdb database encrypted when none of your user database is encrypted anymore ? Yes, It gives you flexibility to turn on and off encryption for user DBs on the fly but does it justify performance degradation ? Is there is way to completely clean up TDE stuff ?

    Appreciate your response. Thank you.

    Thursday, January 12, 2017 10:31 AM

Answers

  • Thank you everyone for all your efforts and reporting this bug. We did some initial investigation on this issue and found it to be reporting bug where the DMV column (sys.databases.is_encrypted) doesn't get updated for tempdb. While we will be looking to fix this in upcoming servicing release but it is benign issue which shouldn't impact the performance or resource consumption except for inaccurate reporting. I will also encourage you all to read our blog post from Bob on this topic for better understanding

    https://blogs.msdn.microsoft.com/sql_server_team/sql-server-mysteries-the-case-of-tde-and-permanent-tempdb-encryption/


    Parikshit


    Saturday, January 14, 2017 7:37 AM
  • I had a Azure VM with SQL Server 2016 on it and performed the test on it. Here's the complete steps:

    Version details of the SQL Server 2016 that I performed my test on: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor) 

    1- Create the master key, certificate, database encryption key and enable encryption (TDE) on the test database.

    USE master;  
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
    go  
    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
    go  
    USE test;  
    GO  
    CREATE DATABASE ENCRYPTION KEY  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
    GO  
    ALTER DATABASE test  
    SET ENCRYPTION ON; 
    GO

    2- Using the following query, I confirm that [encryption_state] is 3 for [tempdb] and [test] databases.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    3- I turn TDE off for the [test] database using the following query:

    ALTER DATABASE test  
    SET ENCRYPTION OFF;

    4- When I execute the following query I see that [encryption_state] is 1 for the [test] database and 3 for the [tempdb].

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    5- After the restart of Database Engine service through SQL Server Configuration Manager, I execute the following query again and it returns only [test] database and the [encryption_state] is still 1. [tempdb] is not returned by the query anymore.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    6- When I execute the following query in this version of SQL Server 2016, it returns [temp].[is_encrypted] = 1

    SELECT is_encrypted, name FROM sys.databases;

    HOWEVER, when I perform the exactly same steps in SQL Server 2014 (+SP2), the query above returns [temp].[is_encrypted] = 0

    As I said before, this is clearly a version specific BUG. Perform the same steps on SQL Server 2014 + SP2, you'll get a different result as I've been advocating since yesterday and you get another result when executing the same set of commands on SQL Server 2016.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Marked as answer by Anil_Kumar_DBA Friday, January 13, 2017 10:40 AM
    Friday, January 13, 2017 9:32 AM

All replies

  • Hi Anil,

    How you verify encryption of tempdb? Do you use DMW sys.dm_database_encryption_keys?

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 11:47 AM
  • Thank you Sunil for your response.

    I checked with select is_encrypted, * from sys.databases. The DMV you mentioned returns info about certificate and keys. Am I supposed to do something different from what I am doing? 

    Thursday, January 12, 2017 12:01 PM
  • Hi Anil,

    This is by design. Look at the explanation given here by MS. Sounds good to me.

    https://connect.microsoft.com/SQLServer/feedback/details/566858/removing-tde-from-tempdb-should-not-require-a-restart

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 12:10 PM
  • Evidently the DEK remains for tempdb even after a restart.

    What happens when you run this query?

    SELECT
       dbs.name,
       keys.encryption_state,
       keys.percent_complete,
       keys.key_algorithm,
       keys.key_length
     FROM
       sys.dm_database_encryption_keys AS keys
       INNER JOIN sys.databases AS dbs ON keys.database_id = dbs.database_id

    Thursday, January 12, 2017 12:25 PM
  • Hello Anil_Kumar_DBA,

    In another reply of yours, you mentioned that the following query return a result.

    SELECT * FROM sys.dm_database_encryption_keys;

    If there's even only one database for which TDE is enabled, then tempdb will also be encrypted. So if you want tempdb to be unencrypted, then ensure that for following query does not return any row:

    SELECT * FROM sys.databases WHERE is_encrypted = 1 AND database_id > 4;

    If the query above returns any row, then turn off TDE for that database using the following query:

    ALTER DATABASE [database_name] SET ENCRYPTION OFF;

    And drop the encryption key from that database using:

    DROP DATABASE ENCRYPTION KEY;

    Now ensure that the following query does not return a result:

    SELECT * FROM sys.databases WHERE is_encrypted = 1 AND database_id > 4;

    If so, then restart the Database Engine service. Now the following query shouldn't be returning any row and your tempdb must be unencrypted:

    SELECT * FROM sys.dm_database_encryption_keys;


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 12:29 PM
  • Hello Anil_Kumar_DBA,

    In another reply of yours, you mentioned that the following query return a result.

    SELECT * FROM sys.dm_database_encryption_keys;

    If there's even only one database for which TDE is enabled, then tempdb will also be encrypted. So if you want tempdb to be unencrypted, then ensure that for following query does not return any row:

    SELECT * FROM sys.databases WHERE is_encrypted = 1 AND database_id > 4;

    If the query above returns any row, then turn off TDE for that database using the following query:

    ALTER DATABASE [database_name] SET ENCRYPTION OFF;

    And drop the encryption key from that database using:

    DROP DATABASE ENCRYPTION KEY;

    Now ensure that the following query does not return a result:

    SELECT * FROM sys.databases WHERE is_encrypted = 1 AND database_id > 4;

    If so, then restart the Database Engine service. Now the following query shouldn't be returning any row and your tempdb must be unencrypted:

    SELECT * FROM sys.dm_database_encryption_keys;


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Ekrem,

    tempdb can not be unencrypted even after disabling TDE from all user databases on that instance. 

    Take a look at : https://connect.microsoft.com/SQLServer/feedback/details/566858/removing-tde-from-tempdb-should-not-require-a-restart


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 12:41 PM
  • I have a test environment with SQL Server 2014 and according to my tests, I can remove the encryption from tempdb following the steps I mentioned.

    That user or you may have had that problem with another version of SQL Server, but it works in my test with the version on my test environment.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 12:45 PM
  • For Ekrem:

    Using the Query [SELECT * FROM sys.databases WHERE is_encrypted = 1 AND database_id > 4;] you just filtered out tempdb. database_id for tempdb is 2 so it will not come in with this query anyway.

    Please verify.

    Thanks,


    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Thursday, January 12, 2017 12:48 PM edit
    Thursday, January 12, 2017 12:47 PM
  • And that's the purpose obviously, because as long as I have an encrypted database, tempdb will be encrypted. I didn't want the OP to be confused, that's why I omitted the tempdb from the query resultset.

    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 12:49 PM
  • We have own way of looking the question.

    Original question is "why is TempDB database still encrypted" even after removing TDE from all other user databases. 

    Just suppressing the output is not the context here I guess.

    In your post you have mentioned as below which is not true at all. No need to restart and all funky stuff because it is not going to go away.

    "If so, then restart the Database Engine service. Now the following query shouldn't be returning any row and your tempdb must be unencrypted: "

    Anil,

    If you are happy with what you were looking for, please close the thread.

    Thank,


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 12:56 PM
  • OP's request is clear, he/she wants to turn encryption off for tempdb and everybody is sharing their thoughts. You may not agree with me, though, that does not make my reply "not true at all". We all are here to learn and share. That's just a cheeky expression by the way.

    My suggestion does not rely on a believe, it's a fact. Anybody can try and see the result and the test may yield diferent results in different test environments. If it doesn't work with someone's environment, then he/she shares the results and we can discuss the technical details as far as we can. In the end of the day, this is a technical forum, not a religious one.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 1:13 PM
  • Hello Anil_Kumar_DBA,

    During a further test, I realized that dropping the encryption key is not a must to set tempdb unencrypted. Just ensure that there's no any encrypted user database with TDE and then restart the database engine service and after the restart tempdb will be unencrypted. I use SQL Server2014 SP2 in my test environment.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 1:17 PM
  • Hello Everyone,

    Thank you for taking time to respond. Please give me few minutes to go through them.

    Appreciate your patience !!

    Thursday, January 12, 2017 2:42 PM
  • Hello Ekrem,

    Thank you for your responses !

    I have tried executing DROP DATABASE ENCRYPTION KEY - for user database when same was not encrypted, it worked fine. Since I wanted to remove encryption from tempdb, re-run the same statement again system database in question - I was greeted with below message.

    Msg 33102, Level 16, State 7, Line 16
    Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb', or 'resource' databases.

    In nutshell, There seems no way to get rid of tempdb encryption which doesn't look good to me as it has teh potential to cause performance issues. Is there anyhing that you would like to suggest me on this topic ?

    Thank you for your time !!

    Thursday, January 12, 2017 3:04 PM
  • Hello Ekrem,

    I have tried that and it didn't work. Microsoft says it is by design.

    Thank you.

    Thursday, January 12, 2017 3:06 PM
  • No way! Don't give up. I didn't say run DROP DATABASE ENCRYPTION KEY command in the "tempdb" database. You didn't follow my steps, that's why you get this error.

    Please review the steps I shared and follow the steps. It works with SQL Server 2014 SP2, I'm sure.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 3:06 PM
  • Evidently the DEK remains for tempdb even after a restart.

    What happens when you run this query?

    SELECT
       dbs.name,
       keys.encryption_state,
       keys.percent_complete,
       keys.key_algorithm,
       keys.key_length
     FROM
       sys.dm_database_encryption_keys AS keys
       INNER JOIN sys.databases AS dbs ON keys.database_id = dbs.database_id

    Hello Hilary,

    I have gone though your response and executed given piece of code. It returned two rows - one for user database with encryption state 1 (unencrypted ) and 3 for tempdb database confirming same is still encrypted.

    After executing DROP DATABASE ENCRYPTION KEY against user database in question, given script returned just one row for tempdb with encryption state 3 as earlier.

    I had bounced SQL Server services but nothing much happened.   

    Thank you for your time !!


    Thursday, January 12, 2017 3:13 PM
  • Hi Anil,

    As I mentioned above; that is by design and you can not remove encryption from tempdb. There is no way currently.

    But I am curious that tempdb gets created every time you restart SQL server. I will do a POC around this mystry.

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 3:16 PM
  • There are some that say that tempdb physical files are not technically re-created, unless they are moved via ALTER Datbase...might want to include in the test?  I'm about to mess around with this on my test instance that I can afford to kill and re-install at will

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.


    Kevin3NF on Twitter
    DallasDBAs

    Thursday, January 12, 2017 3:20 PM
  • Hello Kevin,

    Go to properties of the tempdb files from File Explorer and you'll see that the "Created" value changes when the Database Engine is restarted. So this sign tells me the old file is deleted and this is a brand new file.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 3:34 PM
  • Hi Anil,

    This is by design. Look at the explanation given here by MS. Sounds good to me.

    https://connect.microsoft.com/SQLServer/feedback/details/566858/removing-tde-from-tempdb-should-not-require-a-restart

    Thanks,


    Kindly mark the reply as answer if they help

    Hello Sunil,

    I checked referred post and found below explanation.

    "Thanks for your feedback. One of the security implications of your suggestion is that if an encrypted database is dropped, it may still have data from that database in TempDB and hence we would risk exposing data if we decrypted TempDB at that point....

    And once the database is gone, we have no way of knowing whether the data placed in temp db was from an encrypted or non-encrypted database. As such, the current behaviour is by design."

    With tremendous regards to Microsoft, I do have some disagreement on this topic....first part looks good in due consideration for security reasons. "we have no way of knowing whether the data placed in temp db was from an encrypted or non-encrypted database" - Why does SQL Server need to know this if tempdb is recreated every time ? 

    Lets hope there comes some information about it from your POC - I am looking forward to hear from you !!


    Thursday, January 12, 2017 4:10 PM
  • Anil,

    This you can consider as bug and MS has information about this, I believe this is fixed in SQL Server 2016. A lot of fellow MVP's have already informed this to MS team.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, January 12, 2017 4:18 PM
    Moderator
  • Hey Shashank,

    I think, It is persisted in 2016 as well. I have verified today.

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 4:49 PM
  • Thank you Shashank for your respond.

    I had performed my testing on SQL Server 2016 RTM and there is no good news on that topic.

    As you said, Microsoft is already informed about it. Lets hope this item is included in their to do list on some fortunate day.


    Thursday, January 12, 2017 5:00 PM
  • Hey Shashank,

    I think, It is persisted in 2016 as well. I have verified today.

    Thanks,


    Kindly mark the reply as answer if they help

    I will check that Sunil and revert

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, January 12, 2017 5:01 PM
    Moderator
  • Done with my POC on this today. I will writing a wiki article on this and publish results. Completed these tests on SQL Server 2016 Enterprise Edition SP1 (13.0.4001.0)

    >It is evident that tempdb stay encrypted even after removing TDE from user databases
    >Column [is_encrypted] into [sys.databases] remains 1 even after restarting SQL Services. So is that just the metadata or actually it is encrypted.
    >DEK is persisted in [sys.dm_database_encryption_keys] for user database even after removing TDE. It get removed when you drop the database.

    Cheers!! 


    Kindly mark the reply as answer if they help

    Friday, January 13, 2017 8:57 AM
  • Hello Sunil,

    Please share the address of the Wiki after publishing it. I'd like to reproduce the result in my test environment.

    As I said before, following the steps I shared, I can see the encryption is removed from the tempdb in my test environment. I suspect the issue may be version related.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Friday, January 13, 2017 9:05 AM

  • >Column [is_encrypted] into [sys.databases] remains 1 even after restarting SQL Services. So is that just the metadata or actually it is encrypted?

    Thank you Sunil for doing POC.

    Did you get chance to check further on quoted text ? 

    Appreciate your response. Thank you.

    Friday, January 13, 2017 9:14 AM

  • >Column [is_encrypted] into [sys.databases] remains 1 even after restarting SQL Services. So is that just the metadata or actually it is encrypted?

    Thank you Sunil for doing POC.

    Did you get chance to check further on quoted text ? 

    Appreciate your response. Thank you.

    Anil,

    I will leave that open for MS to answer but during my test when I tried opening MDF and LDF files of tempdb (after creating a shadow copy of them via a software when they were in use by SQL server). I was not able to find any user's specific data in raw file. Though there was some text visible which was related to tempdb's own metadata. Interesting part is that, this tempdb metadata was visible in clear text before encryption and after encryption but NO user specific data was visible (I created few tables in tempdb and inserted few data into them and those were not visible)

    I will post the link of wiki article.

    Thanks,


    Kindly mark the reply as answer if they help

    Friday, January 13, 2017 9:30 AM
  • I had a Azure VM with SQL Server 2016 on it and performed the test on it. Here's the complete steps:

    Version details of the SQL Server 2016 that I performed my test on: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor) 

    1- Create the master key, certificate, database encryption key and enable encryption (TDE) on the test database.

    USE master;  
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
    go  
    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
    go  
    USE test;  
    GO  
    CREATE DATABASE ENCRYPTION KEY  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
    GO  
    ALTER DATABASE test  
    SET ENCRYPTION ON; 
    GO

    2- Using the following query, I confirm that [encryption_state] is 3 for [tempdb] and [test] databases.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    3- I turn TDE off for the [test] database using the following query:

    ALTER DATABASE test  
    SET ENCRYPTION OFF;

    4- When I execute the following query I see that [encryption_state] is 1 for the [test] database and 3 for the [tempdb].

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    5- After the restart of Database Engine service through SQL Server Configuration Manager, I execute the following query again and it returns only [test] database and the [encryption_state] is still 1. [tempdb] is not returned by the query anymore.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    6- When I execute the following query in this version of SQL Server 2016, it returns [temp].[is_encrypted] = 1

    SELECT is_encrypted, name FROM sys.databases;

    HOWEVER, when I perform the exactly same steps in SQL Server 2014 (+SP2), the query above returns [temp].[is_encrypted] = 0

    As I said before, this is clearly a version specific BUG. Perform the same steps on SQL Server 2014 + SP2, you'll get a different result as I've been advocating since yesterday and you get another result when executing the same set of commands on SQL Server 2016.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Marked as answer by Anil_Kumar_DBA Friday, January 13, 2017 10:40 AM
    Friday, January 13, 2017 9:32 AM
  • Thank you Sunil and I look forward to go through your wiki article.

    Friday, January 13, 2017 9:49 AM
  • Hi Ekrem,

    Great point - I am installing SQL Server 2014, will patch same with SP2 and repeat your testing.

    Will update the result in this thread.


    Friday, January 13, 2017 9:56 AM
  • I had a Azure VM with SQL Server 2016 on it and performed the test on it. Here's the complete steps:

    Version details of the SQL Server 2016 that I performed my test on: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor) 

    1- Create the master key, certificate, database encryption key and enable encryption (TDE) on the test database.

    USE master;  
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
    go  
    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
    go  
    USE test;  
    GO  
    CREATE DATABASE ENCRYPTION KEY  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
    GO  
    ALTER DATABASE test  
    SET ENCRYPTION ON; 
    GO

    2- Using the following query, I confirm that [encryption_state] is 3 for [tempdb] and [test] databases.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    3- I turn TDE off for the [test] database using the following query:

    ALTER DATABASE test  
    SET ENCRYPTION OFF;

    4- When I execute the following query I see that [encryption_state] is 1 for the [test] database and 3 for the [tempdb].

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    5- After the restart of Database Engine service through SQL Server Configuration Manager, I execute the following query again and it returns only [test] database and the [encryption_state] is still 1. [tempdb] is not returned by the query anymore.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    6- When I execute the following query in this version of SQL Server 2016, it returns [temp].[is_encrypted] = 1

    SELECT is_encrypted, name FROM sys.databases;

    HOWEVER, when I perform the exactly same steps in SQL Server 2014 (+SP2), the query above returns [temp].[is_encrypted] = 0

    As I said before, this is clearly a version specific BUG. Perform the same steps on SQL Server 2014 + SP2, you'll get a different result as I've been advocating since yesterday and you get another result when executing the same set of commands on SQL Server 2016.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Ekrem,

    I will also verify this on 2014. Though point of debate was use of below query by you which was anyway filtering the tempdb from result set.

    SELECT * FROM sys.databases WHERE is_encrypted = 1 AND database_id > 4;

    Thanks,


    Kindly mark the reply as answer if they help

    Friday, January 13, 2017 10:03 AM
    • Edited by Ekrem Önsoy Friday, January 13, 2017 10:43 AM Changed the URL link as a hyperlink
    Friday, January 13, 2017 10:16 AM
  • That query is just a part of a test and during tests as you know we use lots of different methods. With that, I've already explained my aim with this query yesterday.

    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Friday, January 13, 2017 10:17 AM
  • I had a Azure VM with SQL Server 2016 on it and performed the test on it. Here's the complete steps:

    Version details of the SQL Server 2016 that I performed my test on: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor) 

    1- Create the master key, certificate, database encryption key and enable encryption (TDE) on the test database.

    USE master;  
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
    go  
    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
    go  
    USE test;  
    GO  
    CREATE DATABASE ENCRYPTION KEY  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
    GO  
    ALTER DATABASE test  
    SET ENCRYPTION ON; 
    GO

    2- Using the following query, I confirm that [encryption_state] is 3 for [tempdb] and [test] databases.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    3- I turn TDE off for the [test] database using the following query:

    ALTER DATABASE test  
    SET ENCRYPTION OFF;

    4- When I execute the following query I see that [encryption_state] is 1 for the [test] database and 3 for the [tempdb].

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    5- After the restart of Database Engine service through SQL Server Configuration Manager, I execute the following query again and it returns only [test] database and the [encryption_state] is still 1. [tempdb] is not returned by the query anymore.

    SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys;

    6- When I execute the following query in this version of SQL Server 2016, it returns [temp].[is_encrypted] = 1

    SELECT is_encrypted, name FROM sys.databases;

    HOWEVER, when I perform the exactly same steps in SQL Server 2014 (+SP2), the query above returns [temp].[is_encrypted] = 0

    As I said before, this is clearly a version specific BUG. Perform the same steps on SQL Server 2014 + SP2, you'll get a different result as I've been advocating since yesterday and you get another result when executing the same set of commands on SQL Server 2016.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Hello Ekrem,

    I repeated your test on SQL Server 2014 SP2 and after disabling encryption for user database and bouncing the services, I found tempdb is not encrypted any more. Great news as there was lot of confusion about it.

    But there seems another issue has uncovered. I enabled encryption for test database again and checked sys.database DMV, tempdb is not encrypted which shouldn't happen.

    Thank you for your efforts and time for clarifying on this topic. Appreciate it !! 

    Friday, January 13, 2017 10:28 AM
  • SQL Server 2014 SP2 has its own bugs with TDE and that's the one you hit I guess.

    After enabling TDE for a database in SQL Server 2014 SP2, [tempdb] looks unencrypted when queried using sys.databases but looks encrypted when queried using sys.dm_database_encryption_keys (this bug is fixed with SQL Server 2016).

    However, this does not mean that [tempdb] is not encrypted, of course it needs further testing, but my guess is it's encrypted but not shown so in the sys.databases catalog view.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Friday, January 13, 2017 10:38 AM
  • I created a connect item and filed it as a bug, here's the details:

    https://connect.microsoft.com/SQLServer/feedback/details/3118734


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    I voted up for the connect item. Thank you.
    Friday, January 13, 2017 10:39 AM
  • Thank you everyone for all your efforts and reporting this bug. We did some initial investigation on this issue and found it to be reporting bug where the DMV column (sys.databases.is_encrypted) doesn't get updated for tempdb. While we will be looking to fix this in upcoming servicing release but it is benign issue which shouldn't impact the performance or resource consumption except for inaccurate reporting. I will also encourage you all to read our blog post from Bob on this topic for better understanding

    https://blogs.msdn.microsoft.com/sql_server_team/sql-server-mysteries-the-case-of-tde-and-permanent-tempdb-encryption/


    Parikshit


    Saturday, January 14, 2017 7:37 AM
  • Thank you Parikshit for swift and easing response ! 
    Saturday, January 14, 2017 1:39 PM
  • Thank you everyone for all your efforts and reporting this bug. We did some initial investigation on this issue and found it to be reporting bug where the DMV column (sys.databases.is_encrypted) doesn't get updated for tempdb. While we will be looking to fix this in upcoming servicing release but it is benign issue which shouldn't impact the performance or resource consumption except for inaccurate reporting.

    Parikshit

    Parikshit, 
    Thanks for taking this forward with MS. I observed during my POC that it is just Is_Encrypted  in sys.databases column remains set to 1 even after removing TDE from all of the User databases.

    Cheers!!


    Kindly mark the reply as answer if they help

    Saturday, January 14, 2017 4:34 PM