I have database projects in Visual Studio 2013 from which I deploying databases to SQL Server. Also I am using Transparent Data Encryption for these databases and it is in state 3 which means TDE is ON.

Issue becoming when I deploy databases. After database deployment TDE state is 1 which means TDE is OFF. I only know that Database Encryption Key exist in database page level and I supposed that something happened there when DB schema is changed.

My question is how I can control that process? How I can save TDE state to be 3 after deployment?

I can run SQL query which will turn on TDE but is there any other way?

  • You can use ALTER DATABASE db_name SET ENCRYPTION OFF / ON. So before the deployment, you can set encryption OFF and after deployment, you can turn it ON. You can even check the status encryption_state of encryption using sys.dm_database_encryption_keys and accordingly run the above alter database statement. – Kin Shah Mar 31 '15 at 19:17
  • I've ran TDE for a few years and through numerous deployments and never had to turn it on or off during a deployment using SQL Server 2008 with code deployed from an earlier version of VS. I suspect a script in the deployment process or some other method is causing TDE to turn off. Can you record any ALTER DATABASE commands before your deployment to review? I one saw a odd database corruption issue on TDE, can you re-encrypt without any issues? – Ali Razeghi Mar 31 '15 at 19:43
  • @Kin Thanks fro your respond, but as I mentioned before I don't want to turn ON or turn OF TDE with SQL statements. I want TDE to be always turned ON. – kat1330 Mar 31 '15 at 19:48
  • @ivan.petrovic TDE is not an instance wide setting, if you deploy a new database or overwrite an existing database you would have to enable it for each new/restored database. – Your_comment_is_not_funny Apr 2 '15 at 16:56
  • Were you ever able to figure this out? I would hope there would be a setting you could add to the publish file so you can keep it enabled or disabled for different environments. – Chris Mar 7 '16 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.