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 |
|
irvo1968
Starting Member
2 Posts |
 Posted - 2008-11-12 : 16:53:15
|
| Please can someone advise me on this before I lose my sanity! I am migrating an sql 2005 database to a dedicated server. On the current server, sensitive user data is protected using asymmetric encryption by certificate accessed via ASP. On the new server, I want to change to symmetric key encryption. On the existing server the encrypted data is stored in columns with data type nvarchar(128) and looking at the ASP pages the data was CAST as nvarchar(128) prior to EncryptByCert. I was planning to decrypt the data on the existing server, create a backup, restore the backup on the new server and then encrypt the data with symmetric key and amend all of the ASP accordingly. What I need to know is:1. Do I need to CAST this data when decrypting it on the existing server prior to encrypting it with a symmetric key on the new server and if so what as?2. Should I change the column data type to varbinary prior to encrypting it again on the new server with a symmetric key?I have tried various ways without complete success.Any help much appreciated. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-13 : 00:13:15
|
| If you r going to change symmetric key on new server, u have to follow these steps.ON OLD SERVER SIDE:Create new columns with in each table against encrypted columnsexample : If you have Employee Table (EmpCode,EmpName,EmpNO,EmpAddress) and EmpName and EmpNo are encrypted THEN Employee Table (EmpCode,EmpName,EmpNametemp,EmpNO,EmpNOtemp,EmpAddress), newly created columns must have original data type other then binary oneUpdate these columns with decrypted dataBackup and Restore database on New Server and remove binary columns as well as rename your newly added columns to restore their original namesDrop Symmetric Key, Drop CertificatesCreate New Symmetric Key, New CertificateAdd new columns in each table with binary data type against each column you want to encryptUpdate these columns by adding encrypting data from original columnsRemove old columns against whom you have added binary columns, and also rename your encrypted columns with their original one |
 |
|
|
irvo1968
Starting Member
2 Posts |
Posted - 2008-11-13 : 03:20:59
|
| Thanks, I have done as you suggest and all works well apart from a couple of asp pages that pull in and decrypt an email address. If I run the query directly on the database it works fine but the same code within ASP returns NULL. Other pages that decrypt the email address along with some other data work fine! I have tried various methods of casting and got one of the pages to work by double casting first as varchar then to nvarchar. Is the sql string interpreted differently through ASP? |
|
|
|
|
|
|
|
|
Database Migration Encryption Transfer