Using the SQLEKM provider

This section shows you how to enable the Web Services SQLEKM provider, and provides examples showing how to encrypt and decrypt data.

To run these examples, open SQL Server Management Studio and connect to a SQL Server instance, then open a query window to execute a query. In the example T-SQL statements, the names used for keys (such as dbAES256Key) and databases (such as TestDatabase) are example names only. The exception is master database, which is a real database.

You must have a SQL Server login and appropriate permissions to configure or access Microsoft SQL Server or the Web Services SQLEKM provider.

Enabling the Web Services SQLEKM provider

To enable the Web Services SQLEKM provider, execute the following query:

sp_configure 'show advanced options', 1; RECONFIGURE;
GO
sp_configure 'EKM provider enabled', 1; RECONFIGURE;
GO

Register the Web Services SQLEKM provider by executing the following query:
```
CREATE CRYPTOGRAPHIC PROVIDER <provider name>
FROM FILE = '<provider path>';
GO
```
Where:
- <provider name> is the name that is used to refer to the Web Services SQLEKM provider, e.g. SQLEKM.
- <provider path> is the fully qualified path to the nShield.WebServices.SQLEKM.dll file, e.g. C:\Program Files\nCipher\WebServices\SQLEKM\provider\nShield.WebServices.SQLEKM.dll.
To check that the Web Services SQLEKM provider is listed using SQL Server Management Studio.
1. Go to Security > Cryptographic Providers. You should see <provider name>.

Creating a credential

A SQL Server credential represents the protection domain, and associated passphrase, that is used to authorize access to specific keys protected by the Security World. The protection domain must already exist before attempting to create a credential.

Keys are protected by a single protection domain. By implication, this also applies to the SQL Server credential that represents the protection domain.

Please refer to nShield Web Services v3.3.1 for further information about creating and managing protection domains.

We recommend the use of a strong passphrase. Please consult your organization’s security policies.

Once created, the credential must in turn be associated with a login before it can be used. The owner of that login is then authorized to use that credential to create or use keys that are protected by the protection domain related to the credential.

It is by use of credentials and logins that access to keys for use in SQL Server can be controlled through the Web Services SQLEKM provider. For this reason, you should restrict who can use a credential. It is beyond the scope of this guide to deal with user access permissions. However, please be aware that if a valid credential and associated protection domain is available to an unauthorized user, who is then able to associate that credential with their login, this represents a security risk (the protection domain’s password is stored in the credential and cannot be used to identify the user). This may be less of an issue when using TDE encryption, for which users authorized to access the database do not need an associated credential in any case, but it may be an issue with cell-level encryption.

You may use multiple credentials if you wish to simultaneously use TDE and cell-level encryption. You are advised to set up your cell-level credentials and associated keys first, before setting up the TDE login/credential and switching TDE on, see Transparent Data Encryption - TDE and Cell-Level Encryption (CLE).

To create a credential and map it to a login (also see Creating credentials):

In SQL Server Management Studio, navigate to Security > Credentials.
Right-click Credentials, then select New Credential.
Set Credential name to <loginCredential>.
Set Identity to <protection domain id>, where <protection domain id> matches the id of the protection domain. You must match the character case.
Set Password to <protection domain passphrase>, where <protection domain passphrase> matches the passphrase of the protection domain. You must match the character case.
Ensure Use Encryption Provider is selected, then from the Provider drop-down list, choose <provider name>. Click OK.
Check that under Security > Credentials the name of the new credential appears. If necessary, right click and select Refresh.
In SQL Server Management Studio, navigate to Security > Logins.
Right-click to select the required login, then select Properties.
Ensure Map to Credential is selected, then select loginCredential from the drop down list. Click Add, then click OK.

Checking the configuration

To check that the Web Services SQLEKM provider was configured correctly:

Check that the Web Services SQLEKM provider was registered correctly by running the following query:
```
SELECT * FROM sys.cryptographic_providers;
```
A table is displayed with information about the registration of the Web Services SQLEKM provider. Check that:
- The build version matches the WS-SQLEKM version number (found in the version.json file on the ISO).
- The .dll path matches the path given when registering the provider (e.g. C:\Program Files\nCipher\WebServices\SQLEKM\provider\nShield.WebServices.SQLEKM.dll.)
- The is_enabled column is set to 1.
Check the Web Services SQLEKM provider properties by running the following query:
```
SELECT * FROM sys.dm_cryptographic_provider_properties;
```
A table is displayed with information about the properties of the Web Services SQLEKM provider. Check that:
- provider_version matches the WS-SQLEKM version number (found in the version.json file on the ISO). The number may be in a different format, but digits should be the same.
- friendly_name is nShield Web Services SQLEKM Provider
- authentication_type is set to BASIC
- symmetric_key_support is set to 1
- asymmetric_key_support is set to 1
To check that the supported cryptographic algorithms can be queried, run the following query:
```
DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE 'nShield Web Services SQLEKM Provider');
SELECT * FROM sys.dm_cryptographic_provider_algorithms(@ProviderId);
GO
```
A table is displayed with the supported cryptographic algorithms. For more information about the algorithms that should be displayed, see Supported cryptographic algorithms.

