Colum-level encryptions

Colum-level encryptions are introduced at Microsoft SQL Server 2005.

Here in this article I am trying to demonstrate how to implement the columns level encryption.

Column-level encryption is implemented as a series of built-in functions and a key management hierarchy. 

1.    EncryptByCert() and DecryptByCert()

Encrypts and decrypts data using the public key of a certificate to generate a private asymmetric key

2.    EncryptByAsymKey() and DecryptByAsymKey()

Encrypts and decrypts data using an asymmetric key

3.    EncryptByKey() and DecryptByKey()

Encrypts and decrypts data by using a symmetric key

4.    EncryptByPassphrase() and DecryptByPassphrase()

Encrypts and decrypts data by using a passphrase to generate a symmetric key

Using a passphrase

In our first example we are going to encrypt a column using a passphrase. It is simple to implement but it is not secured.

Step-1 [ Our Base Table ]

CREATE TABLE tbl_CardInfo

      (idno    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

       Cname   VARCHAR(50) NOT NULL,

       CardNo  VARCHAR(16) NOT NULL)

VALUES ('Sukamal Jana', '1234567891234567'),

         ('Sangram jit',  '1234567891254321'),

         ('Rajesh Das',   '4514367891254321')

GO

Step-2 [ Now Create the Master Key ]

IF NOT EXISTS

    (SELECT * FROM sys.symmetric_keys

            WHERE symmetric_key_id = 101)

BEGIN           

    CREATE MASTER KEY ENCRYPTION

       BY PASSWORD = 'joydeep@123'

END      

GO

Step-3 [ Convert data type as VARBINARY ]

As in our base table the "CardNo" columns contain data type "VARCHAR", it takes the clear text information. To encrypt it, we must convert the columns data type as "VARBINARY" data type.

SELECT * FROM tbl_CardInfo

idno    Cname                        CardNo

1          Sukamal Jana             1234567891234567

2          Sangram jit                  1234567891254321

3          Rajesh Das                 4514367891254321

As we can see the card the "CardNo" Columns contains clear text.

SELECT idno,

       Cname,

       CardNo_encrypt = CONVERT(VARBINARY(256),CardNo)

       INTO tbl_CardInfo_Encrypt

FROM   tbl_CardInfo  

WHERE  1=2

Step-4 [ Now copying data By using EncryptByPassphrase() ]

BEGIN

      DECLARE @passphrase VARCHAR(MAX)='joydeep@123'

     

      INSERT INTO tbl_CardInfo_Encrypt(Cname, CardNo_encrypt)

      SELECT Cname,

               CardNo_encrypt = EncryptByPassPhrase(@passphrase, CardNo)

      FROM   tbl_CardInfo

END

Step-5 [ Now use SELECT to display records ]

SELECT * FROM tbl_CardInfo_Encrypt

Output:

idno        Cname                     CardNo_encrypt

1              Sukamal Jana            0x01000000481B8F.....27

2              Sangram jit                0x01000000B4DD5...69

3              Rajesh Das               0x01000000C6BE7A...9D

The card number columns are encrypted.

Step-6 [ Now to Decrypt it by using DecryptByPassphrase() ]

BEGIN

      DECLARE @passphrase VARCHAR(MAX)='joydeep@123'

     

      SELECT idno,

             Cname,

               CardNo_encrypt = CONVERT(VARCHAR(16),DecryptByPassPhrase(@passphrase, CardNo_encrypt))

      FROM   tbl_CardInfo_Encrypt

END

Output:

idno    Cname                        CardNo_encrypt

1          Sukamal Jana             1234567891234567

2          Sangram jit                  1234567891254321

3          Rajesh Das                 4514367891254321

 Using Certificates

Certificates are used as encryption keys, used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates.

The key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key.

After you create the certificate, the next step is to create a symmetric key that will be encrypted by the certificate. You can use many different algorithms for encrypting keys.

A number of different algorithms can be employed for encrypting symmetric  key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

The primary benefit of certificates is that they relieve hosts of the need to maintain a set of passwords for individual subjects. Instead, the host merely establishes trust in a certificate issuer, which may then sign an unlimited number of certificates.

Step-1 [ Creating Certificate ]

CREATE CERTIFICATE cer_Cardinfo

   WITH SUBJECT = 'Credit Card Info'

GO

Step-2 [Create a symmetric key that will be encrypted by the certificate ]

CREATE SYMMETRIC KEY symKey_CardInfo

       WITH ALGORITHM = AES_256

       ENCRYPTION BY CERTIFICATE cer_Cardinfo;

GO

Step-3 [ Delete all the records from Table tbl_CardInfo_Encrypt ]

TRUNCATE TABLE tbl_CardInfo_Encrypt

Step-4 [ Symmetric key associated with the certificate to encrypt by EncryptByKey() ]

OPEN SYMMETRIC KEY symKey_CardInfo

     DECRYPTION BY CERTIFICATE cer_Cardinfo

GO

INSERT INTO tbl_CardInfo_Encrypt

           (

                        Cname,

                        CardNo_encrypt

           )

SELECT Cname,

       CardNo_encrypt = EncryptByKey(KEY_GUID('symKey_CardInfo'), CardNo)

FROM tbl_CardInfo

GO

SELECT * FROM tbl_CardInfo_Encrypt

idno        Cname                            CardNo_encrypt

1              Sukamal Jana                   0x0064EB22A...8B5D

2              Sangram jit                       0x0064EB22A...B621A

3              Rajesh Das                      0x0064EB22...7B0D

Step-5 [Specifies the appropriate certificate can retrieve the data by using DecryptByKey ]

OPEN SYMMETRIC KEY symKey_CardInfo

     DECRYPTION BY CERTIFICATE cer_Cardinfo

GO

SELECT idno,

       Cname,

       CardNo = convert(nvarchar(MAX), DecryptByKey(CardNo_encrypt))

FROM tbl_CardInfo_Encrypt

Output:

idno    Cname                        CardNo_encrypt

1          Sukamal Jana             1234567891234567

2          Sangram jit                  1234567891254321

3          Rajesh Das                 4514367891254321

Step-6 [ To CLOSE the Key ]

CLOSE SYMMETRIC KEY symKey_CardInfo

The keys defined in a database can be viewed through the system catalog table, sys.symmetric_keys

SELECT name,

       pvt_key_encryption_type,

       issuer_name,

       subject,

       expiry_date = CAST(expiry_date as DATE),

       start_date = CAST(start_date as DATE)

FROM sys.certificates

Hope you like it.

Posted by: MR. JOYDEEP DAS