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.
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
CREATETABLE [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
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!!!
USETESTDB
GO
-- Open the asymmetric key
OPENASYMMETRIC KEY TestAsymmKey
DECRYPTIONBY PASSWORD = '1211!pwD##';
GO
--Encrypt Data
UPDATE BankDemo1
SETBankAmtSymKey = ENCRYPTBYASYMKEY(ASYMKEY_ID('TestAsymmKey'),BankAmt)
GO
-- Close the asymmetric key
CLOSEASYMMETRIC 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.
USETESTDB
GO
-- Open the asymmetric key
OPENASYMMETRIC KEY TestAsymmKey
DECRYPTIONBY 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
CLOSESYMMETRIC 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.
