Using CAKM for Microsoft SQL Server EKM Provider
Once the CAKM for Microsoft SQL Server EKM Provider is installed and EKM is enabled, the database user accesses the provider using SQL queries.
Viewing the Provider Properties
To view the provider properties, execute the following query:
SELECT [provider_id], [guid], [provider_version], [sqlcrypt_version], [friendly_name], [authentication_type], [symmetric_key_support], [symmetric_key_persistance], [symmetric_key_export], [symmetric_key_import], [asymmetric_key_support], [asymmetric_key_persistance], [asymmetric_key_export], [asymmetric_key_import]
FROM [master].[sys].[dm_cryptographic_provider_properties]
Note
The provider_id
fetched here can be used to query database for the supported algorithms (in the next section).
Viewing the Supported Algorithms
To view the supported algorithms, execute the following query:
SELECT * FROM sys.dm_cryptographic_provider_algorithms(<your_provider_id>)
For example:
SELECT * FROM sys.dm_cryptographic_provider_algorithms(65593)
Creating a Key on CipherTrust Manager
The below command creates a key on the CipherTrust Manager and opens its corresponding key in the database. Once the key in the database is open, you can use it for cryptographic operations.
To create a new asymmetric key on the CipherTrust Manager, execute the following statement:
CREATE ASYMMETRIC KEY <key_name_in_sql_server>
FROM PROVIDER <provider_name>
WITH ALGORITHM = <algorithm_name>,
PROVIDER_KEY_NAME = '<key_name_in_cipherTrust_manager>',
CREATION_DISPOSITION=CREATE_NEW
For example:
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider <provider_name>
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW
You'll need to modify this statement to match your key name and preferred algorithm. To create a symmetric key, the statement must start with CREATE SYMMETRIC KEY
.
Note
By default, the new key created using CAKM for Microsoft SQL Server EKM provider is non-exportable. You can make it exportable from the CipherTrust Manager.
Opening an Existing Key in the Database
When you want to access a key that already exists on the CipherTrust Manager, you'll need to open that key in the database.
To open an asymmetric key in the database, execute the following statement:
CREATE ASYMMETRIC KEY <key_name_in_sql_server>
FROM Provider <provider_name>
WITH
PROVIDER_KEY_NAME = '<key_name_in_cipherTrust_manager>',
CREATION_DISPOSITION=OPEN_EXISTING
You'll need to modify this statement to match your key name. To open a symmetric key, the statement must start with CREATE SYMMETRIC KEY
.
Note
To use a versioned key, create a versioned asymmetric key on the CipherTrust Manager and then use the above statement to create a key in Microsoft SQL Server.
Viewing All Keys in the Database
To view all keys currently in the database, execute the following queries:
Select * from [master].[sys].[asymmetric_keys]
Select * from [master].[sys].[symmetric_keys]
Viewing All of a User’s Keys on the CipherTrust Manager
To view all of the user's keys on the CipherTrust Manager, execute the following query:
SELECT * FROM sys.dm_cryptographic_provider_keys(<your_provider_id>)
Dropping a Key from the Database
To drop an asymmetric key from the database, execute the following statement. This will remove the key only from the database. The key will remain on the CipherTrust Manager.
DROP ASYMMETRIC KEY <key_name_in_sql_server>
For example:
DROP ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
To drop a symmetric key, the statement must start with DROP SYMMETRIC KEY
.
Dropping a Key from the CipherTrust Manager
To drop a key from the database and remove it from the CipherTrust Manager, execute the following statement:
DROP ASYMMETRIC KEY <key_name_in_sql_server> REMOVE PROVIDER KEY
To drop a symmetric key, the statement must start with DROP SYMMETRIC KEY
.
Encrypting and Decrypting Table Column
In the examples in this section, the table used is created as below:
Create table dbo.test1 (Id int not null, FName varchar(max) not null, LName varchar(max) not null)
You can use either of the following two options while using symmetric keys for column encryption with the EncryptBy function:
On the CipherTrust Manager, create a symmetric key. This key can be used in all data encrypt/decrypt operations on the CipherTrust Manager unless 'Key Cache' is enabled.
On the CipherTrust Manager, create a symmetric key protected by an asymmetric key. This key can be used in all data encrypt/decrypt operations locally by the Microsoft SQL Server’s crypto library and only encryption/decryption of the symmetric key by the asymmetric key will be performed on the CipherTrust Manager resulting in improved performance.
Encrypting Data
To encrypt data, call the following procedures:
EncryptByAsymKey: for encryption by asymmetric keys.
EncryptByKey: for encryption by symmetric keys.
For example:
To encrypt with an asymmetric key, execute the following statement:
INSERT INTO dbo.test1 values(2,'fname2', EncryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), 'lname2'))
To encrypt with a symmetric key, execute the following statement:
INSERT INTO dbo.test1 values(6,'fname6',EncryptByKey(Key_GUID('SQL_EKM_AES_256_Key'), 'lname6'))
Encrypting Existing Data
If you want to encrypt existing data in a table, use the below statements.
To encrypt with the symmetric key:
UPDATE <table_name> SET <column_name> = ENCRYPTBYKEY(KEY_GUID('symmetric_key'), <column_name>)
For example:
UPDATE EMPLOYEE SET LNAME = ENCRYPTBYKEY(KEY_GUID('SQL_EKM_AES_256_Key'), LNAME)
To encrypt with asymmetric key:
UPDATE <table_name> SET <column_name> = ENCRYPTBYASYMKEY(ASYMKEY_ID('asymmetric_key'), <column_name>)
For example:
UPDATE EMPLOYEE SET LNAME = ENCRYPTBYASYMKEY(ASYMKEY_ID('SQL_EKM_RSA_2048_Key'), LNAME)
Decrypting Data
To decrypt data, call the following queries:
DecryptByAsymKey: for decryption by asymmetric keys.
DecryptByKey: for decryption by symmetric keys.
For example:
To decrypt with an asymmetric key, execute the following query:
SELECT Id,FName,
CONVERT(varchar(max),DecryptByAsymKey(AsymKey_Id('SQL_EKM_RSA_2048_Key'), LName)) LName FROM dbo.test1 where id =2
To decrypt with a symmetric key, execute the following query:
SELECT Id,FName,CONVERT(varchar(MAX),DecryptByKey(LName))
LName FROM dbo.test1 where id =6
Note
If you are using versioned keys for column encryption in the CipherTrust Manager, do not retire the version with which the data has been encrypted.
Dropping CAKM for Microsoft SQL Server EKM Provider
Drop the credential by executing the following statements:
ALTER LOGIN <login_name>
DROP CREDENTIAL <credential_name>
DROP LOGIN <login_name>
Drop the CAKM for Microsoft SQL Server EKM Provider by executing the following statement:
DROP CRYPTOGRAPHIC PROVIDER <provider_name>
Note
Once the session is created with the CipherTrust Manager, Microsoft SQL Server EKM does not close the connection. When CAKM for Microsoft SQL Server EKM provider is disabled or dropped then it closes the connection.