Encryption and keys

When you have configured the Web Services SQLEKM provider, and you have a suitable credential associated with your login, you can use the provider to:

Manage keys within the nShield HSM.
Encrypt or decrypt entire databases, or fields within tables, within SQL Server using TDE or cell-level encryption, or both at the same time.

Keys can be created in the Web Services SQLEKM provider and referenced by the appropriate database as required for use. When a reference of a key is no longer required for active use in the database, it should be deleted from the database while retaining the original copy of the key in the provider, which also acts as a secure backup. Storing original copies of keys in the Web Services SQLEKM provider is more secure than leaving key references and associated data together in the database. As long as the key is not deleted from the Web Services SQLEKM provider, its reference can be restored when next required for active use in the database.

Copying and deletion of keys does not apply to a TDE Database Encryption Key (TDEDEK), which is created as an integral part of a user database. On the other hand, this can apply to the wrapping key (TDEKEK) which is used to protect the TDEDEK. See Transparent Data Encryption - TDE.

It is recommended to regularly re-encrypt your data using fresh keys so that any persistent attempts to decipher or compromise your encrypted data are impeded.

Key naming, tracking and other identity issues

Keys held in the database are really references to actual keys held in the Web Services SQLEKM provider. For the purpose of key tracking, it is suggested that you use the same name for both the database and the provider version of a key. Use a suffix or prefix to distinguish between the database and provider versions.

In a database there can be only one key with a specific name at any one time.

If you have a significant number of keys, you may wish to implement a key naming convention that helps you track which keys encrypt which data, backed up with some form of secure documentation. Note if a key naming convention incorporates a database identifier, a Security World can hold keys for more than one database at the same time, and a key can be used in more than one database at a time.

To use the examples in this document you will first need to create TestDatabase and TestTable as shown in Creating a database and Creating a table. Otherwise, provide your own database and table to perform encryption operations and adapt the examples accordingly. Refer to Verifying by inspection that TDE has occurred on disk before adapting any examples. See also T-SQL shortcuts and tips.

Keys created under a login that is mapped to a particular credential will be protected by that credential.

Please refer to nShield Web Services v3.3.1 for further information about listing keys.

Supported cryptographic algorithms

The Web Services SQLEKM provider supports the following algorithms: AES_128, AES_192, AES_256, RSA_2048, RSA_3072 and RSA_4096.

Symmetric keys

Symmetric key GUIDs

When a new symmetric key is generated through the Web Services SQLEKM provider, it is associated in the database with a Global Unique Identifier or GUID. The database issues a different and random GUID for every new key, and uses the GUID to identify the correct symmetric key for encryption or decryption purposes. As long as a copy of this key with the same GUID remains available to the database, it can be used indefinitely.

If the key is lost to the database, then a cryptographically equivalent duplicate can be generated through the Web Services SQLEKM provider from the copy stored in the HSM. The duplicate key, although cryptographically identical to the lost key, will be issued with a new GUID by the database. Because the GUID is different from the original key it will not be identified with the original key, and will not be allowed to perform encryption or decryption of the data with which the lost key was associated.

To avoid this issue, you should always specify an IDENTITY_VALUE when generating a symmetric key. IDENTITY_VALUE is used to generate the key GUID in the database. The examples below create a symmetric key in the Web Services SQLEKM provider, and make available the same key for use in the database. The key does not have to share the same name between the Web Services SQLEKM provider and database.

Original key

To create a symmetric key with an identity value:

USE <database name>
CREATE SYMMETRIC KEY <key name in database> FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='<key name in provider>',
IDENTITY_VALUE='<unique GUID generator string>',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=<symmetric algorithm>;
GO

Where

<database name> is the name of the database for which you wish to provide encryption. See T-SQL shortcuts and tips for examples.
<provider name> is the name of the provider you are using.
<key name in database> is the name you wish to give the key in the database.
<key name in provider> is the name you wish to give the key in the provider. Please note that there is a length restriction on this name of 31 characters maximum if created using a T-SQL query.
<unique GUID generator string> is a unique string that will be used to generate the GUID.
<symmetric algorithm> is a valid symmetric key algorithm descriptor.

