June 12, 2013 at 12:27 pm
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.
June 12, 2013 at 1:07 pm
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.
June 12, 2013 at 1:11 pm
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.
June 13, 2013 at 8:23 am
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.
June 13, 2013 at 9:01 am
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.
June 13, 2013 at 11:15 am
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.
June 13, 2013 at 11:34 am
#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.
June 13, 2013 at 11:47 am
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.
June 13, 2013 at 1:23 pm
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.
June 13, 2013 at 2:53 pm
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.
June 13, 2013 at 3:07 pm
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/
June 13, 2013 at 3:20 pm
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.
June 13, 2013 at 3:21 pm
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.
June 13, 2013 at 3:45 pm
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.
June 14, 2013 at 2:35 pm
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.!
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply