Create an SP?

Question

Create an SP?

newbieuser

SSCrazy Eights

Points: 9423
More actions
May 8, 2009 at 9:50 am

#384539

Hi all,
We’ll have to fix encryption/decryption data on the production db for our client. Looks like there was some configuration changes on the software end and the program didn’t run correctly. We have venc and vdec procedures for encrypt and decrypt. We have three tables used in this program,
NRP with fields used in this program - ref_no,pym_id,acct
NRPA with fields used in this program - nrplh_key,pym_id,acct
NRPLH with fields used in this program - nrplh_key,ref_no,ship_dt
The program was supposed to encrypt the data based on the nrplh_key and pym_id. But, it has encrypted based on ref_no, pym_id due to some configuration failure. Now, we will have to fix the data and below are the steps:
1.We can get the record for those encrypted incorrectly from this select query:
select ref_no,pym_id,acct from nrplh,nrpa where nrplh.nrplh_key = nrpa.nrplh_key AND acct <> ' ' and ship_dt between '01-JAN-2007' and '01-MAR-2007'
The query returns about 300 encrypted records. First, we have to decrypt this data using ref_no. Execute the vdec proc to decrypt this data using ref_no.
2.Then run venc proc to encrypt nrpa.acct with the correct key nrplh_key and pym_id. I’m not sure if we can do this in a sql script.
3.Update the nrpa.acct with this encrypted data based on the nrplh_key and pym_id.
Please give me your suggestions on how I can get this done, scripting this and give it to the client so they can run this on the production db.
Thanks a lot again for all your help. I'm new to SQL Server I really appreciate your support.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 1

I think my previous thread is very well confusing.

I would like to know if it is possible to create like a script to execute the vdec(decrytion procedure) for the encrypted values (obtained from the above select query). Basically the select query will return ref_no,pym_id and acct values. The vdec procedure should take the input from these values and decrypt them. Can this be done using a sql by execute vdec procedure??

Please help, thanks much.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Do you know how to use your venc/vdec procedures from SQL? If so, can you provide an example?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

I right click on the venc or vdec procedures, execute the procedure and give the input data.

DECLARE@ret_value int,

@out_data varchar(200)

EXEC @ret_val = [dbo].[vdec]

@in_data1 = N'0x008b16f1b5e3ea4eaba24e2348cbf9c501000000fc5b0d5c0',

@in_data2 = N'e2f06f21ff1468953fe1047f3002e61eb0c4cb0e08bdf1ef56b',

@in_data3 = N'80d19af6eecaa4db53e145db9bef3854becd28fb20a857bb6e87',

@in_add_auth = 1,

@in_auth = N'9386871^',

@out_data = @out_data OUTPUT

SELECT@out_data as N'@out_data'

SELECT'Ret Val' = @ret_val

GO

I get the decrypted output.

But for the data fix, I'll have to provide with a script to decrypt the records from the above select query based on the ref_no and pym_id. IS this possible?

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

psangeetha (5/8/2009)
I right click on the venc or vdec procedures, execute the procedure and give the input data.
DECLARE@ret_value int,
@out_data varchar(200)
EXEC @ret_val = [dbo].[vdec]
@in_data1 = N'0x008b16f1b5e3ea4eaba24e2348cbf9c501000000fc5b0d5c0',
@in_data2 = N'e2f06f21ff1468953fe1047f3002e61eb0c4cb0e08bdf1ef56b',
@in_data3 = N'80d19af6eecaa4db53e145db9bef3854becd28fb20a857bb6e87',
@in_add_auth = 1,
@in_auth = N'9386871^',
@out_data = @out_data OUTPUT
SELECT@out_data as N'@out_data'
SELECT'Ret Val' = @ret_val
GO
I get the decrypted output.
But for the data fix, I'll have to provide with a script to decrypt the records from the above select query based on the ref_no and pym_id. IS this possible?

How would you use the with this procedure? Say for instance you had @ref_no, @pym_id, @acct variables with the values from a single row, how would you use it?

Also, how many rows have to be fixed here? And finally, can you post the code for the venc/vdec routines?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]