Decryption of RSA encrypted data possible in T-SQL?

Posted on 2007-04-09
Medium Priority
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?

Question by:dready
  • 2

Expert Comment

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.

Expert Comment

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.
LVL 11

Author Comment

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?

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?

Accepted Solution

JipFromParis earned 2000 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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

619 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