Same encryption Description for C# and SQL

Ruchi Sharavat

string EncryptionKey = "XYZQWER274";
byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
cs.Close();
}
clearText = Convert.ToBase64String(ms.ToArray());
}
}

I need to send some encrypted url to users somwtimes from C# application and sometime from SQl email functionality.

Then will have to create C# page for that url where i need to decrypt querystring. I am using above encryption method for C# encryption. What can i do in SQL to work exactly similar to this so that the decrypt method may work properly for url wheather is it encrypted via SQL or via C#.

I had tried ENcryptedByKey in sql but that doesn't work to match exactly.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
In that case, you can do sql column data encryption. Check this link for example.
You can add another column for encryption/decryption data.

https://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
Ruchi Sharavat5y ago
Hi Rijwan,

I know about that and i am using that while sending and receiving URl in C# but the problem is I have SQL job as well which is sending email to user to send the URL. In that case how to encrypt this userid value 22 ?
Rijwan Ansari5y ago
Hi Ruchi,

Let me explain how we do in real scenario.
You can easily find several articles on encryption and decryption code in c#. Example:
https://www.c-sharpcorner.com/article/encryption-and-decryption-using-a-symmetric-key-in-c-sharp/

How to implement for url part?
- Let's say one url is like: https:yourwebsite.com/anypage?userId =22
what we do, we encrypt the url parameter userId value 22. then
After encryption url:
https:yourwebsite.com/anypage?userId =OgIZSQiAhBn+g9dS2G0/ew

Logic:
simply, get encrypted string of value (say 22) and build the url with that encrypted value.

Loading from Url.
Get encrypted value with query string and do decryption to get actual value.
After that you can do process.

You don't need to save encrypted value in Database for this purpose. We do encryption in database value for password, card number and likewise, however not for this purpose.
Nitin Sontakke5y ago
I am not answering your question. I am suggesting a work around.

If all your attempts fail and if nobody comes with desirable solution, you can use this as a last resort.

Be aware that it is possible to create a function in C#, put it in a class library project, register that .dll file with Microsoft SQL Server and call that function from within T-SQL code as if it is a SQL Function.

This way you are guaranteed to have exact same encryption working in both.

Here is an article explaining this process:

https://dotnettec.com/call-c-function-from-sql-server/