Is it possible to decrypt data in SQL if it was encrypted in .net?

Posted on 2008-10-16
Last Modified: 2012-05-05
Is it possible, using either SQL2000 or SQL2005, to decrypt data that was encrypted by the front-end .net web code? (On the website, data was encrypted before being imported into database table).

I'm not familiar with web, but I do know that PasswordDerviceBytes and Rijndael was used to encrypt:
PasswordDeriveBytes Object = PasswordDeriveBytes(ThePassword, new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,  0x65, 0x64, 0x76, ....});
Rijndael alg = Rijndael.Create();
alg.Key = Key;
alg.IV = IV;

I have access to the data and know ThePassword. Using the password, is there a SQL script that can decrypt the data? (The ultimate goal is to deliver this data in a file, with the data in un-encrypted format so the users can read it)

I'm not sure if this is possible? A similar question was asked here but never answered:


Question by:trpnbillie
  • 2
LVL 37

Assisted Solution

momi_sabag earned 150 total points
ID: 22738081
if you can decrypt the data using .net code then you can create a clr stored procedure or clr function (clr functions/procedures are sql server procedures/functions that are written in .net code instead of tsql)
this feature is available in sql 2005 and it can solve your problem

Accepted Solution

Abh4IT earned 350 total points
ID: 22738788

From SQL 2005 it supports CLRHosting that is SQL can run programs written for CLR. Therefore the same application could be loaded into SQL Server andexecuted in SQL Server. This allows you to reuse the same code written for CLR.
Some posting on this aspect
And this one shows how to create UDF-User Defined Functions using .NET

Author Comment

ID: 22742472
Thank you both. I am going to try to implement this and will report back my findings!

Author Comment

ID: 22749621
You have provided the solution! Using a CLR stored procedure is allowing me to call the decryption method that is in the .NET code. I am running into a another problem in that the data is only 75% decrypted. I'll open a separate question for that as I believe it may be related to the data being from a SQL 2000 database.  (I imported the data from SQL2000 to a SQL2005 db in order to use CLR) THANKS AGAIN!

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Agent Timeout 5 58
Query to Add Late Tolerance 10 67
Securing Access to Specific Folders 6 49
LAG_ROWID - how do I get the right order using this query? 2 2
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Encryption for Business Encryption ( ensures the safety of our data when sending emails. In most cases, to read an encrypted email you must enter a secret key that will enable you to decrypt the email. T…
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…
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 …

770 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