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 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. )
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
sql server insert 12 30
SQL SELECT query help 7 41
T-SQL:  Collapsing 9 25
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Worried about if Apple can protect your documents, photos, and everything else that gets stored in iCloud? Read on to find out what Apple really uses to make things secure.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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