Decrypt string using SQL

[mwa]

Not sure if this should be posted here or in SQL forum, but here goes:

I've used the following code in an ASP.net application to Encrypt a string...

Public Shared Function Encryptor(ByVal strText As String, ByVal strEncrKey _
         As String) As String
            'will encrypt a string, in a way that can be decrypted
            Dim byKey() As Byte = {}
            Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
            Try
                byKey = System.Text.Encoding.UTF8.GetBytes(strEncrKey)

                Dim des As New DESCryptoServiceProvider
                Dim inputByteArray() As Byte = Encoding.UTF8.GetBytes(strText)
                Dim ms As New MemoryStream
                Dim cs As New CryptoStream(ms, des.CreateEncryptor(byKey, IV), CryptoStreamMode.Write)
                cs.Write(inputByteArray, 0, inputByteArray.Length)
                cs.FlushFinalBlock()
                Return Convert.ToBase64String(ms.ToArray())

            Catch ex As Exception
                Return ex.Message
            End Try

        End Function

The return from this function is then written to a SQL Server database. Then I use the reverse of this to decrypt the string in ASP.net. This all works just fine. However, I would also like to be able to decrypt this string via a stored procedure or SQL function, without having to use asp.net. Is this possible? If so, suggestions on how to get started?



mwa
<><

 

[ca8msm]

You would be better of using either one of the technologies to encrypt/decrypt the data, not both. That said, you could always create a CLR function to do the work, then it will be available as a function call in SQL.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[mwa]

Yeah, I agree that it is better to keep it one language/platform. The SQL side of it is simply for reporting (select only) purposes. The main application is an ASP.Net application.

I'm not sure that I can use the CLR solution. I'm using SQL 2005, but I am only using VS 2003. From what I just read, it seems like I need SQL 2005 and VS 2005.

mwa
<><

 

[ca8msm]

No, the version of Visual Studio you have is irrelevant. You can use Notepad if you wish as long as you compile it to a .NET version 2.0 or above DLL. Here's an example of how to then use that DLL:

http://aspnetlibrary.com/articledet...ng-CLR-to-access-.NET-functions-in-SQL-Server

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[mwa]

OK, You're right... I went back and re-read the article...

The easiest way to write managed code for SQL Server 2005 is to use Visual Studio 2005

Let me look into your article.

mwa
<><