SQL server encryption – Asymmetric Keys

In the previous blog we learnt about symmetric key encryption. We also learnt through an example how data is stored in varbinary form after encryption and how the original text can be retrieve using the corresponding decrypt function.

http://sqlandmorewithkruti.blogspot.com/2015/10/sql-server-encryption-symmetric-keys.html

In this blog we will see something similar with the same table structures using Asymmetric keysAs stated earlier Asymmetric keys consists of public keys and private keys. Each key can decrypt data encrypted by the other. This is a fairly resource intensive operation as compared to Symmetric keys. This option may be chosen over the previous if we need a higher degree of data protection.

1) Create Database Master Key 

2) Encrypt Data

• Create Asymmetric Key
• Open Key
• Encrypt Data
• Close Key

3) Decrypt Data

• Open Key
• Dencrypt Data
• Close Key

1) Create Database Master Key (DMK)

  USE TESTDB;

  GO

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1211!pwD##';

  GO

2)  Encrypt Data

I have a table with the below structure

CREATE TABLE [dbo].[BankDemo1](

            [BankID] [tinyint] NOT NULL,

            [BankName] [varchar](10) NOT NULL,

                                [BankAmt] [varchar](10) NOT NULL,

 CONSTRAINT [PK_BankDemo1] PRIMARY KEY CLUSTERED

(

            [BankID] ASC

)

) ON [PRIMARY]

BankID	BankName	BankAmt
1	abc	1000
2	def	2000
3	hij	3000

We shall be encrypting the [BankAmt] column. But the cipher data generated during encryption can only be of type varbinary. So the first step is for us to change the structure of the table.

 USE TESTDB;

 GO

 ALTER TABLE BankDemo1

 ADD BankAmtAsymKey varbinary(MAX) NULL

 GO

Now that our table schema is modified we proceed with creation of the asymmetric key

Syntax : https://msdn.microsoft.com/en-us/library/ms174430.aspx

 USE TESTDB;

 GO

-- Create symmetric key 

CREATE ASYMMETRIC KEY TestAsymmKey

 WITH ALGORITHM = RSA_512

 ENCRYPTION BY PASSWORD = '1211!pwD##';

 GO

Let’s do the trick!!!

USE TESTDB

GO

-- Open the asymmetric key

OPEN ASYMMETRIC KEY TestAsymmKey

DECRYPTION BY PASSWORD = '1211!pwD##';

GO

--Encrypt Data

UPDATE BankDemo1

SET BankAmtSymKey = ENCRYPTBYASYMKEY(ASYMKEY_ID('TestAsymmKey'),BankAmt)

GO

 -- Close the asymmetric key

CLOSE ASYMMETRIC KEY TestAsymmKey;

GO

Let’s see the magic we created!!!

BankID	BankName	BankAmtSymKey
1	Abc	0x00F5998B8C856F4E92697DBA96BD44AD01000000547D8E1730DEF80F F7B2055C8478DA0DDCF7FCAC8E1C23B9C3E7F4A9D1712F75
2	Def	0x00F5998B8C856F4E92697DBA96BD44AD0100000015C40BFAD6271818 6D1EC41375B167FE96C96CB177F8A171C2C81E48E171D1C9
3	Hij	0x00F5998B8C856F4E92697DBA96BD44AD01000000738A3FA8152E5CD6 6335CF2231B37BD5FBFD94BECD0CF056389E6614E5E40037

In the current example I’m saving both the text prior to encryption under the [BankAmt] column and the cipher text in [BankAmtAsymKey] column. In practical scenarios it’s obvious to use a mechanism like stored procedures to perform the encryption task. This stored procedure would ideally be taking inputs for data to be encrypted and storing only the cipher text in the table.

    3) Dencrypt Data

      

      Well since we used ENCRYPTBYASMKEY function your guess is right we will use the corresponding DENCRYPTBYASMKEY function to retrieve the data.

 USE TESTDB

 GO

  -- Open the asymmetric key

 OPEN ASYMMETRIC KEY TestAsymmKey

 DECRYPTION BY PASSWORD = '1211!pwD##';

 GO

 -- Decrypt Data

 SELECT BankID,BankName,

 (CONVERT(CHAR(52), DECRYPTBYASYMKEY(ASYMKEY_ID('TestAsymmKey'),   BankAmtAsymKey, N'1211!pwD##')))

 AS 'Decrypted Amount' FROM BankDemo1

 -- Close the asymmetric key

CLOSE SYMMETRIC KEY TestSymmKey;

GO

Now that we are clear on using both symmetric and asymmetric keys with their respective functions we shall see how to use certificates which encapsulate key information in the next blog.