August 29, 2008 at 1:57 pm
hi,
I need a help or suggestion from you.
I have a table, in that a column is encrypted(SYMMETRIC KEY encryption) in binary data format in the database. I have a SP to encrypt the plain text which receive an input and gives the encrypted data.
I have to copy the whole table to same structured table in the different database as a batch update. I created a SP which receive the source table in XML. Everything working fine except the encrypted data.If try to decrypt the data after the copy, I am not getting the original data.
So have plan to encrypt the plain text once again before insert into the destination table.But stuck in calling the SP in the batch insert(which provide the required encrypted by receiving the plain text). I am not able to convert that procedure to function also.
I tried with cursor by looping each record, encrypting and insert into destination table.Its working but take so much of time.
my questions:
how to maintain the format of encrypted data while passing thru XML, or any way to convert the binary encrypted data to XML string and in the SP again convert it into binary data in batch insert and update.
Is there any possibility to call the stored procedure within the batch insert statement like user defined function
Insert into table_1 values(number, sec_data)
(@row_num,sp_encryp(@plain,@encrpy))
Please advice me 🙁
August 29, 2008 at 2:06 pm
When you say that you are not getting the correct data after the insert, what do you mean? Do you mean that you are able to decrypt the data, but it is the same as when you encrypted it. Or are you saying that you can't decrypt it?
August 30, 2008 at 12:21 am
hi
If i use the XML , batch insert and tried to decrypt the copied(destination table) data I'm getting an empty value. i.e not able to get the original data.
August 30, 2008 at 9:07 am
Has the key been copied to the other database?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply