Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Need a help in backup Encyption Certificate Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 3:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
There is a percent complete column, where you can get the progress. In terms of the rate, you'd have to just watch this and calculate something.

SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO

In terms of master keys, you have one per database. These should be linked to the Service Master Key, and if so, you don't need to open them. If they are not, you need the password. No way I know of to recover these. If you remove encrpytion, you can drop it and add it back.

In terms of TDE, you might read this, and then document your progress as you go.
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463323
Posted Thursday, June 13, 2013 3:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Honestly I was just going through that article.

when I tried following query

USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%'
GO

i found following two results


##MS_DatabaseMasterKey##--keylength-128--algorithm is --D3
##MS_ServiceMasterKey##--keylength-128--algorithm is --D3


I have no idea who made this.

But when I tried to follow your suggested article..I found error on first step..that "THERE IS ALREADY A MASTER KEY IN THE DATABASE"

When I run the following query

use master
SELECT * FROM sys.certificates

it gives me certificate which I made few day back called "TDECert" it is also states that ENCRYPTED_BY_MASTER_KEY in othe coulmn.

I am now confused..what to do now.

Please inform me..is there any way I can clear all out and start from begining.

I know I am bigginer and doing hard task like encryption by refering books, internet, asking friends like you and putting some own logic, I might have stupid quetions..but please help me to improve.

thanks.
Post #1463328
Posted Thursday, June 13, 2013 3:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
My requirment is to encrypt the one of my own database with AES_128..algorighm

that's all.

and then further to depth level.
like table, field, cell level..if possible.

thanks.
Post #1463329
Posted Thursday, June 13, 2013 3:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
First, you probably should have someone else responsible for setting this up or working with you. Encryption is serious business and if you lose keys, you lose data. Microsoft is not going to be able to help you recover stuff. Once the keys are gone, the data is gone.

In terms of the keys, the system creates the service master key. You want this if you need to recover this instance without re-installing everything. It's less critical than other keys. The master key was created by someone, which is fine. You want a backup of this if you are trying to recover this database or restore it elsewhere. You need this key.

Can you run this?

Use Master;
go
BACKUP MASTER KEY TO FILE = 'exportedmasterkey'
ENCRYPTION BY PASSWORD = 'mytest'

If that works, then you are OK.

In terms of your certificate, this is inside the database protected by TDE. This is not tempdb, even though that is protected, you don't care. You don't experiment there.

Here's the code I use in a TDE demo:
USE master
;
go
BACKUP CERTIFICATE TDEPRimer_CertSecurity
TO FILE = 'tdeprimer_cert'
WITH PRIVATE KEY (
FILE = 'tdeprimer_cert.pvk',
ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%')
;
go

This should give me two files in my \data folder. Can you do that.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463333
Posted Friday, June 14, 2013 2:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
how to disable database 'x' encryption while an encryption while its encryption is in progress?

it shows that database 'x' encryption is having value 2 from last one day..

Is there any timeframe that to change that status into 3 which indicates that encryption process is completed.

I am trying to find it out from the books and internet..but no success...

please help me.

thanks in advance.!
Post #1463768
Posted Friday, June 14, 2013 5:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
It should complete by itself. Has the progress changed at all?

If not, then I think you are in trouble. I'd backup the database, or copy out all data/schema and my guess is you will need to drop it and recreate it. If it's stuck in "2", not sure what a backup will look like. You can run the backup, then open it up in a hex editor (like xvi32). If anything after the first couple times you scroll down looks random, it's encrypted.

However, if you can access the database with SSMS and a query window, you ought to be able to bcp the data out. I would not restart this server until I'd BCP'd out all the data and scripted the schema.

As I mentioned, encryption is serious business. You might need to call a consultant or MS to help you straighten this out if you're stuck. If you want recommendations, I can provide some, but it will cost $$.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463787
Posted Monday, June 17, 2013 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Hi friends.

Thanks SSC-Dedicated and all others for all your help .

I have still issue..after 4 days..even the encryption_State didn't change..it is still at 2, I want it to be in completed state which is 3.

apart from this there is already a master key on server, someone has created few days ago.

I don't know the password for that master key.

Is there any way I can drop it .?

Thanks.


Post #1464153
Posted Monday, June 17, 2013 8:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
At this point I wouldn't drop the master key. That won't help with your encryption status.

If you cannot drop this (encrypted state = 2) database, or do not have an unencrypted backup of it, I would call Microsoft support.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1464181
Posted Monday, June 17, 2013 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
I guess to call Microsoft Support , I might have to check with my boss, for my rights to do that.

But Steve,

I have another option..I don't know whether it will work out or not.



Can I follow the same thing with "tempdb" system database.?

Like use tempdb-> create master key-> create certificate-> uset that certificate to encrypt the personal database-

than I will create one database in SqlServer called "student", I will enter few fake records.

The reason why I am asking is, I am here for short time period on this job, I need to produce the report based on performance counter/measurment after encryption is done.

for an example performance of encrypted database and non-encrypted database.

and I know I am just more than bigginer in this encypting database.

or I would like to follow your new idea if you have any!

thanks.



Post #1464195
Posted Monday, June 17, 2013 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
No, do not play with tempdb for encryption. This happens automatically when you enable TDE in another database. This is a special, system database. The way you work with user databases is not the way you work with tempdb.

You can create another user database and try the encryption there, using a new certificate and the instructions from the article I linked.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1464239
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse