SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 encryption		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sboshuis
Starting Member

Netherlands
2 Posts
Posted - 07/27/2012 :  08:04:13  Show Profile  Reply with Quote
Playing a little with sql encryption and beginning to get the picture but not sure about some things.

- first I create a master key
- then a certificate that is encrypted by the master key
- then a symmetric key with password and encrypted by the certificate

all fine

but when I do the same in another server using the same but with a different master key I can still decrypt data that is encrypted on the other server.

- So, the master key is only there to protect the symmetric key?

Why do you need to backup the master key then? when I add a symmetric key with another master key decryption still works so? why?

So, as long as I have the create scripts for certificate and key I can allways decrypt the data on any sqlserver?

Or am I missing something?

komkrit
Yak Posting Veteran

Thailand
60 Posts
Posted - 07/27/2012 :  14:10:11  Show Profile  Reply with Quote
One thing I think, you backup/restore database to another server. So Database Master Key goes together with database backup file.
In my test, I can only use the following steps to decrypt data from database backup. But decrypt an raw encrypted data without database back is more complex

How to read encrypted data from database backup.
1. Restore database from backup to any server.
2. Open Master Key with password (you have to remember Database Master Key password)
3. Open Symmetric Key
4. You can read data by function decrypt() right now.

How to read encrypted data from exported file
1. Create blank database.
2. Restore Database Master Key from file(database master key backup file)
3. Open Database Master Key with password
4. Create certificate from file(Certificate Backup with private key + Certificate Password)
5. Create Symmetric Key from command with Key_SOURCE + IDENTITY_VALUE
6. Open Symmetric Key
7. You can read data by function decrypt() right now.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000