TempDB And TDE

Bob Ward troubleshoots an oddity around sys.databases marking tempdb as encrypted even when no user databases are encrypted:

In my test I did not hit the breakpoint. And furthermore, you will notice that when you query sys.dm_database_encryption_keys, there is no row for tempdb at all.  So our debugger breakpoint proves that tempdb is not permanently encrypted. Instead, if ALL user databases have TDE disabled and you restart SQL Server, tempdb is no longer encrypted. So instead of using sys.databases, use sys.dm_database_encryption_keys to tell which databases are truly enabled for encryption. I then verified my findings in the source code. Basically, we only enable encryption for tempdb if 1) ALTER DATABASE enables any user db for TDE 2) When we startup a user database and have encryption enabled. I also verified the behavior with my colleagues in the Tiger Team (thank you Ravinder Vuppula). We will look at fixing the issue with sys.databases in the future (ironically as I said earlier it was never enabled for tempdb before SQL Server 2016).

Read on for Bob Ward’s patented Debugger Fun.  My takeaway from this is that sys.dm_database_encryption_keys is valid, whereas sys.databases’s is_encrypted column might not be.

Related Posts

NT AUTHORITY\ANONYMOUS Error Editing Procedures

Kenneth Fisher takes us through a security issue: If you have to deal with linked servers then you probably have or will run into the following error: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure. Kenneth explains why you might […]

Read More

Avoiding the Kerberos Double-Hop Issue

Michael Bourgon shows us one extra thing to keep in mind to avoid errors when trying to use Kerberos in a double-hop situation: Yesterday I ran into the dread Kerberos Double-Hop when trying to set up a linked server.  Thought it was the standard “Add an SPN using the Microsoft Kerberos Configuration tool”.  Which didn’t […]

Read More


January 2017
« Dec Feb »