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
Learner44
Learner44
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 438
HI ..I am having trouble with keeping backup of encryption certificate.

I created database encryption key with folowing query.

Use Test
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE SQLServerTDECertificate
GO



but now SQL server not allowing me to do the backup.

BACKUP CERTIFICATE SQLServerTDECertificate
TO FILE = 'C:\temp\TDE\SQLServerTDECertificate.cert' WITH PRIVATE KEY
( FILE = 'C:\temp\TDE\SQLServerTDECertificateKey.key', ENCRYPTION BY PASSWORD = '!@90jujujuijkop*&^jni@!')
GO


error message:
Msg 15151, Level 16, State 1, Line 1
Cannot find the certificate 'SQLServerTDECertificate', because it does not exist or you do not have permission.


Please help me how I can do the backup.
Learner44
Learner44
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 438
I found that I can not write that .cert and .key file to specific location.

It says that either I don't have permission or files are already exists.

I checked files are not exists.,

How can I check things about permissions?

thanks.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65157 Visits: 19118
The server account needs full control.

If you remove your path and do the backup, you should find the cert in your SQL Server's \data folder. However the cert (and private key files) should be ACL'd to the service account and you need administrative permissions to copy these anywhere.

You can set specific permissions for the service account to a particular folder, but c:\temp is not likely a good location.

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 (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 438
when I tried to backup the master key and certificate it gives me following error.

USE master

GO

BACKUP CERTIFICATE SQLServerTDECertificate

TO FILE = 'C:\CERT\MySQLCert'

WITH PRIVATE KEY (file='C:\CERT\MySQLCertKey',

ENCRYPTION BY PASSWORD='!@chennuio89*()as@!')


Msg 15581, Level 16, State 3, Line 2
Please create a master key in the database or open the master key in the session before performing this operation.


and when tried to create master key with following query

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='!@chennuio89*()as@!'
GO

Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.


Need a help please.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65157 Visits: 19118
If your master key is not protected by the service master key, you need to open it. Since I don't know how you configured your encryption piece, you may need an

open master key decryption by password = 'XXXX'



then you can issue a backup, but try this first:



BACKUP CERTIFICATE SQLServerTDECertificate
TO FILE = 'MySQLCert.cer'
WITH PRIVATE KEY (file='MySQLCertKey.pvk',
ENCRYPTION BY PASSWORD='!@chennuio89*()as@!')



Please also tell me that isn't your real password.

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 (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 438
Hi I can open the master key...

USE tempdb

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'


but when i tried following qquery it give me an error,


use tempdb
go

BACKUP CERTIFICATE SQLServerTDECertificate

TO FILE = 'C:\Users\RawalPr1\Desktop\T1\MySQLCert.bak'

WITH PRIVATE KEY (file='C:\Users\RawalPr1\Desktop\T1\MySQLCertKey.pvk',

ENCRYPTION BY PASSWORD='password')


error:

Cannot write into file 'C:\Users\RawalPr1\Desktop\T1\MySQLCertKey.pvk'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

what to do next?

I also tried on several other location except this..but still the same replay.

thanks.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65157 Visits: 19118
#1, why use tempdb? It's like you are trying to make this more complex first instead of getting it working.

Do it WITHOUT ANY PATH first. Get the backup working correctly.

Then you can add a path, but the service account for SQL Server, not your user account, needs fll control rights in the folder.

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 (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 438
Actully some other user has already created master key..

I don;t have any idea who was he/she.

Now the issue is i don't have password for it too. and that key is created with different algorithm.

I have create one certificate under master database.

but when I do backup it is aksing for open or create the master key.

as matter of fact i can not create another master key and I don't have password to decreypt it and open it, I choosed anothe option too create the master key in different system db e.g. tempdb. just for testing the encyption with custome database.

Your all suggestions and tips are welcome for future steps.

thanks.
Ed Wagner
Ed Wagner
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17099 Visits: 10094
I don't know what to do regarding the database master key for which you don't have the password. I hope someone else does, because I wouldn't mind learning about that myself.

My approach to backing up database master keys, certificates and symmetric keys is simple. The obvious part is to backup all your keys and certificates. What I also do is to save the original SQL I use to create the keys and certificates in the first place. This way, I know I can recreate the keys with the original password. I had a problem restoring a key once and promised myself to not let that happen again.

From what I've read, you can't get from the encrypted keys back to the key passwords. Someone may have figured out a way to do this, but that someone would not be me. So I want to be sure I have everything backed up and able to be restored as reliably as possible. The SQL files are very small and I keep them in a secure (and encrypted) location.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Learner44
Learner44
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 438
Hi,

After encypting the database, my "is_encrypted" value is 1 and "encryption_state" value is 2..


what does it mean?

I know the fact that when "encryption_state" value will be 3 the encryption process will be completed.

But, I want to know what is the time duration of this change in value...I mean when encryption_state will convert into 3 from 2.

Please help.

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