Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help: Encryption and Decryption in T-SQL and VB.NET Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 8:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
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?

Post #1350408
Posted Monday, August 27, 2012 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 12,744, Visits: 31,085
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1350415
Posted Monday, August 27, 2012 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
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


Post #1350434
Posted Monday, August 27, 2012 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 12,744, Visits: 31,085
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1350439
Posted Monday, August 27, 2012 12:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
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

Post #1350524
Posted Monday, August 27, 2012 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 12,744, Visits: 31,085
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1350536
Posted Monday, August 27, 2012 2:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
Thank you very much for your assistance.
I ended up using CLR for the two functions.
Post #1350581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse