Auto encryption of new databases

  • I am using SQL 2008 R2 Enterprise with database encryption. Performance is fine.

    I just wonder if there is a way to ensure that all newly created databases will be encrypted by default.

    Thanks in advance! 😎

  • I have not done this myself, but I am pretty sure that you can do it through the use of the new Policy Management features. For this, I think that you would have a policy that requires the [Database Options].[EncryptionEnabled] facet to be "true".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Alright, thanks, I will give it a try.

  • I should add that this may not actually do it for you, it may only enforce it. (throw an exception if it's not enabled when a new database is created). You may need to enable it in Model to get it automatically turned on.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • if you specify a check TDE policy it will log when the policy has been violated. When manually evaluating the policy you have the option of applying the change to meet the policy requirement, however unless the pre reqs are in place (database master key, server certificate and database encryption key) the change will fail.

    Do you really want to enable TDE for all the databases on your instance(s)?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks for replying. No I dont want to enable it on all databases, but it is a security policy of a customer. Meanwhile I managed to write some code to solve the problem.

    You are right with the policy, when evaluating the result and changing the db to be encrypted there is no encrytion key available, it would fail.

  • Quite, the encryption hierarchy has to be in place before policy based implementation will succeed

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply