SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
leaders_j
leaders_j
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 97
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?
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72993 Visits: 40959
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!
leaders_j
leaders_j
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 97
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



Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72993 Visits: 40959
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!
leaders_j
leaders_j
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 97
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


Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72993 Visits: 40959
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!
leaders_j
leaders_j
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 97
Thank you very much for your assistance.
I ended up using CLR for the two functions.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search