Sql-server – Enable TDE When Publishing DACPAC

deploymentencryptionsql servertransparent-data-encryption

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.

Best Answer

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.

From: https://social.msdn.microsoft.com/Forums/en-US/df7d6f7f-f912-48ae-b553-0cede0eedce1/ssdt-and-tde?forum=ssdt

"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
"
Related Question