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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy