FIX: The tempdb system database is still encrypted after you turn off encryption on an instance of SQL Server

Applies to: SQL Server 2016 Enterprise CoreSQL Server 2016 EnterpriseSQL Server 2016 Developer

Symptoms


Consider the following scenario:

  • You have an instance of Microsoft SQL Server in which the user and temp databases are encrypted.
  • You turn off encryption on the user databases by running the ALTER DATABASE database_name SET ENCRYPTION OFF Transact-SQL command. 
  • You restart SQL Server.

In this scenario, you find that the value in the sys.databases.is_encrypted column in the sys.databases dynamic management view is still 1. This is true even though all other databases in this instance of SQL Server are no longer encrypted.

Resolution


Service pack information for SQL Server

This update is fixed in the following Service Packs for SQL Server:

Service Pack 2 for SQL Server 2016

Service Pack 3 for SQL Server 2014

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


Learn about the terminology Microsoft uses to describe software updates.