In the database properties in Visual Studio there is a checkbox for encryption. I'm not sure if I check this if it will enforce encryption when doing a DACPAC deploy. What I would really like to do is enable encryption per environment. Ideally I would add a setting to my dacpac publish profile that determines whether or not encryption will be enabled on the database when publishing. Currently TDE gets disabled every time I do a publish.
This setting will only let you 'respect' the encrypted database setting. It will not generate scripts for you to run to enable encryption on the target deployment server. If you uncheck that, it will then disable encryption on your source database scripts that you are generating for the DACPAC.
Since TDE requires several keys to fully function, you might need to configure that outside of the DACPAC in order to set encryption as on/off per database/environment. You could utilize this as part of your greater rollout effort (using orchestration like Jenkins/Octopus/maybe TFS), doing it as part of a powershell script, and so fourth.
"When this checkbox is checked, no encryption changes are made to the target database*. That is, databases are neither encrypted or decrypted. I don't see that it performs any function at all, but perhaps it will be utilized in a future version.
When this checkbox is unchecked, encrypted database are decrypted, and no changes are made to unencrypted databases. No warning or status message is output to the messages window when running the script. This is the coe that is generated:
IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)') BEGIN ALTER DATABASE [$(DatabaseName)] SET ENCRYPTION OFF WITH ROLLBACK IMMEDIATE; END "
3Depending on the deploy scenario the following can also help: Assuming the Keys/Certs are in place, you can add the code to check for and turn on TDE as a Post Deploy Script.– DaveMar 7, 2016 at 22:48
+1 for @Dave, that's probably the best. Have your deployment process just verify it's on instead of setting it. Mar 7, 2016 at 23:00
I've accepted this at the correct answer but for me really the solution was implementing Dave's comment. Thanks guys!– ChrisMar 14, 2016 at 19:41