If the value of the <unique GUID generator string> is known to an attacker, this will help them reproduce the symmetric key. Therefore, it should always be kept secret and stored in a secure place. We recommend the <unique GUID generator string> shares qualities similar to a strong passphrase. Check your organization’s security policy.

Only one key that has been created using a particular IDENTITY_VALUE can exist at the same time in the same database.

Creating a duplicate key

This example shows how a duplicate of a lost symmetric key can be made through the Web Services SQLEKM provider and imported into the database.

To create a duplicate key:

USE <database name>
CREATE SYMMETRIC KEY <key name in database> FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='<key name in provider>',
IDENTITY_VALUE='<unique GUID generator string>',
CREATION_DISPOSITION = OPEN_EXISTING;
GO

Where <unique GUID generator string> is the same value as used to create the original key.

Creating and managing symmetric keys

This query generates a new symmetric key through the Web Services SQLEKM provider:

USE TestDatabase
CREATE SYMMETRIC KEY dbAES256Key
FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='ekmAES256Key',
IDENTITY_VALUE='Rg7n*9mnf29xl4',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=AES_256;
GO

Where <provider name> is the name of the provider you are using.

In this example, the key is named dbAES256Key in the database and ekmAES256Key in the Web Services SQLEKM provider.

Listing symmetric keys in a database

To list the symmetric keys in a database using SQL Server Management Studio: . Go to Databases > TestDatabase > Security > Symmetric Keys (right-click to select Refresh).

Alternatively, you may check keys by following the methods shown in the section Checking keys.

Removing symmetric keys from the database only

To remove the symmetric key dbAES256Key from the database only (TestDatabase):

USE TestDatabase
DROP SYMMETRIC KEY dbAES256Key;
GO

After the above query completes, the key dbAES256Key is deleted from the database, but the corresponding key ekmAES256Key remains protected by the Web Services SQLEKM provider.

Re-importing symmetric keys

To re-import the symmetric key (dbAES256Key) that was removed from the database, where a corresponding copy (ekmAES256Key) exists in the Web Services SQLEKM provider:

USE TestDatabase
CREATE SYMMETRIC KEY dbAES256Key FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='ekmAES256Key',
IDENTITY_VALUE='Rg7n*9mnf29xl4',
CREATION_DISPOSITION = OPEN_EXISTING;
GO

This example uses the same IDENTITY_VALUE as in the original key generation. This regenerates the same GUID. Having the same GUID means that the key is logically identical to the key it replaces.

Removing symmetric keys from the database and provider

To remove a symmetric key (dbAES256Key) from both the database (TestDatabase) and the Web Services SQLEKM provider, execute the following query:

USE TestDatabase
DROP SYMMETRIC KEY dbAES256Key REMOVE PROVIDER KEY;
GO

Using this method means you do not have to name the corresponding key in the Web Services SQLEKM provider to remove it from there.

Refer to your security policies before considering deleting a key from the Web Services SQLEKM provider. You cannot import a key into the database once you have deleted that key from the provider.

Creating and managing asymmetric keys

Creating an asymmetric key

To generate a new asymmetric key through the Web Services SQLEKM provider:

USE TestDatabase
CREATE ASYMMETRIC KEY dbRSA2048Key FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='ekmRSA2048Key',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=<asymmetric algorithm>;
GO

Where

<provider name> is the name that is used to refer to the Web Services SQLEKM provider.
<asymmetric algorithm> is a valid asymmetric key algorithm descriptor.

Please note that there is a length restriction on this name of 31 characters maximum if created using a T-SQL query.

This example names the key dbRSA2048Key in the database, and ekmRSA2048Key in the Web Services SQLEKM provider.

IDENTITY_VALUE is not a supported argument for asymmetric key generation.

Listing asymmetric keys in a database

To list the asymmetric keys in a database using SQL Server Management Studio: . Go to Databases > TestDatabase > Security > Asymmetric Keys (right-click to select Refresh).

Alternatively, you may check keys by following the methods shown in the section Checking keys.

Removing an asymmetric key from the database only

To remove the asymmetric key dbRSA2048Key from the database only (TestDatabase):

USE TestDatabase
DROP ASYMMETRIC KEY dbRSA2048Key;
GO

After the above query completes, the key dbRSA2048Key is deleted from the database, but the corresponding key ekmRSA2048Key remains protected by the Web Services SQLEKM provider.

Re-importing an asymmetric key

To re-import a deleted asymmetric key (dbRSA2048Key) back into the database (TestDatabase), where a corresponding copy (ekmRSA2048Key) exists in the Web Services SQLEKM provider:

USE TestDatabase
CREATE ASYMMETRIC KEY dbRSA2048Key
FROM PROVIDER <provider name> WITH PROVIDER_KEY_NAME='ekmRSA2048Key',
CREATION_DISPOSITION = OPEN_EXISTING;
GO

Removing an asymmetric key from the database and provider

To remove the asymmetric key (dbAES256Key) from both the database (TestDatabase) and the Web Services SQLEKM provider, execute the following query:

USE TestDatabase
DROP ASYMMETRIC KEY dbRSA2048Key REMOVE PROVIDER KEY;
GO

Using this method means you do not have to name the corresponding key in the Web Services SQLEKM provider to remove it from there.

Refer to your security policies before considering deleting a key from the Web Services SQLEKM provider. You cannot import a key into the database once you have deleted that key from the provider.

Creating a symmetric wrapped key from an asymmetric wrapping key

To create a symmetric wrapped key (dbSymWrappedKey1) from an asymmetric wrapping key (dbAsymWrappingKey1), execute the following query:

USE TestDatabase
CREATE ASYMMETRIC KEY dbAsymWrappingKey1 FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='ekmAsymWrappingKey1',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048;
CREATE SYMMETRIC KEY dbSymWrappedKey1
WITH ALGORITHM = AES_128,
IDENTITY_VALUE ='yr7s365$dfFJ901'
ENCRYPTION BY ASYMMETRIC KEY dbAsymWrappingKey1;

Where <provider name> is the name that is used to refer to the Web Services SQLEKM provider.

If you wish to delete the wrapped and wrapping keys, you will have to delete the wrapped key first.

Importing keys

By 'importing keys' we should distinguish between:

Importing a key into the database that was created in the Web Services SQLEKM provider.
Importing a (foreign) key that was created outside the Web Services SQLEKM provider.

Keys created in the Web Services SQLEKM provider can be imported into a database provided they are in simple format.

As regards keys created outside the Web Services SQLEKM provider, it is not recommended to import such keys into the Security World unless they are from a trustworthy source. Importing of externally created keys into the Security World may require format conversion.

Please contact Entrust nShield Technical Support if you wish to pursue key import (or export) operations further.

To import an externally created symmetric key with an identity value:

USE <database name>
CREATE SYMMETRIC KEY <key name in database> FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='<key name in provider>',
IDENTITY_VALUE='<unique GUID generator string>',
CREATION_DISPOSITION = OPEN_EXISTING;

Where:

<database name> is the name of the database for which you wish to provide encryption. See T-SQL shortcuts and tips for examples.
<provider name> is the name of the provider you are using.
<key name in database> is the name you wish to give the key in the database.
<key name in provider> is the name of the externally created key in the provider. This must be no more than 32 characters maximum.
<unique GUID generator string> is a unique string that will be used to generate the GUID.

If the value of the <unique GUID generator string> is known to an attacker, this will help them reproduce the symmetric key. Therefore, it should always be kept secret and stored in a secure place. We recommend that the <unique GUID generator string> shares qualities similar to a strong passphrase. Check your organization’s security policy.

Only one key that has been created using a particular IDENTITY_VALUE can exist at the same time in the same database.

To import an externally created asymmetric key

USE <database name>
CREATE ASYMMETRIC KEY <key name in database> FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='<key name in provider>',
CREATION_DISPOSITION = OPEN_EXISTING;

Parameters are the same as for the symmetric key. Note, for an externally created asymmetric key, name length restriction of 32 characters maximum applies for <key name in provider>

Transparent Data Encryption - TDE

These examples assume that both the TestDatabase and TestTable as described in T-SQL shortcuts and tips have been created, and are not currently encrypted.

When TDE encryption has been correctly set up and switched on, the database it is protecting will appear as normal to any user who has been granted suitable permissions to use the database. The user does not require a Web Services SQLEKM provider credential to access or modify TDE protected data.

Note that the person setting up or managing the TDE encryption keys must use the same protection domain for their login credential as used for the tdeCredential below.

The TDE Database Encryption Key (TDEDEK) is a symmetric key that is used to perform the actual encryption of the database. It is created by SQL Server and cannot be exported from the database meaning that it cannot be created or directly protected by the Web Services SQLEKM provider. In order to protect the TDEDEK within the database it may in turn be encrypted by a wrapping key. The wrapping key is called the TDE Key Encryption Key (TDEKEK). In this case, the Web Services SQLEKM provider can create and protect the TDEKEK.

