Using the SQLEKM provider

This section shows you how to enable the 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 cryptographic keys (such as dbAES256Key) and databases (such as TestDatabase) are example names only. The exception to this rule is the master database, which is a real database.

If you are using a failover cluster, run the examples through the virtual server. Otherwise, use the active server in the cluster. Note that any directory/file paths will be relative to the active server.
You must have a SQL Server login and appropriate permissions to configure or access the SQL Server or SQLEKM provider.

If a database is started before the SQLEKM provider is ready to accept a connection, the database might enter a pending recovery state. By default, 20 retries of provider initialization are attempted, with each attempt occurring at 5-second intervals. If necessary, the number of retry attempts can be configured by adding the following registry entry (type REG_DWORD):

HKLM\SOFTWARE\nCipher\SQLEKM\NCoreMaxInitRetries

Enabling the SQLEKM provider

  1. To enable the SQLEKM provider, execute the following query:

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

  2. Register the SQLEKM provider by executing the following query:

    CREATE CRYPTOGRAPHIC PROVIDER <Name of provider>
FROM FILE = '<Path to provider>';
GO

    Where:

    • <Name of provider> is the name that is used to refer to the SQLEKM provider, e.g. SQLEKM.

    • <Path to provider> is the fully qualified path to the ncsqlekm.dll file, e.g. C:\Program Files\nCipher\nfast\bin\ncsqlekm.dll.

  3. To check that the SQLEKM provider is listed:

    1. Open SQL Server Management Studio on the Management Studio.

    2. Go to Security > Cryptographic Providers. You should see <Name of provider>.

Creating a credential

A SQL Server credential represents the OCS, or softcard, and associated passphrase that is used to authorize access to specific keys protected by the Security World. The OCS or softcard must already exist before attempting to create a credential. When using an OCS cardset with the SQLEKM provider, use a 1/N quorum.

Encryption keys can be protected by only one OCS cardset, or else softcard, at any one time. By implication, this also applies to the SQL Server credential that represents that OCS cardset or softcard.
You can transfer key(s) from one OCS cardset to another OCS cardset, or from one softcard to another softcard. You must use the rocs utility to perform the key transfer. Please see the User Guide for your HSM for more details. However, you cannot transfer keys between an OCS cardset and softcard.

If you are using a failover cluster, you will need to create the OCS or softcard directly through the active server. Please refer to the User Guide for your HSM for further information about creating an OCS or a softcard.

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 particular login before it can be used. The owner of that login is then authorized to use that credential to create or use encryption keys that are protected by the OCS or softcard related to the credential.

A login can be associated with only one credential at a time, but a credential can be associated with several logins at a time.

