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 Wednesday, June 12, 2013 12:27 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
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.
Post #1462774
Posted Wednesday, June 12, 2013 1:07 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
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.
Post #1462788
Posted Wednesday, June 12, 2013 1:11 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 12:21 PM
Points: 33,202, Visits: 15,350
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
Post #1462792
Posted Thursday, June 13, 2013 8:23 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
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.
Post #1463114
Posted Thursday, June 13, 2013 9:01 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 12:21 PM
Points: 33,202, Visits: 15,350
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
Post #1463143
Posted Thursday, June 13, 2013 11:15 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 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.
Post #1463215
Posted Thursday, June 13, 2013 11:34 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 12:21 PM
Points: 33,202, Visits: 15,350
#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
Post #1463223
Posted Thursday, June 13, 2013 11:47 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
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.
Post #1463231
Posted Thursday, June 13, 2013 1:23 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:00 PM
Points: 4,196, Visits: 3,235
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
Post #1463280
Posted Thursday, June 13, 2013 2:53 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
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.
Post #1463320
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse