Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Decryption of RSA encrypted data possible in T-SQL?

Posted on 2007-04-09
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 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.

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

808 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