Help: Encryption and Decryption in T-SQL and VB.NET

  • Hello,

    I am new to T-SQL Encryption and Decryption. I wrote two functions in VB.NET to encrypt and to decrypt using DES Algorithm and UTF-8 encoding. I tried using T-SQL to achieve the same result as the one in VB.NET but the result is totally different from the result of VB.NET functions.

    Is there a way to achieve the same result using DES algorithm or any other encryption algorithm on VB.NET and on T-SQL?

  • probably. If you can give an example of the unencrypted value / encrypted value as it would be processed from vb.net, we can see if there is a matching algorythm in SQL.

    if it doesn't, it's actually very easy to create a CLR (Common Language Runtime) function for SQL which calles the actual Sub or Function in VB.NET and returns the result, so you can simply re-use the exisitng functionality.

    I had to do exactly that for an AES class we use in VB.NET in one of our applications.;

    if you assume the code below Encrypt() and Decrypt() are calling VB.NET code, here's a n existing snippet of a working example.

    #Region "AES Encrypt Descrypt"

    <Microsoft.SqlServer.Server.SqlFunction()> _

    Public Shared Function CLR_EncryptAES(<SqlFacet(MaxSize:=512)> ByVal TextString As SqlString, <SqlFacet(MaxSize:=512)> ByVal Password As SqlString) As SqlString

    Dim _sResults As SqlString

    _sResults = New SqlString(Encrypt(TextString.ToString, Password.ToString))

    Return _sResults

    End Function

    <Microsoft.SqlServer.Server.SqlFunction()> _

    Public Shared Function CLR_DecryptAES(<SqlFacet(MaxSize:=512)> ByVal EncryptedString As SqlString, <SqlFacet(MaxSize:=512)> ByVal Password As SqlString) As SqlString

    Dim _sResults As SqlString

    _sResults = New SqlString(Decrypt(EncryptedString.ToString, Password.ToString))

    Return _sResults

    End Function

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Here, the VB.NET Code

    Imports System.Security.Cryptography

    Imports System.IO

    Imports System.Text

    Class EncryptDecrypt

    Public Shared key As Byte()

    Public Shared IV As Byte() = New Byte(7) {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}

    Public Shared Function Decrypt(ByVal stringToDecrypt As String, ByVal sEncryptionKey As String) As String

    Dim inputByteArray As Byte()

    Try

    key = System.Text.Encoding.UTF8.GetBytes(sEncryptionKey)

    Dim des As DESCryptoServiceProvider = New DESCryptoServiceProvider()

    inputByteArray = Convert.FromBase64String(stringToDecrypt)

    Dim ms As MemoryStream = New MemoryStream()

    Dim cs As CryptoStream = New CryptoStream(ms, des.CreateDecryptor(key, IV), CryptoStreamMode.Write)

    cs.Write(inputByteArray, 0, inputByteArray.Length)

    cs.FlushFinalBlock()

    Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8

    Return encoding.GetString(ms.ToArray())

    Catch e As Exception

    Return e.Message

    End Try

    End Function

    Public Shared Function Encrypt(ByVal stringToEncrypt As String, ByVal strKey As String) As String

    Try

    key = System.Text.Encoding.UTF8.GetBytes(strKey)

    Dim des As DESCryptoServiceProvider = New DESCryptoServiceProvider()

    Dim inputByteArray As Byte() = Encoding.UTF8.GetBytes(stringToEncrypt)

    Dim ms As MemoryStream = New MemoryStream()

    Dim cs As CryptoStream = New CryptoStream(ms, des.CreateEncryptor(key, IV), CryptoStreamMode.Write)

    cs.Write(inputByteArray, 0, inputByteArray.Length)

    cs.FlushFinalBlock()

    Return Convert.ToBase64String(ms.ToArray())

    Catch e As Exception

    Return e.Message

    End Try

    End Function

    End Class

  • well, my simple copy paste of your class, and a button to call it returns an error form vb.net;

    All i wanted was a string and it's encrypted value, with a certain passphrase, so i could test DES and DES Triple to see if it's the same algrythm, but you poasted code.

    Specified key is not a valid size for this algorithm.

    Invalid character in a Base-64 string.

    I did what i could with your code, but it doesn't work for me.

    Private Sub btnTestEncryption_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestEncryption.Click

    Dim s As String = "Encryption Test"

    Dim p As String = "Secret Password"

    Dim r As String = EncryptDecrypt.Encrypt(s, p)

    Debug.Print(r)

    Dim t As String = EncryptDecrypt.Decrypt(r, p)

    Debug.Print(t)

    MsgBox("Done.")

    End Sub

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Use this instead. The key should be 8 characters in length

    Private Sub btnTestEncryption_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestEncryption.Click

    Dim s As String = "Encryption Test"

    Dim p As String = "Password"

    Dim r As String = EncryptDecrypt.Encrypt(s, p)

    Debug.Print(r)

    Dim t As String = EncryptDecrypt.Decrypt(r, p)

    Debug.Print(t)

    MsgBox("Done.")

    End Sub

  • ok I see that works;

    With my example values, I also saw that SQL DES and TRIPLE_DES are not interchangable with the results from your function;

    I think you'll have to wrap it into a CLR like my example suggested; you might have to make sure adding a CLR is OK at your biz as well.

    You might also consider switching to the much stronger cyphers in SQL Server and replacing what you currently use.

    my test code: when i compared the script results, they are nowehere near the values yours puts out for the same info.

    unless i'm doing it wrong, that is.

    --t13Oh9nvDTh3Ac+l9nUQqA==

    --Encryption Test

    --If there is no master key, create one now.

    IF NOT EXISTS

    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

    CREATE MASTER KEY ENCRYPTION BY

    PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

    GO

    -- create symmetric key 'SecureSymmetricKey'

    -- using the DESX encryption algorithm

    -- and encrypt the key using the password

    -- 'StrongPassword'

    CREATE SYMMETRIC KEY SecureSymmetricKey

    WITH ALGORITHM = DES

    ENCRYPTION BY PASSWORD = N'Password';

    -- must open the key if it is not already

    OPEN SYMMETRIC KEY SecureSymmetricKey

    DECRYPTION BY PASSWORD = N'Password';

    CREATE SYMMETRIC KEY SecureSymmetricKey2

    WITH ALGORITHM = TRIPLE_DES

    ENCRYPTION BY PASSWORD = N'Password';

    -- must open the key if it is not already

    OPEN SYMMETRIC KEY SecureSymmetricKey2

    DECRYPTION BY PASSWORD = N'Password';

    -- declare and set varible @STR to store plaintext

    DECLARE @STR NVARCHAR(100)

    SET @STR = N'Encryption Test';

    -- declare and set varible @encrypted_str to store

    -- ciphertext

    DECLARE @encrypted_str VARBINARY(MAX)

    SET @encrypted_str =

    EncryptByKey(Key_GUID('SecureSymmetricKey'), @STR);

    Print @encrypted_str

    SET @encrypted_str =

    EncryptByKey(Key_GUID('SecureSymmetricKey2'), @STR);

    Print @encrypted_str

    /*

    --CLEANUP

    CLOSE SYMMETRIC KEY SecureSymmetricKey2

    CLOSE SYMMETRIC KEY SecureSymmetricKey

    DROP SYMMETRIC KEY SecureSymmetricKey2

    DROP SYMMETRIC KEY SecureSymmetricKey

    */

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you very much for your assistance.

    I ended up using CLR for the two functions.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply