Column Level Encryption

An EKM provider can be used for column-level encryption and decryption. This chapter shows how to use the SecurityServer EKM provider as a column-level encryption and decryption engine.

1. To demonstrate encryption and decryption a table demo will be created first. Consider here that for cryptographic keys to be successfully used, they have to be generated within the same database as the table entries which you wish to encrypt.

CREATE DATABASE utimaco GO

USE utimaco

CREATE TABLE [dbo].[demo] (

firstname varchar (255) NOT NULL, name varchar (255) NOT NULL, secret varbinary (8000) NOT NULL

) GO

CREATE SYMMETRIC KEY CLE_AES_256

FROM PROVIDER utimaco WITH ALGORITHM = AES_256,

PROVIDER_KEY_NAME = 'CLE_AES_256', CREATION_DISPOSITION=CREATE_NEW

GO

2. New rows can be inserted like in the next SQL statement. This statement uses a symmetric column encryption for the column secret.

INSERT INTO demo

VALUES ('John', 'Doe', ENCRYPTBYKEY(KEY_GUID('CLE_AES_256'), 'utimaco'))

3. In the same way an asymmetric encryption could be used.

INSERT INTO demo

VALUES ('John', 'Doe', ENCRYPTBYASYMKEY(ASYMKEY_ID('CLE_RSA_2048'),

'utimaco'))

EncryptByAsymKey returns NULL if the input exceeds a certain number of bytes, depending on the algorithm.

The limits are:

• a 2048 bit key can encrypt up to 245 bytes

Encryption and decryption with an asymmetric key are very costly compared with encryption and decryption with a symmetric key.

4. To show the decrypted value of an encrypted column the next statements can be used. This decrypts the symmetric encrypted column address and shows all stored rows of this table:

SELECT CONVERT(varchar, DECRYPTBYKEY(secret)) secret FROM demo

5. A decryption of asymmetric column can be achieved similar to the decryption of symmetric encrypted column:

SELECT

CONVERT(varchar, DECRYPTBYASYMKEY(ASYMKEY_ID('CLE_RSA_2048'), secret))

secret FROM demo