It is by use of credentials and logins that access to encryption keys for use in SQL Server can be controlled through the 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 OCS card or softcard is available to an unauthorized user, who is then able to associate that credential with their login, this represents a security risk (the token’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 encryption.

Countermeasures to reduce these risks may be made through SQL Server or Windows access permissions in accordance with your security policies. Options that may be considered are to restrict use of the OCS or softcards by identifying the relevant files amongst the Security World data, and setting their access permissions to authorized users only. You can identify OCS cards and softcards using the nfkminfo utility as follows:

  • OCS cards: use nfkminfo –c

  • Softcards: use nfkminfo –s.

You will see the OCS card or softcard names and their associated hash number. Look in the Security World data and set appropriate permissions for all files that share the same hash number as the OCS or softcard you are protecting, see Security World data and back-up and restore for more information about file hash numbers.

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 encryption 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:

  1. In SQL Server Management Studio, navigate to Security > Credentials.

  2. Right-click Credentials, then select New Credential.

  3. Set Credential name to loginCredential.

  4. Set Identity to <OCSname>, where <OCSname> matches the name of the OCS or softcard. You must match the character case.

  5. Set Password to <passphrase>, where <passphrase> matches the passphrase on the card set or softcard. You must match the character case.

  6. Ensure Use Encryption Provider is selected, then from the <Name of provider>, drop-down list, choose <Name of provider>. Click OK.

  7. Check that under Security > Credentials the name of the new credential appears. If necessary, right click and select Refresh.

  8. In SQL Server Management Studio, navigate to Security > Logins.

  9. Right-click to select the required login, then select Properties.

  10. 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 SQLEKM provider was configured correctly:

  1. Check that the 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 SQLEKM provider. Check that:

    • The build version matches the sqlekm version number (found in the SQLEKM installation versions file).

    • The .dll path matches the path given when registering the SQLEKM provider (e.g. C:\Program Files\nCipher\nfast\bin\ncsqlekm.dll.)

    • The is_enabled column is set to 1.

  2. Check the 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 SQLEKM provider. Check that:

    • provider_version matches the sqlekm version number (found in the SQLEKM installation versions file). The number may be in a different format, but digits should be the same.

    • friendly_name is nCipher SQLEKM Provider

    • authentication_type is set to BASIC

    • symmetric_key_support is set to 1

    • asymmetric_key_support is set to 1

  3. 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 'nCipher 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 encryption keys

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

  • Manage cryptographic keys within the nShield HSM.

  • Encrypt or decrypt entire databases or fields within tables within your SQL Server service using TDE or Cell encryption, or both at the same time.

Encryption keys can be created in the SQLEKM provider and referenced by the appropriate database as required for use. When a reference of an encryption 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 SQLEKM provider, which also acts as a secure backup. Storing original copies of encryption keys in the SQLEKM provider is more secure than leaving encryption key references and associated data together in the database. So long as you retain a copy of the original key in the 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.

Copies of encryption keys that are retained in the SQLEKM provider (or Security World) are in turn protected by inbuilt encryption facilities, and cannot be read or decrypted without suitable authorization mechanisms. Even if a Security World or HSM is stolen, it will be useless to anyone who does not have access to the correct authorization mechanisms.

You must be very careful if you consider deleting an original encryption key from the SQLEKM provider; once deleted from there, it is lost for good, unless you have a prior backup of the Security World. Similarly, you must be very careful before dropping any of the authorization mechanisms such as OCS cards, softcards, ACS cards, and their associated passwords.Loss of these could also mean you lose access to your encryption keys.

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

Encryption keys that have been made accessible to a database through the SQLEKM provider are accessible through references provided to the database. Copies of the real keys do not exist in the database.

Key naming, tracking and other identity issues

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

In a database there can be only one key with a specific name at any one time. However, note that key names can be duplicated for different keys in the SQLEKM provider.

Even though possible, we strongly discourage permitting duplicate key names in the SQLEKM provider, since this simply leads to confusion and potential operational errors.

If you have very many 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.

If you are using more than one Security World you should ensure you can physically identify the ACS and OCS cards that belong to each Security World.

Once a Security World is loaded onto a HSM, its OCS cards can be inserted into the card reader and individually identified with cardset name and creation sequence number using Entrust supplied utilities.

Additionally, you can name individual OCS cards when the OCS cardset is created. The keys a card is protecting can be identified using the rocs utility.

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.

Encryption keys created under a login that is mapped to a particular credential will be protected by that credential. If you wish to transfer keys to another OCS or softcard please see the User Guide for your HSM.
You can check which keys are protected under which credential by using the rocs utility; see the User Guide for your HSM for details. If you are using rocs in a failover cluster environment, you must use it on the active server.
If you are protecting encryption keys with an OCS credential, an operator card must be inserted into the HSM card reader of every HSM that is part of the configuration to create or authorize use of the encryption keys.

Supported cryptographic algorithms

The algorithms that you can use for encryption depends on the type of Security World being used.

The following table lists cryptographic algorithms that are available when using symmetric keys.

Algorithm FIPS 140-2 Level 2
Security World		 FIPS 140-2 Level 3
v1/v2 Security World		 FIPS 140-2 Level 3
v3 Security World		 CC-CMTS
Security World

DES

Yes

No

No

No

Triple_DES

Yes

Yes

No

No

Triple_DES_3KEY

Yes

Yes

No

No

AES_128

Yes

Yes

Yes

Yes

AES_192

Yes

Yes

Yes

Yes

AES_256

Yes

Yes

Yes

Yes

The following table lists cryptographic algorithms that are available when using asymmetric cryptographic keys.

Algorithm FIPS 140-2 Level 2 Security World FIPS 140-2 Level 3 v1/v2 Security World FIPS 140-2 Level 3 v3 Security World CC-CMTS Security World

RSA_512

Yes

Yes

No

No

RSA_1024

Yes

Yes

No

No

RSA_2048

Yes

Yes

Yes

Yes

RSA_3072

Yes

Yes

Yes

Yes

RSA_4096

Yes

Yes

Yes

Yes
Although DES and RSA_512 keys can be used, this is mainly for compatibility with legacy systems. Otherwise they are not recommended for use with nShield products. For more information, contact Entrust nShield Technical Support.

Symmetric keys

Symmetric key GUIDs

When a new symmetric key is generated through the 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 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 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 SQLEKM provider and database.

Original key

To create a symmetric key with an identity value:

USE <Your_database_name>
CREATE SYMMETRIC KEY <Name_of_key_in_database> FROM PROVIDER <Name_of_SQLEKM_provider>
WITH PROVIDER_KEY_NAME='<Name_of_Key_in_SQLEKM_provider>',
IDENTITY_VALUE='<Unique_GUID_generator_string>',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=<Symmetric_algorithm_desc>;
GO

Where

  • <Your_database_name> is the name of the database for which you wish to provide encryption. See T-SQL shortcuts and tips for examples.

  • <Name_of_SQLEKM_provider> is the name of the SQLKM provider you are using.

  • <Name_of_key_in_database> is the name you wish to give the key in the database.

  • <Name_of_key_in_SQLEKM_provider> is the name you wish to give the key in the SQLEKM 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_desc> 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 SQLEKM provider from the HSM copy, and imported into the database.

To create a duplicate key:

USE <Your_database_name>
CREATE SYMMETRIC KEY <Name_of_key_in_database> FROM PROVIDER <Name_of_SQLEKM_provider>
WITH PROVIDER_KEY_NAME='<Name_of_Key_in_SQLEKM_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

If you are using a credential based on an OCS, ensure that your operator card is inserted in the HSM card reader before attempting to create and manage symmetric keys.

This query generates a new symmetric key through the SQLEKM provider which will be protected inside the HSM. It then makes the key available to the database.

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

Where <Name of SQLEKM provider> is the name that is used to refer to the SQLEKM provider.

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

Listing symmetric keys in a database

To list the symmetric keys in a database:

  1. Open SQL Server Management Studio on the Management Studio.

  2. Go to Databases > TestDatabase > Security > Symmetric Keys (right-click to select Refresh).

Alternatively, you may check keys by following the methods shown in 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 in the HSM and is accessible through the 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 HSM:

USE TestDatabase
CREATE SYMMETRIC KEY dbAES256Key FROM PROVIDER <Name of provider>
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 nShield HSM, 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 SQLEKM provider to remove it from there.

Refer to your security policies before considering deleting a SQLEKM provider key from the HSM. You cannot import a key into the database once you have deleted that key from the SQLEKM provider. Once deleted from the SQLEKM provider, if you have no Security World backup copy of that key, it will be lost.

Creating and managing asymmetric keys

If you are using a credential based on an OCS, ensure that your operator card is inserted in the HSM card reader before attempting to create and manage asymmetric keys.

Creating an asymmetric key

The following query generates a new asymmetric key in the SQLEKM provider which will be protected inside the HSM, and then makes the key available to the database:

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

<Name_of_key_in_SQLEKM_provider> is the name you wish to give the key in the SQLEKM provider. 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 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:

  1. Open SQL Server Management Studio on the Management Studio.

  2. Go to Databases > TestDatabase > Security > Asymmetric Keys (right-click to select Refresh).

Alternatively, you may check keys by following the methods shown in 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 in the 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 SQLEKM provider:

USE TestDatabase
CREATE ASYMMETRIC KEY dbRSA2048Key
FROM PROVIDER <Name of provider> 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 nShield HSM, 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 SQLEKM provider to remove it from there.

Refer to your security policies before considering deleting a SQLEKM provider key from the HSM. You cannot import a key into the database once you have deleted that key from the SQLEKM provider. Once deleted from the SQLEKM provider, if you have no Security World backup copy of that key, it will be lost.

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 <Name of provider>
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 <Name of provider> is the name that is used to refer to the 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 SQLEKM provider.

  • Importing a (foreign) key that was created outside the SQLEKM provider into its Security World.

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

As regards keys created outside the 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. Entrust provides limited off the shelf key import facilities through use of the generatekey utility or KeySafe application (no key export facilities are supplied).

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 <Your_database_name>
CREATE SYMMETRIC KEY <Name_of_key_in_database> FROM PROVIDER
<Name_of_SQLEKM_provider>
WITH PROVIDER_KEY_NAME='<Name_of_Key_in_SQLEKM_provider>',
IDENTITY_VALUE='<Unique_GUID_generator_string>',
CREATION_DISPOSITION = OPEN_EXISTING;

Where:

  • <Your_database_name> is the name of the database for which you wish to provide encryption. See T-SQL shortcuts and tips for examples.

  • <Name_of_SQLEKM_provider> is the name of the SQLKM provider you are using.

  • <Name_of_key_in_database> is the name you wish to give the key in the database.

  • <Name_of_key_in_SQLEKM_provider> is the name of the externally created key in the SQLEKM 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 <Your_database_name>CREATE ASYMMETRIC KEY <Name_of_key_in_database> FROM PROVIDER<Name_
of_SQLEKM_provider>
WITH PROVIDER_KEY_NAME='<Name_of_Key_in_SQLEKM_provider>', CREATION_DISPOSITION = 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 <Name_of_key_in_SQLEKM_provider>

Transparent Data Encryption - TDE

An example of configuring an AlwaysOn availability group with no shared disk for TDE encryption is given in Failover cluster with HSMs in an AlwaysOn availability group.

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 any SQLEKM provider credential to access or modify TDE protected data.

Note that:

  • If the credential protecting the TDE encryption key is OCS based, the operator cards must be inserted in the HSM card reader for the TDE encryption to be set up and authorized.

  • The person setting up or managing the TDE encryption keys must use the same OCS or softcard 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 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 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 as described earlier in this document, then to set up TDE encryption, it should normally be sufficient to perform the following steps on the active node only:

  • Create TDEKEK

  • Set up TDE login and credential

  • Create TDEDEK and switch on encryption.

These steps are described in more detail below. If these steps are performed on the active node, then the TDE set up should be automatically inherited when you failover to the other node. You should not have to repeat the TDE set up on the second node. This does not apply if you are using an AlwaysOn availability group with no shared disk. In this case, please see Failover cluster with HSMs in an AlwaysOn availability group.

Creating a TDEKEK

The TDEKEK must be protected under the same OCS or softcard 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 <Name of provider>
WITH PROVIDER_KEY_NAME='ekmAsymWrappingKey', CREATION_DISPOSITION =
CREATE_NEW, ALGORITHM = RSA_2048;
GO

Where <Name of provider> is the name that is used to refer to the 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

  1. In SQL Server Management Studio, navigate to Security > Credentials.

  2. Right-click Credentials, then select New Credential.

  3. Set Credential name to tdeCredential (for example).

  4. Set Identity to <OCSname>, where <OCSname> is the name of the OCS or softcard. This must be the same key protector as that used to protect the ekmAsymWrappingKey created above.

  5. Set Password to <passphrase>, where <passphrase> matches the passphrase on the OCS or softcard.

  6. Set Use Encryption Provider to <Name of provider>, where <Name of provider> is the name of the SQLEKM provider you are using. Click OK.

  7. In SQL Server Management Studio, navigate to Security > Logins.

  8. Right-click Logins, then select New Login.

  9. Set Login name to tdeLogin (for example).

  10. Ensure Mapped to asymmetric key is selected, then select dbAsymWrappingKey (the TDEKEK created previously) from the drop down list.

  11. Ensure Map to Credential is selected, then select tdeCredential from the drop down list. Click Add, then click OK.

  12. 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.

  13. 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):

  1. In SQL Server Management Studio, navigate to Databases > TestDatabase.

  2. Right-click TestDatabase, then select Tasks > Manage Database Encryption…​

  3. Set Encryption Algorithm to the AES 256 algorithm.

  4. Ensure that Use server asymmetric key is selected, then select dbAsymWrappingKey from the drop down list.

  5. 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 OCS or softcard 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 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 simple text editor, provided you have appropriate access permissions.

  1. 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.

  2. 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

  1. Following the procedure above (see Creating a TDEKEK) create a new asymmetric TDEKEK called dbAnotherAsymWrappingKey.

  2. Create the new credential anotherTdeCredential.

  3. Create a new TDE login called anotherTdeLogin. Map it to dbAnotherAsymWrappingKey and the new anotherTdeCredential.

  4. In SQL Server Management Studio, navigate to Databases > TestDatabase.

  5. Right-click TestDatabase, then select Tasks > Manage Database Encryption…​

  6. Select Re-Encrypt Database Encryption Key and Use server asymmetric. Select dbAnotherAsymWrappingKey from the drop down list.

  7. Ensure Regenerate Database Encryption Key is not selected.

  8. Ensure Set Database Encryption On is selected, then click OK.

