Decryption of RSA encrypted data possible in T-SQL?

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?

LVL 11
dreadyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

drtoto82Commented:
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
JipFromParisCommented:
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
dreadyAuthor Commented:
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
JipFromParisCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.