Blog Archives
mssql aes function encrypt decrypt in microsoft sql
Simple symmetric encryption in MSSQL. Copied from MS references below:
USE AdventureWorks2012;
--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 = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE Sales09
WITH SUBJECT = 'Customer Credit Card Numbers';
GO
CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
GO
-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.
SELECT CardNumber, CardNumber_Encrypted
AS 'Encrypted card number', CONVERT(nvarchar,
DecryptByKey(CardNumber_Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
AS 'Decrypted card number' FROM Sales.CreditCard;
GO
References:
Quick .Net Encryption Reference
The code below represents a very basic .NET encryption class which has been tested and should work in your application – simply plug and play. 🙂
Contains two static methods that can be called without needing to instantiate the class.
Keep in mind the initialization vector below (indicated by rgbIV) is generic, and you will need to come up with your own. Remember not to share this. Even if the password is compromised, the attacker would also need to know the initialization vector to crack your value.
Also note the code which has been commented out. This illustrates cases where passwords and/or IV can be statically set in the class and/or shared based on value passed in for password parameter.
Sharing IV and password or storing either statically is a security risk and could cause errors depending on byte differences of the values. If you statically store these values, you will still create secure cipher text, but it will be much easier to crack.
Enjoy. 😉
using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;
namespace AIS.Common.Crypto
{
public static class Rijndael
{
public static string Encrypt(string ClearText,string password)
{
byte[] clearTextBytes = Encoding.UTF8.GetBytes(ClearText);
System.Security.Cryptography.SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();
MemoryStream ms = new MemoryStream();
byte[] rgbIV = Encoding.ASCII.GetBytes("example");
//byte[] key = Encoding.ASCII.GetBytes("longerexample");
//byte[] rgbIV = Encoding.ASCII.GetBytes(password);
byte[] key = Encoding.ASCII.GetBytes(password);
CryptoStream cs = new CryptoStream(ms, rijn.CreateEncryptor(key, rgbIV),
CryptoStreamMode.Write);
cs.Write(clearTextBytes, 0, clearTextBytes.Length);
cs.Close();
return Convert.ToBase64String(ms.ToArray());
}
public static string Decrypt(string EncryptedText, string password)
{
byte[] encryptedTextBytes = Convert.FromBase64String(EncryptedText);
MemoryStream ms = new MemoryStream();
System.Security.Cryptography.SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();
byte[] rgbIV = Encoding.ASCII.GetBytes("example");
//byte[] key = Encoding.ASCII.GetBytes("longerexample");
//byte[] rgbIV = Encoding.ASCII.GetBytes(password);
byte[] key = Encoding.ASCII.GetBytes(password);
CryptoStream cs = new CryptoStream(ms, rijn.CreateDecryptor(key, rgbIV),
CryptoStreamMode.Write);
cs.Write(encryptedTextBytes, 0, encryptedTextBytes.Length);
cs.Close();
return Encoding.UTF8.GetString(ms.ToArray());
}
}
}
References:
Wikipedia – Encryption, http://en.wikipedia.org/wiki/Encryption
Out of Office Reply 