August 27, 2012 at 8:10 am
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?
August 27, 2012 at 8:18 am
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
August 27, 2012 at 8:39 am
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
August 27, 2012 at 8:58 am
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
August 27, 2012 at 12:28 pm
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
August 27, 2012 at 12:52 pm
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
August 27, 2012 at 2:22 pm
Thank you very much for your assistance.
I ended up using CLR for the two functions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply