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:

http://msdn.microsoft.com/en-us/library/bb669072.aspx

http://msdn.microsoft.com/en-us/library/ms179331.aspx

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

Follow

Get every new post delivered to your Inbox.