To replace the TDEDEK

  1. In SQL Server Management Studio, navigate to Databases > TestDatabase.

  2. Right-click TestDatabase, then select Tasks > Manage Database Encryption…​

  3. Ensure Re-Encrypt Database Encryption Key is not selected.

  4. Ensure Regenerate Database Encryption Key is selected, then select AES 256 from the drop down list.

  5. 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 encryption keys. These keys can be protected by different credentials. Unlike TDE protection, the user will need to obtain keys from the SQLEKM provider, and must have the correct credential to authorize and load the encryption 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.

  1. 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.

  2. 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 encryption 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

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

  4. 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

  5. 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.

  6. 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]

  7. 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 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 exUSE 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:

  1. Open SQL Server Management Studio on the Management Studio.

  2. Go to Databases > TestDatabase > Tables.

  3. 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 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 Security World keys associated with the current set of credentials:

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

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

  • To correlate symmetric keys 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 '<Friendly_name_of_provider>');
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 <Friendly_name_of_provider> can be found in Checking the configuration for the cryptographic provider you are using.

  • To correlate asymmetric keys 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 '<Friendly_name_of_provider>');
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 <Friendly_name_of_provider> can be found in 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 '<Friendly_name_of_provider>');
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 <Friendly_name_of_provider> can be found in Checking the configuration for the cryptographic provider you are using.

Cross-referencing keys between the cryptographic provider and Security World

The same key may exist under a different name in the cryptographic provider and database, but will not be recognizable at all by direct inspection of keys in the Security World (%NFAST_KMDATA%\local, or %NFAST_KMLOCAL%).

The example below allows you to cross-reference the same key between the cryptographic provider and Security World. The key can in turn be cross-referenced to the same key in the database, as shown in previous examples.

If you are running a failover cluster, you will need to run these procedures on the active server.

In a command window, run the utility:

 nfkminfo –l

You should see something similar to the example below:

Keys protected by softcards:
key_simple_sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-3d4790baa10b3537d84ffe97d2bb03d9008517db
'ekmAES256Key'
key_simple_sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-8789a2543b2fe980b172ca4616b680d76b51bfb4
'ekmRSA2048Key'
key_simple_sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-c9a4569eb22b54b15d91a4feebf5d1799f3750ed
'ekmWrappingKey'
Taking the first key as an example, we can divide the text fields as follows:
Field String Notes

Prefix

key_simple_sqlekm-

The key’s prefix, showing that it is of type 'simple'.

Protector

ef990fcd2115d2784d04fc8c963cdefa3a184264-

The hash that uniquely identifies the OCS card or softcard that is currently protecting the key within the cryptographic provider.

In the example above, the nfkminfo -l output states that the key is protected by a softcard.

Key hash

3d4790baa10b3537d84ffe97d2bb03d9008517db

A unique identifier for that key within the cryptographic provider.

Friendly name

ekmAES256Key

The name entered by the user when the key was generated.

Detailed information about individual keys in the Security World

You can obtain detailed information about individual keys in the Security World by using the utility nfkminfo –k <APPNAME> <IDENT>.

To obtain detailed information about individual keys in the Security World; on a client server, first run the utility as nfkminfo –k. This will provide a list of keys under the headings AppName and Ident similar to the example below:

>nfkminfo -kKey list - 6 keys
AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-
139f8de8a1017c095079208a4ad3e1480bd10170
AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-262cad87e7b6836f4eb571c35c433f95eebeb4cc
AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-
56e6e89ddccf3218827ca49c53b8bf9d10a0e5dc
AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-70ffd81cbb650eac2b148b415d6a903fa6a35e6c
AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-
824bdf1e2a4fa1b30bc39bd52aa322fbc47e253a
AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-bbccc80048164e13deef3868dd4dfbadaf67856a

The ident should match the same key as seen in the Security World (%NFAST_KMDATA%\local, or %NFAST_KMLOCAL%).

Use the AppName and Ident information to obtain information about a specific key as shown in the example below:

