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 |
|
Steffi1013
Starting Member
3 Posts |
 Posted - 2007-08-27 : 15:51:16
|
| I need help with the syntax for encrypting columns for two tables. The scenario that I have is two tables with CCnum field in them which needs to be encrypted. For certain process I insert the credit card number into the 1st table and then move the data into the second table; but in other processes I just insert the credit card number into the second table and do nothing with the first table.So both columns in each table needs to be encrypted, but what I am finding is that I have to decrypt the data by putting it in a #temp table from the first table and THEN doing an insert and encrypting it for the second table. Because if I don't decrypt the data from the first table, it is double encrypted in the second table and I am unable to decrypt it.Is there an easier way that I am just not seeing? By the way I am using certificates and then symetric keys to do the encrypt. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-08-27 : 16:02:27
|
| why don't you just have a single table with CCnum in it and reference this single table from your other tables. Single point of encryption and referential integrity too (use Foreign keys).-ec |
 |
|
|
Steffi1013
Starting Member
3 Posts |
Posted - 2007-08-27 : 16:04:33
|
I wish I could but this is a legacy database and I have inherited it and this is the way the application works right now (and of course they do not want to change how the application works) :-( |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-08-27 : 16:16:21
|
| so, if I read this correctly, you only have an issue in the situations where you need to insert CCnum into both tables. Right?Can't you simply insert this value into both tables in the same transaction? -ec |
|
|
|
Jenda
Starting Member
29 Posts |
Posted - 2007-08-27 : 19:15:52
|
quote:
Originally posted by Steffi1013
I wish I could but this is a legacy database and I have inherited it and this is the way the application works right now (and of course they do not want to change how the application works) :-(
If you rename the two tables, move the encrypted column into a third table, create views that'll look exactly like the old tables used to and use INSTEAD OF triggers you should be able to fix the issue without needing any further changes. |
|
|
|
Steffi1013
Starting Member
3 Posts |
Posted - 2007-08-28 : 09:10:19
|
| Jenna:Let me see if I understand you correctly... I have two tables "Orders" and "Processing"... you are suggesting that I rename them both and then create a view that is identical to them and place an INSTEAD OF trigger on both those tables?? Or where are you suggesting that I place the trigger?? |
|
|
|
|
|
|
EncryptByKey Script Problems