June 2, 2009 at 5:44 am
[font="Tahoma"]
Hi all,
Mu database using encryption and I new in using data encryption and I have Some question I listed hereunder:
What are objects I have to backup after encrypt my columns?,to prevent losing of my encrypted data
What if needed to Restore database using encryption on another server is there something I should done or just straightforward (backup/restore)?
What if my symmetric key delete by mistake?
What are the best practices to admin database using encryption?
What if I need to change master key password the data have been encrypted with old password will working with the new password correctly?
Thanks , Regards
[/font]
June 2, 2009 at 7:10 am
Hi - I can't answer all your questions but here's what I have done.
What are objects I have to backup after encrypt my columns?,to prevent losing of my encrypted data
The system tables get backed up with the database. However: Keep the scripts you used to create your Certificates and Keys.
What if needed to Restore database using encryption on another server is there something I should done or just straightforward (backup/restore)?
The system tables sys.symmetric_keys, sys.certificates, etc should be restored. I think you'll only need to re-create the master key - with the same password on the restored db
What if my symmetric key delete by mistake?
That's why you keep the original creation scripts
What are the best practices to admin database using encryption?
Don't Know
What if I need to change master key password the data have been encrypted with old password will working with the new password correctly?
Yes in theory (but I haven't tried this out) If you use ALTER MASTER KEY REGENERATE WITH ENCRYPTION giving a new password. You'll obviously have to use the new password to get at your data
June 2, 2009 at 12:08 pm
Thanks a lot for your valuable response.
Regarding backup question when I backup DMK and Certificate and regenerate the symmetric key by using the following scripts the column I was encrypted retrieve null !!!!
BACKUP MASTER KTY with same password
BACKUP MASTER KEY TO FILE = 'C:\Backup\DMK'
ENCRYPTION BY PASSWORD = 'Test'
BACKUP Certificate
BACKUP CERTIFICATE EncryptTestCert TO FILE = 'c:\Backup\EncryptTestCert'
WITH PRIVATE KEY ( FILE = 'c:\Backup\TestTableKey' ,
ENCRYPTION BY PASSWORD = ‘Test’)
GO
And then delete symmetric key, certificate, and DMK with following scripts
Restore Master Key
RESTORE MASTER KEY FROM FILE = 'C:\Backup\DMK'
DECRYPTION BY PASSWORD = 'Test'
ENCRYPTION BY PASSWORD = 'Test'
FORCE
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Test'
Restore Certificate
CREATE CERTIFICATE EncryptTestCert
FROM FILE = 'c:\Backup\EncryptTestCert'
WITH PRIVATE KEY (FILE = 'c:\Backup\TestTableKey',
DECRYPTION BY PASSWORD = 'Test');
Regenrate symmetric key
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO
And then try decrypt the column I got NULL!!!
Is there something I missed ??
Editor's Note: Removed font
June 2, 2009 at 12:49 pm
that's the largest font I've ever seen on the forum. 😀
June 2, 2009 at 1:28 pm
OK You've done things a different way to me.
But I've got it very wrong.
I've checked my data, and I can't access data encrypted before the database was restored :angry: - but its not that important, as not much data has been lost.
So I'm now looking at the BACKUP MASTER KEY / RESTORE MASTER KEY procedures (which I didn't do originally), as this has exposed a hole in my procedures - and if there were a failure now - we would lose a lot of significant data.
June 2, 2009 at 2:02 pm
sqlguy (6/2/2009)
Thanks a lot for your valuable response.Regarding backup question when I backup DMK and Certificate and regenerate the symmetric key by using the following scripts the column I was encrypted retrieve null !!!!
First, it really isn't necessary to use such a large font. it just makes it very difficult to read everything in your post easily. Many of us really don't want to have to scroll a lot to read just a few sentences.
Second, I hope you were doing this with a test database first and not with a production database. Any time you start working with using encryption, it is important to do your due diligence in both researching what you are going to do as well as testing it throughly before going into production.
Although you will get very good advice from many of the members on SSC, it is always best to do extra research in areas like this one. Plus, we are human, and we do make mistakes at times. I myself, know very little at this time regarding the use of encryption. Any advice I may give you would be based of my own research and testing. I would not expect you take anything I may say as gospel and implement directly into production.
June 2, 2009 at 2:10 pm
Try looking over some of the documentation.
Here's a couple of links.
Hopefully this will help out. I imagine the
Greg E
http://msdn.microsoft.com/en-us/library/aa337557(SQL.90).aspx
and for RS
http://msdn.microsoft.com/en-us/library/ms157275(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms156010(SQL.90).aspx
June 2, 2009 at 2:37 pm
The issue is that you are creating a new symmetric key. You can't "regenerate" keys as they will be different each time. That's the idea.
You would restore this database to a new server, the symmetric key would be there already. This is also why you need regular backups of your keys.
June 2, 2009 at 3:11 pm
I've figured out where I went wrong.
1) While you can backup the master key and certificates, you can't back up a symmetric key.
2) if you want to re-create the same symmetric key it must be created with an IDENTITY_VALUE and KEY_SOURCE supplied (and the same certificate)
http://msdn.microsoft.com/en-us/library/ms366281(SQL.90).aspx - thanks Greg
3) Since I didn't use IDENTITY_VALUE + KEY_SOURCE - to be safe, I'll have to decrypt all my sensitive data, Then create a re-creatable symmetric key, and re-encrypt my data with that.
I'm glad I found that out now rather than after a failure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply