Auto encryption of new databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Auto encryption of new databases

Auto encryption of new databases

Rate Topic

Display Mode

Topic Options

Author

Message

olli 48768

Posted Monday, September 10, 2012 7:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 4:56 AM
Points: 3, Visits: 10

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! Cool

Post #1356749

RBarryYoung

Posted Monday, September 10, 2012 11:34 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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".

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #1356918

olli 48768

Posted Tuesday, September 11, 2012 3:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 4:56 AM
Points: 3, Visits: 10

Alright, thanks, I will give it a try.

Post #1357213

RBarryYoung

Posted Tuesday, September 11, 2012 7:10 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #1357382

Perry Whittle

Posted Tuesday, September 11, 2012 12:12 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

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" Wink

Post #1357626

olli 48768

Posted Wednesday, September 12, 2012 12:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 4:56 AM
Points: 3, Visits: 10

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.

Post #1357804

Perry Whittle

Posted Wednesday, September 12, 2012 1:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

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" Wink

Post #1357814

« Prev Topic | Next Topic »

Permissions