Test the integration

Test the database encryption

The following queries test encryption on a database named TestDatabase created in section Test setup.

  1. Launch Microsoft SQL Server Management Studio and connect to the SQL Server.

  2. Run the following query to create a database encryption key (DEK) wrapped with the TDE key named TDE_Key created in integrate-ms-sql-csp-vault.adoc#create-tde-key-ms-sql.

    USE TestDatabase
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM  = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_Key;
GO

  3. Run the following query to enable encryption on TestDatabase:

    ALTER DATABASE TestDatabase
SET ENCRYPTION ON;
GO

  4. Check the state of keys and encryption.

    The thumbprints should be matching for each query. The encryption_state of 3 signifies "encrypted". See sys.dm_database_encryption_keys (Transact-SQL) for reference.

    For example:

    USE master
GO

SELECT DB_NAME(dek.database_id) AS database_name, dek.*
FROM sys.dm_database_encryption_keys dek;
Select * from sys.asymmetric_keys
GO
    state of keys and encryption

Rotate the key manually in the Entrust CSP Vault

The Entrust CSP Vault v10.5.1 supports reusing the same key name upon a key rotation. The asymmetric key object continues to resolve to the new cloud key after a key rotation. It is sufficient to rotate the cloud key in the Entrust CSP Vault. No action is needed in the Microsoft SQL Server.

  1. (Recommended) Back up your database before performaing key rotation.

  2. Sign in to your vault web GUI.

  3. In the toolbar select CLOUDKEYS, then select the Cloud Keys tab.

  4. In the Key Set drop-down menu, select your keyset created in integrate-ms-sql-csp-vault.adoc#create-tde-database-keyset.

  5. Select your key created in integrate-ms-sql-csp-vault.adoc#create-master-key.

  6. Scroll down and select Rotate Now.

    A new version of the key is created. You can see this new version in the Key Thumbprints tab, marked with a star in front of it.

    rotated key

Shut down encryption on the database and remove credentials

  1. Launch Microsoft SQL Server Management Studio and connect to the SQL Server.

  2. Run the following query to shut down encryption.

    ALTER DATABASE TestDatabase
SET ENCRYPTION OFF;
GO

USE TestDatabase
DROP DATABASE ENCRYPTION KEY
GO

USE master
GO

DROP ASYMMETRIC KEY TDE_Key
GO

ALTER LOGIN TDE_Login
DROP CREDENTIAL tde_ekm_cred
GO

DROP LOGIN TDE_Login
DROP CREDENTIAL tde_ekm_cred
GO

  3. Run the following query to remove the credential from the admin login:

    ALTER LOGIN [INTEROP\Administrator]
DROP CREDENTIAL sa_entrust_csp_vault;
GO

DROP credential sa_ekm_tde_cred
GO