1

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.

Improve this question

1 Answer 1

Reset to default
3

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
"
Improve this answer
3
  • 3
    Depending 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.
    – Dave
    Mar 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.
    – Ali Razeghi - AWS
    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!
    – Chris
    Mar 14, 2016 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.