Cryptographic Function Demo

This post demonstrates the use of a cryptographic function encrypt data within a table. To understand how the crytographic functions can be employed to improve the security of database applications, please review this post.

The first step in the demonstration is to create an empty database within which sensitive data will be housed:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'CryptoFunctionDemo')
DROP DATABASE CryptoFunctionDemo;
GO

CREATE DATABASE CryptoFunctionDemo;
GO

Next, a table will be created which will house some sensitive data:

USE CryptoFunctionDemo;
GO

CREATE TABLE dbo.MySensitiveData (
   Id INT NOT NULL IDENTITY(1,1),
   EncryptedData VARBINARY(256) NOT NULL
   )

To support the encryption and decryption of data, an asymmetric key will be created along with two stored procedures making the encryption and decryption calls on behalf of the application:

CREATE ASYMMETRIC KEY MyCryptoKey
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = 'asd!i36oheQ#wr8iW#%qwei4!orqhq9w7as';
GO

CREATE PROC dbo.spPutSensitiveData @MyData NVARCHAR(25)
AS
   INSERT INTO dbo.MySensitiveData (EncryptedData)
   SELECT 
      ENCRYPTBYASYMKEY(
         ASYMKEY_ID('MyCryptoKey'),
        @MyData
        );
GO

CREATE PROC dbo.spGetSensitiveData @Id int
AS
 SELECT
    CONVERT(NVARCHAR(25),
       DECRYPTBYASYMKEY(
          ASYMKEY_ID('MyCryptoKey'),
          EncryptedData,
          N'asd!i36oheQ#wr8iW#%qwei4!orqhq9w7as'
          )
       ) AS MyData
 FROM dbo.MySensitiveData
 WHERE Id = @Id;
GO 

With this in place, data can now be put into the table in an encrypted format:

EXEC dbo.spPutSensitiveData N'This is my sensitive data'
GO

Accessing the table directly, the data in its encrypted format can be seen but not read:

SELECT * FROM dbo.MySensitiveData
GO

Id EncryptedData
----------- ------------------
1 0xF82340DA34C32...

(1 row(s) affected)

Using the intended stored procedure to decrypt the data, it can be retreived in its unencrypted format:

EXEC dbo.spGetSensitiveData 1
GO

MyData
-------------------------
This is my sensitive data

(1 row(s) affected)

To clean up the demonstration envrionment, execute the following script:

USE master;
GO
DROP DATABASE CryptoFunctionDemo;
GO

Comments

• Anonymous
  June 28, 2011
  Nice demo, Bryan. Thanks for sharing.

• Anonymous
  June 29, 2011
  Thanks, Lee.  I'll be doing some of these demos live at the upcoming Dallas Tech Fest.  Hope you can attend.