Blog Post

SQL server encryption – Symmetric Keys

,

In the previous blog we learnt about encryption and a brief about the SQL server option provided for data protection through encryption. 


In this blog we will learn with a demo how to use the first option Symmetric Keys for encryption.As stated earlier symmetric keys use the same key to encrypt and decrypt data.
When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. 
In this blog we will be using one protected with password.  In later modules we will be using asymmetric keys and certificates

1) Create Database Master Key

2) Encrypt Data
  • Create Symmetric Key
  • Open Key
  • Encrypt Data
  • Close Key

3) Decrypt Data

  • Open Key
  • Dencrypt Data
  • Close Key


 1)  Create Database  Master Key

USETESTDB;
GO
CREATEMASTER 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 BankAmtSymKeyvarbinary(MAX) NULL
GO
               
Now that our table schema is modified we proceed with creation of the symmetric key

USE TESTDB;
GO
CREATESYMMETRIC KEY TestSymmKey
WITHALGORITHM = AES_256
ENCRYPTIONBY PASSWORD = '1211!pwD##';
GO
Let’s do the trick!!!
USETESTDB
GO

 -- Open the symmetric key
OPENSYMMETRIC KEY TestSymmKey
DECRYPTIONBY PASSWORD = '1211!pwD##';

 --Encrypt Data
GO
UPDATE BankDemo1
SETBankAmtSymKey =ENCRYPTBYKEY (Key_GUID('TestSymmKey'),BankAmt)
GO

 -- Close the symmetric key
CLOSESYMMETRIC KEY TestSymmKey;

GO
Let’s see the magic we created!!!

BankID
BankName
BankAmtSymKey
1
Abc
0x00F5998B8C856F4E92697DBA96BD44AD01000000547D8E1730
DEF80FF7B2055C8478DA0DDCF7FCAC8E1C23B9C3E7F4A9D1712F75
2
Def
0x00F5998B8C856F4E92697DBA96BD44AD0100000015C40BFAD6
2718186D1EC41375B167FE96C96CB177F8A171C2C81E48E171D1C9
3
Hij
0x00F5998B8C856F4E92697DBA96BD44AD01000000738A3FA8152
E5CD66335CF2231B37BD5FBFD94BECD0CF056389E6614E5E40037
  
In the current example have saved both the text prior to encryption under the [BankAmt] column and the cipher text in [BankAmtSymKey] column. 
In practical scenarios it’s obvious to use a mechanism like stored procedures to perform the encryption task. This stored procedure would ideally taking inputs for data to encrypt and storing only the cipher text in the table.

3) Dencrypt Data

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

USETESTDB
GO

 -- Open the symmetric key
OPENSYMMETRIC KEY TestSymmKey
DECRYPTIONBY PASSWORD = '1211!pwD##';
GO

-- Decrypt Data
SELECTBankID,BankName,
CONVERT(varchar, DecryptByKey(BankAmtSymKey)) AS 'Decrypted Amount'
FROMBankDemo1
 -- Close the symmetric key
CLOSESYMMETRIC KEY TestSymmKey;
GO
In the next blog we shall see the same example but this time we shall be using asymmetric keys.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating