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 SecurityServer EKM provider as a column-level encryption and decryption engine.

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

SQL

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, such as in the next SQL statement. This statement uses a symmetric column encryption for the column secret.

SQL

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

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

SQL

INSERT INTO demo
VALUES ('John', 'Doe', ENCRYPTBYASYMKEY(ASYMKEY_ID('CLE_RSA_2048'), 'utimaco'))
GO

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.

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

SQL

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

A decryption of an asymmetric column can be achieved similarly to the decryption of a symmetric encrypted column:

SQL

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