C:\>nfkminfo -k simple sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-
bbccc80048164e13deef3868dd4dfbadaf67856a
Key AppName simple Ident sqlekm-ef990fcd2115d2784d04fc8c963cdefa3a184264-
bbccc80048164e13deef3868dd4dfbadaf67856a
BlobKA length 488
BlobPubKA length 0
BlobRecoveryKA length 856
name "ekmTripleDES3Key"
hash bbccc80048164e13deef3868dd4dfbadaf67856a
recovery Enabled
protection PassPhrase
other flags !PublicKey !SEEAppKey !NVMemBlob +0x0
softcard ef990fcd2115d2784d04fc8c963cdefa3a184264
gentime 2020-04-23 16:09:49
SEE integrity key NONE
BlobKA
format 6 Token
other flags 0x0
hkm c7da546dc01a093f53b6ef29e1eaec3b8d4454f6
hkt ef990fcd2115d2784d04fc8c963cdefa3a184264
hkr none
BlobRecoveryKA
format 9 UserKey
other flags 0x0
hkm none
hkt none
hkr 31edffe57fcca13b16835b32d989d8e7bce43a4b
No extra entries
What is called the key_thumbprint when viewing key information through T-SQL queries, is the same as the hash when viewed using the nfkminfo utility.

Changes in the SQLEKM provider require SQL Server restart

If changes are made solely to the SQLEKM provider or associated Security World, there is no automatic mechanism to transmit these changes to the SQL Server. In this case, after such changes have been made, the SQL Server must be restarted in order to recognize them.

Examples of changes within the SQLEKM provider or Security World that will necessitate a SQL Server restart are:

  • Key import.

  • OCS or softcard deletion.

  • Passphrase changes.

  • Insertion or removal of OCS cards from card reader (except where card presence is normally required for ongoing key authorization).

  • Configuration changes affecting the Security World.

Where keys are created or deleted through SQL Server queries, a restart should not normally be required. You will require administrator rights to restart the SQL Server.

To restart the SQL Server:

  1. In the SQL Server Management Studio, right-click on the server name and select Restart

    Or:

  2. On a command line, enter the following commands in succession:

    net stop mssqlserver
net start mssqlserver
System environment changes that affect SQL Server may also require a restart in order to be recognized.