Before running the following examples, you should create a backup copy of the unencrypted database. See Backing up a database with SQL Server Management studio. Alternatively, you may prefer to adapt the T-SQL query shown in Making a database backup. Save the backup as <Drive>:\<Backup_directory_path>\TestDatabase_TDE_Unencrypted.bak.

If you are using a shared disk cluster, TDE should be configured on the active node.

Creating a TDEKEK

The TDEKEK must be protected under the same protection domain as that used to create the tdeCredential below.

To create a TDEKEK, or wrapping key, for database encryption:

USE master
CREATE ASYMMETRIC KEY dbAsymWrappingKey FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='ekmAsymWrappingKey', CREATION_DISPOSITION =
CREATE_NEW, ALGORITHM = RSA_2048;
GO

Where <provider name> is the name that is used to refer to the Web Services SQLEKM provider.

The TDEKEK is the only key you must create in the master database.

To check the TDEKEK, in SQL Server Management Studio navigate to Databases > System Databases > Master > Security > Asymmetric Keys. If necessary, right-click and select Refresh.

Setting up the TDE login and credential

In SQL Server Management Studio, navigate to Security > Credentials.
Right-click Credentials, then select New Credential.
Set Credential name to tdeCredential (for example).
Set Identity to <protection domain id>, where <protection domain id> matches the id of the protection domain. This must be the same key protector as that used to protect the ekmAsymWrappingKey created above.
Set Password to <passphrase>, where <passphrase> matches the passphrase of the protection domain.
Set Use Encryption Provider to <provider name>, where <provider name> is the name of the provider you are using. Click OK.
In SQL Server Management Studio, navigate to Security > Logins.
Right-click Logins, then select New Login.
Set Login name to tdeLogin (for example).
Ensure Mapped to asymmetric key is selected, then select dbAsymWrappingKey (the TDEKEK created previously) from the drop down list.
Ensure Map to Credential is selected, then select tdeCredential from the drop down list. Click Add, then click OK.
In SQL Server Management Studio, check that the tdeCredential exists by navigating to Security > Credentials. If necessary, right-click and select Refresh. You should see the credential name listed.
In SQL Server Management Studio, check that the tdeLogin exists by navigating to Security > Logins. If necessary, right-click and select Refresh. You should see the login name listed.

Creating the TDEDEK and switching on encryption

Only one TDEDEK per database can be used at a time.

To create the TDEDEK using the dbAsymWrappingKey (TDEKEK) created above for database encryption, and enable TDE on the database (TestDatabase):

In SQL Server Management Studio, navigate to Databases > TestDatabase.
Right-click TestDatabase, then select Tasks > Manage Database Encryption…
Set Encryption Algorithm to the desired algorithm.
Ensure that Use server asymmetric key is selected, then select dbAsymWrappingKey from the drop down list.
Ensure Set Database Encryption On is selected, then click OK.

After successfully setting up the TDE encryption, the person performing the set up no longer needs to use the same protection domain for their login credential as used for the tdeCredential.

Verifying by inspection that TDE has occurred on disk

Note that the inspection method will only work for data that can be backed up in the database (on disk) as human-readable character strings.

To check the encryption state of the database, refer to the section How to check the TDE encryption/decryption state of a database. If the TDE has been successful, then an 'Encrypted' state should be indicated.

Querying the TestTable or database contents will not indicate whether the table was encrypted on disk, because it will be automatically decrypted when loaded into memory. TDE encryption on disk can be verified by inspecting backup copies of the TestDatabase from before and after the TDE encryption.

After TDE encryption has been set up and checked to be functioning, make a backup copy of the encrypted TestDatabase: see Backing up a database with SQL Server Management studio for instructions.

You should now have the following unencrypted and encrypted backup copies of the TestDatabase:

<Drive>:\<Backup_directory_path>\TestDatabase_TDE_Unencrypted.bak
<Drive>:\<Backup_directory_path>\TestDatabase_TDE_Encrypted.bak

These backup files can be inspected using a text editor, provided you have appropriate access permissions.

Open TestDatabase_TDE_Unencrypted.bak in a text editor and search for a known value. It should be possible to find the plaintext FirstName or else LastName of anyone mentioned in the original and unencrypted TestTable.
Open TestDatabase_TDE_Encrypted.bak in a text editor and search for the same value. It should not be possible to find any plaintext names or other values in the encrypted file.

