Solved

Decryption of RSA encrypted data possible in T-SQL?

Posted on 2007-04-09
4
657 Views
Last Modified: 2013-11-06
I am using sql server 2000. In one of my tables on the webserver database, we store confidential information. The asp.net application encrypts the information before storing it, using the build in .NET cryptography.RSA class.

Now I want to import order data into our internal database, and here I want to have the encrypted fields decrypted. Currently, we import by creating a csv-file via a webfrontend on the webserver, and the asp code generating the .csv decrypts the encrypted fields. The file is ftp-ed and imported into our intnernal DB.

Works fine, but we want to skip the steps of manually creating the .csv file, ftp-ing it and importing it.
 I thought of setting up a linked server to our webdatabase and using directly inserting the new rows into our internal database using sql  statements. But I dont know how to decrypt the encrypted fields in this solution.

My Question:
* Is there a way to decrypt from T-SQL (for RSA encrypted data)?
* Or is there a complely different road to automatically import new rows without any manual steps, but with decrypting the encrypted fields?

Any suggestions?

0
Comment
Question by:dready
  • 2
4 Comments
 
LVL 5

Expert Comment

by:drtoto82
ID: 18880977
Actually, I don't know if my way will work with encryption or not. I haven't tried it before on ecypted data.
But u can then just schedule a script to decrypt the data before using it .
My was is to use a scheduled DTS Package :
U configure the form of your import or export, what cells to access in your database , their mapping , ....etc
I recommend that u use the wizard first untill u get the point and get used to it. If u need more , u can begin later creating ur sql scripts , But believe me , the wizard is very good.

Check these URLS :
(This one will work well. )
. http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
. http://msdn2.microsoft.com/en-us/library/aa176528(SQL.80).aspx
 . http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/
. http://support.microsoft.com/kb/269074
.
I think they are much more than enough, BUT , If u still need more help, plz let me know.
0
 
LVL 3

Expert Comment

by:JipFromParis
ID: 18880995
1°) Is there a way to decrypt from T-SQL (for RSA encrypted data)?
No there isn't as of SQL Server 2000. Would you use a SQL Server 2005 you would be able to create a C# stored procedure that would handle the job, however this would still be a custom solution.
2°) Or is there a complely different road to automatically import new rows without any manual steps, but with decrypting the encrypted fields?
Still falling back to SQL Server 2005, I would recommend considering SQL Server Service Broker. On the webserver DB you would queue orders, including encrypted content. On your internal database you would setup a queue reader that would handle the decryption and populate the database. As for question 1, the queue reader could be a C# stored procedure.
0
 
LVL 11

Author Comment

by:dready
ID: 18881759
Thanks for your comments~!
@ drtoto82: I do have quite a bit of experience using DTS packages, but the question is how to decrypt the data, and  I don't see how to do that from within a DTS package. You wrote: "But u can then just schedule a script to decrypt the data before using it .". This is exactly what I'd like to know how to do that, so if you have any ideas?

@Jip:
These are some good reasons for us to consider upgrading to SQL server 2005, but for now we are stuck with 2000...

Any other suggestions anyone?
0
 
LVL 3

Accepted Solution

by:
JipFromParis earned 500 total points
ID: 18882953
As far as I understand, the obvious path is to define a DTS package which first task will create the CSV file, a second "Execute Process Task" would handle decryption, an a third task will be responsible for FTP-ing the decrypted file.
Regarding the "Execute Process Task", you claim to own the source code for decryption into your webserver. Thus, it should be relatively easy to extract the given source and to wrap it in a .Net console application that would handle the job. This application would take a partially encrypted CSV file and would build a clear text one for consumption by next step.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Article by: btan
Provide an easy one stop to quickly get the relevant information on common asked question on Ransomware in Expert Exchange.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now