CODEWITHSUNDEEP
c#sqlmd5
Erkan
Erkan

Reputation: 35

SQL and C# Encryption

I am using an encryption in C# like this:

// Step 1. We hash the passphrase using MD5
// We use the MD5 hash generator as the result is a 128 bit byte array
// which is a valid length for the TripleDES encoder we use below  
var utf8 = new UTF8Encoding();
var hashProvider = new MD5CryptoServiceProvider();
var tdesKey = hashProvider.ComputeHash(utf8.GetBytes(passwordPhrase));

// Step 2. Create a new TripleDESCryptoServiceProvider object
var tdesAlgorithm = new TripleDESCryptoServiceProvider
                        {
                            Key = tdesKey,
                            Mode = CipherMode.ECB,
                            Padding = PaddingMode.PKCS7
                        };

// Step 3. Attempt to encrypt the string
var dataToEncrypt = utf8.GetBytes(message);
var encryptor = tdesAlgorithm.CreateEncryptor();

return Convert.ToBase64String(
    encryptor.TransformFinalBlock(
        dataToEncrypt, 0, dataToEncrypt.Length));

I want to use the same process in SQL Server. So I mean that an encrypted message from C# code and encrypted message from SQL Server must be the same string.

For example, 683846 will become fN8r09iDu4c= in SQL Server too.

How can I do this?

Upvotes: 1

Views: 1810

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

You can only achieve this if you use the same C# code in the server too, via SQLCLR. The native SQL encryption functions do not create same output as your C# code (even when same key and initialization vector is used). Native SQL encryption can only be decrypted by SQL.

Upvotes: 1

oleksii
oleksii

Reputation: 35925

You can use ENCRYPTBYPASSPHRASE for Triple DES encryption and HASHBYTES to derive key material from passphrase.

You would have to store passphrase in SQL Server somewhere and I am not sure what is the best way to do that. Alternatively you can accept passphrase as a parameter passed in from the user.

Upvotes: 0

Related Questions