Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sboshuis
Starting Member
2 Posts |
 Posted - 2012-07-27 : 08:04:13
|
| 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 certificateall finebut 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
60 Posts |
Posted - 2012-07-27 : 14:10:11
|
| 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 complexHow 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 Key4. You can read data by function decrypt() right now.How to read encrypted data from exported file1. Create blank database.2. Restore Database Master Key from file(database master key backup file)3. Open Database Master Key with password4. Create certificate from file(Certificate Backup with private key + Certificate Password)5. Create Symmetric Key from command with Key_SOURCE + IDENTITY_VALUE6. Open Symmetric Key7. You can read data by function decrypt() right now.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
 |
|
|
|
|
|
encryption