Column Level Encryption | Utimaco Integration Guides

An EKM provider can be used for column-level encryption and decryption. This chapter shows how to use the CryptoServer SQLEKM provider as a column-level encryption and decryption engine. To demonstrate encryption and decryption a table demo will be created first. Take into account here that in order 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.

SQL Statement

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

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

SQL Statement

INSERT INTO demo
VALUES ('John', 'Doe', ENCRYPTBYKEY(KEY_GUID('CLE_AES_256'), 'utimaco'))

In the same way an asymmetric encryption could be used.

SQL Statement

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 512 bit RSA key can encrypt up to 53 bytes
a 1024 bit key can encrypt up to 117 bytes
a 2048 bit key can encrypt up to 245 bytes

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

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:

SQL Statement

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

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

SQL Statement

SELECT
CONVERT(varchar, DECRYPTBYASYMKEY(ASYMKEY_ID('CLE_RSA_2048'), secret))
secret
FROM demo