Need a help in backup Encyption Certificate

Question

Need a help in backup Encyption Certificate

Viewing 15 posts - 16 through 30 (of 31 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

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

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 2

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 - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

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.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

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 - SSC Editor SSC Guru Points: 734459 More actions · Answer 5

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.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 6

Thanks steve,

I am following that article now which you linked earlier.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 7

Thanks Steve,

I successfully implemented encryption on database 'X'.

now apart from the "EXECUTE AS" and apply different user to see the record to verify transperant data encryption., IS THERE ANY OTHER WAY WE CAN CHECK THE ENCRYPTION ON THE DATABASE.? AND its's performance.?thanks.

Pratik.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 8

Performance? You'd have to have a workload you can run and run it before, and after, the encryption. Otherwise no good way to get the overhead. However I haven't seen anyone report > 5%.

Encryption? Sure, detach the database, open it in a hex editor and search for known strings. They'll be there in unencrypted databases, not in encrypted ones.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 9

Performance:

Hi Steve,

I got your point in regards to this.

But is there any chance that I can still do it on my student database, which has only one address table with 900 records in it.!!

can I put encryption on off mode and check the performance and do the same opertaion after stting up that option to on.?

how to open this hexa editor?

becuase under my right for login and username..I can only store the files to default location under c:\..\data\ forlder but I can not access it.

So, I just want to know how to open this hexa editor and how I can get that database file which i named as student_db_pre_encryption.bak and student_db_post_encryption.bak , before and after the encryption process respectively during backup stage.?

thanks for your help.

Pratt.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 10

you can do this with a small database, but you likely won't see any difference in performance. If that's OK, or what you're trying to show, then it will be fine.

In terms of a hex editor, you need to download one. I use XVI32. You'll need to download it. Once you have, you can detach your database and then open the .mdf file in the hex editor and find your strings. Once you enable TDE, you can detach it again, and you won't find your strings.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 11

Thanks Steve,

Just to confirm, Here the we can measure the performance by most likely large number of DML operations (running many insert, update and delete statments in a sequence) and by including "Client Statistics" option to view the results, is it?

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 12

You set use SET STATISTICS IO and get the data back.

You want server statistics, not client.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 13

Hi Steve..

M I doing the right thing to check the performance!!!

Please let me know how can I make it correct OR better!!

USE Student;

GO

ALTER DATABASE Student

SET ENCRYPTION off;

GO

USE Student;

GO

SET STATISTICS IO ON;

GO

SELECT *

FROM dbo.address

WHERE DWADDRESSID < 590;

BACKUP LOG StudentTO DISK = 'pubs.sqb'

USE Student;

GO

ALTER DATABASE Student

SET ENCRYPTION on;

GO

USE Student;

GO

SET STATISTICS IO ON;

GO

SELECT *

FROM dbo.address

WHERE DWADDRESSID < 590;

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 14

That will show you the difference for that query only, but if that's all you need, it should demonstrate that. If you don't see a difference, you might need more complex or more queries.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 15

Hi Steve,

So I included client statistics option just to check a kind of performance.

I exceuted different queries in different mode of encryption

[p]TRIAL=> ENCRPTN=> CLNTPRCESNGTIME=> TTLEXETIME=> QUERY_INCLUDES

1 => ON => 10ms => 256ms => Bunch of insert, update and merge statement, affected around 4103 rows

2 => OFF => 4ms => 252ms => Bunch of insert, update and merge statement, affected around 4103 rows

3 => ON => 97ms => 355ms => Complex Bunch of insert, update and merge statement, affected around 3690rows

4 => OFF => 14ms => 269ms => Complex Bunch of insert, update and merge statement, affected around 3690rows

[/p]

I just want to know now that, while encryption is on, ClientProcessingTime(CLNTPRCESNGTIME) and TotalExecutionTime(TTLEXETIME) is increasing significantlly , due to data is encrypted.!!!

M I right?

and, they are taking time to decrypt and then show up in the result..!!!

do I have right understading about this data or not!!!

Please help me to understand this result more properlly.

thanks.