The backup files circumvent the automatic TDE decryption of the database, allowing direct inspection of the contents as stored on disk. Although this inspection has been carried out on backup files, these should contain information similar enough to the actual database disk contents to demonstrate whether the TDE encryption is working on disk or not.

To replace the TDEKEK

Follow the procedure above (see Creating a TDEKEK) to create a new asymmetric TDEKEK called dbAnotherAsymWrappingKey.
Create a new credential called anotherTdeCredential.
Create a new TDE login called anotherTdeLogin. Map it to dbAnotherAsymWrappingKey and the new anotherTdeCredential.
In SQL Server Management Studio, navigate to Databases > TestDatabase.
Right-click TestDatabase, then select Tasks > Manage Database Encryption…
Select Re-Encrypt Database Encryption Key and Use server asymmetric. Select dbAnotherAsymWrappingKey from the drop down list.
Ensure Regenerate Database Encryption Key is not selected.
Ensure Set Database Encryption On is selected, then click OK.

To replace the TDEDEK

In SQL Server Management Studio, navigate to Databases > TestDatabase.
Right-click TestDatabase, then select Tasks > Manage Database Encryption…
Ensure Re-Encrypt Database Encryption Key is not selected.
Ensure Regenerate Database Encryption Key is selected, then select AES 256 from the drop down list.
Ensure Set Database Encryption On is selected, then click OK.

Switching off and removing TDE

See Uninstalling and upgrading.

How to check the TDE encryption/decryption state of a database

The following encryption_state information applies to TDE encryption only.

You can use the following T-SQL queries to find the current encryption state of a database. This can be particularly useful where large amounts of data have to be processed and you wish to check progress before attempting any further operations on the database.

First, find the database ID from the database name by using the following query:

SELECT DB_ID('<Database name>') AS [Database ID];
GO

Where <Database name> is the name of the database you are interested in.

List database encryption states by using the following query:

SELECT * FROM sys.dm_database_encryption_keys

The above query provides a table output that includes columns titled database_id and encryption_state.

Find the database ID you are interested in and look at the corresponding value for the encryption state.

Alternatively, you can use the composite query:

SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys

Where database_id is the ID number of the database you are interested in.

Values of encryption_state are as follows:

Value of `encryption_state`	Meaning of value
0	Encryption disabled (or no encryption key)
1	Unencrypted or Decrypted
2	Encryption in progress
3	Encrypted
4	Key change in progress
5	Decryption in progress
6	Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Cell-Level Encryption (CLE)

In CLE separate data fields in the same table can be encrypted under different keys. These keys can be protected by different credentials. Unlike TDE protection, the user will need to obtain keys from the Web Services SQLEKM provider, and must have the correct credential to authorize and load the key(s) for the specific encrypted data they wish to access. Non-encrypted data is not affected by this and is visible to any authorized user.

Cell-level encryption will only work on data stored in the database as VARBINARY type. You must provide any necessary type conversions so that data is in VARBINARY form before encryption is performed. Decryption will return the data to its original VARBINARY structure. It may then be necessary to reconvert to its original type for viewing in human-readable form.

Database backup files that use the VARBINARY type are not human-readable. Therefore, the previous inspection method, as used for TDE to directly check if data has been encrypted on disk, cannot be used for cell-level encryption.

If you have not already created the following keys and made them available in your current database copy, then create them now.

Symmetric key

USE TestDatabase
CREATE SYMMETRIC KEY dbAES256Key
FROM PROVIDER SQLEKM
WITH PROVIDER_KEY_NAME='ekmAES256Key',
IDENTITY_VALUE='Rg7n*9mnf29xl4',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=AES_256;
GO

Asymmetric key

USE TestDatabase
CREATE ASYMMETRIC KEY dbRSA2048Key FROM PROVIDER SQLEKM
WITH PROVIDER_KEY_NAME='ekmRSA2048Key',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048;
GO

Encrypting and decrypting a single cell of data

Before you start, make sure you have a fresh version of the TestTable that is unencrypted.

In the example below, the encrypted and decrypted data is stored separately. Normally, the original data would be overwritten with the processed data.

View TestTable by running the following query:
```
SELECT TOP 10 [FirstName]
,[LastName]
,CAST(NationalIdNumber AS decimal(16,0)) AS [NationalIDNumber]
,(NationalIdNumber) AS VarBinNationalIdNumber
,[EncryptedNationalIdNumber]
,[DecryptedNationalIdNumber]
FROM [TestDatabase].[dbo].[TestTable]
```
You will see the column NationalIdNumber in its original decimal form, and the column VarBinNationalIdNumber which shows the same number in its VARBINARY form (as stored in the database), and in which it will be encrypted.

