SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need a help in backup Encyption Certificate


Need a help in backup Encyption Certificate

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63648 Visits: 19115
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
My Blog: www.voiceofthedba.com
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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.
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63648 Visits: 19115
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
My Blog: www.voiceofthedba.com
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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.!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63648 Visits: 19115
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
My Blog: www.voiceofthedba.com
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63648 Visits: 19115
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
My Blog: www.voiceofthedba.com
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63648 Visits: 19115
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search