The columns EncryptedNationalIdNumber and DecryptedNationalIdNumber should contain NULL.

To encrypt a single cell in the TestTable, run the following query:

USE TestDatabase
UPDATE TestTable
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('dbAES256Key'),
NationalIDNumber)
WHERE FirstName = 'Kate' AND LastName = 'Austin';
GO

This query encrypts the NationalIdNumber for Kate Austin using the symmetric key dbAES256Key, and stores the result in the column EncryptedNationalIDNumber.

If encrypting using an asymmetric key, run the following query:

USE TestDatabase
UPDATE TestTable
SET EncryptedNationalIDNumber =
ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), NationalIDNumber)
WHERE FirstName = 'Kate' AND LastName = 'Austin';
GO

Run the previous View Table query. The EncryptedNationalIdNumber will now contain the encrypted value against the name Kate Austin.

Run the following query to decrypt the information:

USE TestDatabase
UPDATE TestTable
SET DecryptedNationalIDNumber = DecryptByKey(EncryptedNationalIDNumber)
WHERE FirstName = 'Kate' AND LastName = 'Austin';
GO

If decrypting using an asymmetric key, run the following query:

USE TestDatabase
UPDATE TestTable
SET DecryptedNationalIDNumber =
DECRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), EncryptedNationalIDNumber)
WHERE FirstName = 'Kate' AND LastName = 'Austin';
GO

Run the previous View Table query. The DecryptedNationalIdNumber will now contain the decrypted value against the name Kate Austin.

Ensure that this value matches the corresponding value in the VarBinNationalIdNumber column. If the values match, then the decryption worked successfully.

To view the decrypted value in its original decimal form, run the following query:

SELECT TOP 10 [FirstName]
,[LastName]

,CAST(NationalIdNumber AS decimal(16,0)) AS [NationalIDNumber]
,(NationalIdNumber) AS VarBinNationalIdNumber
,[EncryptedNationalIdNumber]
,CAST(DecryptedNationalIdNumber AS decimal(16,0)) AS
[DecryptedNationalIdNumber]
FROM [TestDatabase].[dbo].[TestTable]

Reset the EncryptedNationalIdNumber and DecryptedNationalIdNumber columns by running the following query:

USE TestDatabase
UPDATE TestTable
SET EncryptedNationalIDNumber = NULL, DecryptedNationalIDNumber = NULL;
GO

Encrypting and decrypting columns of data

Before you start, make sure you have a fresh version of the TestTable that is unencrypted.

In the example below, the encrypted and decrypted data is stored separately. Normally, the original data would be overwritten with the processed data.

Perform the same steps as shown in the section Encrypting and decrypting a single cell of data, but in this case where encryption or decryption occurs, replace with the following queries.

Encrypt an existing column of data using the symmetric key:

USE TestDatabase
UPDATE TestTable
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('dbAES256Key'),
NationalIDNumber);
GO

Decrypt an existing column of data using the symmetric key:

USE TestDatabase
UPDATE TestTable
SET DecryptedNationalIDNumber = DecryptByKey(EncryptedNationalIDNumber);
GO

Encrypt an existing column of data using the asymmetric key:

USE TestDatabase
UPDATE TestTable
SET EncryptedNationalIDNumber = ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), NationalIDNumber);
GO

Decrypt an existing column of data using the asymmetric key:

USE TestDatabase
UPDATE TestTable
SET DecryptedNationalIDNumber = DECRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), EncryptedNationalIDNumber);
GO

Creating a new table and inserting cells of encrypted data

The following assumes you have available TestDatabase and the keys dbAES256Key, dbRSA2048Key as created previously.

Create a table with an encrypted field:

To create a new database table Customers, where individual cells of data held in the third column (CardNumber) will be encrypted, execute the following query:

USE TestDatabase
GO
CREATE TABLE Customers (FirstName varchar(MAX), SecondName varchar(MAX), CardNumber varbinary(MAX));

Insert encrypted data with the symmetric key:

The following query allows the user to enter the sensitive data (CardNumber) via the keyboard and then immediately encrypt using a symmetric key, sending the CardNumber directly into memory (and database) in an encrypted state.

USE TestDatabase
INSERT INTO Customers (FirstName, SecondName, CardNumber)
VALUES ('Joe', 'Bloggs', ENCRYPTBYKEY(KEY_GUID('dbAES256Key'),
CAST('<16 digit card number>' AS VARBINARY)));
INSERT INTO Customers (FirstName, SecondName, CardNumber)
VALUES ('Iain', 'Hood', ENCRYPTBYKEY(KEY_GUID('dbAES256Key'),
CAST('<16 digit card number>' AS VARBINARY)));
INSERT INTO Customers (FirstName, SecondName, CardNumber)
VALUES ('Joe', 'Smith', ENCRYPTBYKEY(KEY_GUID('dbAES256Key'),
CAST('<16 digit card number>' AS VARBINARY)));
GO

Where <16 digit card number> is a 16-digit payment card number to be encrypted.

View data encrypted with the symmetric key in plain text:

The following query allows the user to view, in plain text on screen, the sensitive data (CardNumber) for customers named 'Joe'. The data remains encrypted in memory and (database).

USE TestDatabase
SELECT [FirstName], [SecondName],
CAST(DecryptByKey(CardNumber) AS varchar) AS 'Decrypted card number'
FROM Customers WHERE [FirstName] LIKE ('%Joe%');
GO

If an asymmetric key (dbRSA2048Key) is used, similar actions can be achieved using the following queries.

Insert encrypted data with the asymmetric key:

USE TestDatabase
INSERT INTO Customers (FirstName, SecondName, CardNumber)
VALUES ('Joe', 'Connor', ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),
CAST('<16 digit card number>' AS VARBINARY)));
INSERT INTO Customers (FirstName, SecondName, CardNumber)
VALUES ('Richard', 'Taylor', ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),
CAST('<16 digit card number>' AS VARBINARY)));
INSERT INTO Customers (FirstName, SecondName, CardNumber)
VALUES ('Joe', 'Croft', ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),
CAST('<16 digit card number>' AS VARBINARY)));
GO

Where <16 digit card number> is a 16-digit payment card number to be encrypted.

View data encrypted with the asymmetric key in plain text:

USE TestDatabase
SELECT [FirstName], [SecondName],
CAST(DECRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),CardNumber) AS varchar) AS 'Decrypted card number'
FROM Customers WHERE [FirstName] LIKE ('%Joe%');
GO

It is possible to encrypt separate table cells using different keys. When decrypting with a particular key, it should not be possible to see data that was encrypted using another key.

Viewing tables

Using SQL Server Management Studio

To check that data in a table was either encrypted or decrypted successfully, complete the following steps with SQL Server Management Studio: . Go to Databases > TestDatabase > Tables. . Right-click the table name and select Select Top 1000 Rows to view the encrypted or decrypted data.

Using SQL Query

To check that data in a table was either encrypted or decrypted successfully, execute the following SQL query:

Use TestDatabase
SELECT * FROM <table_name>

Checking keys

The following queries show how you can check the attributes of keys in your database and the Web Services SQLEKM provider.

To view the symmetric keys in a database:

Use TestDatabase
SELECT * FROM sys.symmetric_keys

To view the asymmetric keys in a database:

Use TestDatabase
SELECT * FROM sys.asymmetric_keys

To list all the Web Services SQLEKM provider keys that are associated with the current credential:

DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id
FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '<provider friendly_name>');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId);
GO

Where <provider friendly_name> can be found as shown in the section Checking the configuration for the provider you are using.

To correlate symmetric keys between the database and the Web Services SQLEKM provider:

DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '<provider friendly_name>');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.symmetric_keys
ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
WHERE sys.dm_cryptographic_provider_keys.key_type = 'SYMMETRIC KEY'
GO

Where <provider friendly_name> can be found as shown in the section Checking the configuration for the cryptographic provider you are using.

To correlate asymmetric keys between the database and the Web Services SQLEKM provider:

DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '<provider friendly_name>');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
WHERE sys.dm_cryptographic_provider_keys.key_type = 'ASYMMETRIC KEY'
GO

Where <provider friendly_name> can be found as shown in the section Checking the configuration for the cryptographic provider you are using.

To correlate all keys (symmetric and asymmetric) between the database and cryptographic provider:

DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '<provider friendly_name>');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.symmetric_keys
ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
FULL OUTER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
GO

Where <provider friendly_name> can be found as shown in the section Checking the configuration for the Web Services SQLEKM provider you are using.

The key_thumbprint returned by the T-SQL queries, maps to the kid when listing keys with the Web Services Option Pack REST API. Please refer to nShield Web Services v3.3.1 for further information about listing keys.

Restarting SQL Server

Changes to the Security World, or the Web Services Option Pack Server, may result in the need to restart SQL Server.