Microsoft SQL Server: nShield HSM Integration Guide

Entrust tested the integration with the following versions:

Entrust tested the integration with the following nshield HSM hardware and software versions, and SQLEKM provider:

Supported nshield SQLEKM provider:

Familiarize yourself with:

• The Microsoft SQL Server features and documentation.

• The Microsoft SQL Server Management Studio features and documentation.

• The T-SQL language. You should be able to perform basic SQL tasks such as creating a database or tables.

• Database security concepts and practices.

• The documentation for the HSM.

1. Install the Security World software by double-clicking on the SecWorld_Windows-xx.xx.xx.iso file. For detailed instructions, see the Installation Guide and the User Guide for the HSM available from the installation disc.

2. Add the Security World utilities path C:\Program Files\nCipher\nfast\bin to the Windows system path.

3. Open the firewall port 9004 for the HSM connections.

4. Install the nshield Connect HSM locally, remotely, or remotely via the serial console. See the following nshield Support articles, and the Installation Guide for the HSM:

   • https://nshieldsupport.entrust.com/hc/en-us/articles/360021378272-How-To-Locally-Set-up-a-new-or-replacement-nShield-Connect

   • https://nshieldsupport.entrust.com/hc/en-us/articles/360014011798-How-To-Remotely-Setup-a-new-or-replacement-nShield-Connect

   • https://nshieldsupport.entrust.com/hc/en-us/articles/360013253417-How-To-Remotely-Setup-a-new-or-replacement-nShield-Connect-XC-Serial-Console-Model

5. Open a command window and run the following to confirm that the HSM is operational .

   C:\Users\dbuser>enquiry
   Server:
    enquiry reply flags  none
    enquiry reply level  Six
    serial number        530E-02E0-D947 7724-8509-81E3 09AF-0BE9-53AA 9E10-03E0-D947
    mode                 operational
   ...
   Module #1:
    enquiry reply flags  none
    enquiry reply level  Six
    serial number        530E-02E0-D947
    mode                 operational
    ...

6. Create your Security World if one does not already exist, or copy an existing one. Follow your organization’s security policy for this. Create extra ACS cards as spares in case of a card failure or lost. These cannot be duplicated after the Security World is created.

7. Confirm that the Security World is usable .

   C:\Users\dbuser>nfkminfo
   World
    generation  2
    state       0x37270008 Initialised Usable ...
    ...
   Module #1
    generation 2
    state      0x2 Usable
    ...

1. Mount the nDSOP_Windows-x.x.x.iso file.

2. Double-click the setup file and follow the instructions.

The OCS or Softcard and associated passphrase will be used to authorize access to specific keys protected by the SQLEKM provider. Typically, one or the other will be used, rarely both.

A SQL Server credential (as used for EKM) maps one protecting token to one stored passphrase. It can store information for only one token at a time. An OCS does have a quorum. But a quorum greater than one cannot be directly supported by the SQL Server, neither can different passphrases for each card in an OCS. Therefore, use an OCS quorum of one.

Recovering from a power failure requires the OCS to be inserted in the HSM itself regardless. Do not use OCS with a TVD if you want automatic recovery unless you come up with a work-around.

1. Ensure the C:\ProgramData\nCipher\Key Management Data\config\cardlist file contains the serial number of the card(s) to be presented, or the wildcard value.

2. Open a command window as administrator.

3. Execute the following command. Enter a passphrase or password at the prompt. Follow your organization’s security policy for this for the values K/N, where K=1 as mentioned above. Use the same passphrase for all the OCS cards in the card set (one for each person with access privilege, plus the spares). After an OCS card set has been created, the cards cannot be duplicated. Notice that slot 2 , remote via a Trusted Verification Device (TVD), is used to present the card.

   >>createocs -m1 -s2 -N nDSOPocs -Q 1/1
   
   FIPS 140-2 level 3 auth obtained.
   
   Creating Cardset:
    Module 1: 0 cards of 1 written
    Module 1 slot 0: Admin Card #1
    Module 1 slot 2: blank card
    Module 1 slot 3: empty
    Module 1 slot 2:- passphrase specified - writing card
   Card writing complete.
   
   cardset created; hkltu = 6c9c2b7c32fb884a531e08f7a37edc3924fb5e76

   Add the -p (persistent) option to the command above to be able to encrypt/decrypt the database after the OCS card has been removed from the HSM front panel slot, or from the TVD. The authentication provided by the OCS as shown in the command line above is non-persistent and only available while the OCS card is inserted in the HSM front panel slot, or the TVD.

4. Verify the OCS created.

   >nfkminfo -c
   Cardset list - 1 cardsets:  (P)ersistent/(N)ot, (R)emoteable/(L)ocal-only
    Operator logical token hash               k/n timeout  name
    6c9c2b7c32fb884a531e08f7a37edc3924fb5e76  1/1  none-NL nDSOPocs

A SQL Server credential (as used for EKM) maps one protecting token to one stored passphrase. Softcards are singular and do not have a quorum, so the SQL Server credential matches them quite well.

Recovering from a power failure with Softcard does not requires smart cards.

1. Ensure the C:\Program Files\nCipher\nfast\cknfastrc file exists with the following content. Otherwise, create it.

   > type "C:\Program Files\nCipher\nfast\cknfastrc"
   CKNFAST_LOADSHARING=1

2. Execute the following command. Enter a passphrase or password at the prompt.

   >>ppmk -n nDSOPsoftcard
   
   Enter new pass phrase:
   Enter new pass phrase again:
   New softcard created: HKLTU 629a3a902eec139fb5136afec7b0f0801f45b60d

3. Verify the Softcard created.

   >nfkminfo -s
   SoftCard summary - 1 softcards:
    Operator logical token hash               name
    629a3a902eec139fb5136afec7b0f0801f45b60d  nDSOPsoftcard

   The rocs utility shows the OCS and Softcard created.

   >rocs
   `rocs' key recovery tool
   Useful commands: `help', `help intro', `quit'.
   rocs> list cardset
   No. Name                     Keys (recov) Sharing
     1 nDSOPocs                 1 (1)        1 of 1
     2 nDSOPsoftcard            0 (0)        (softcard)
   rocs> quit

1. Launch the SQL Server Management Studio GUI.

2. Enable EKM by executing the following query:

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

3. Register the SQLEKM provider with the SQL Server by executing the following query:

   CREATE CRYPTOGRAPHIC PROVIDER nDSOP
       FROM FILE = 'C:\Program Files\nCipher\nfast\bin\ncsqlekm.dll'

4. Check the SQLEKM provider is listed in the SQL Server Management Studio GUI. Go to Security > Cryptographic Providers . You should see nDSOP . Right-click it to verify that it is enabled.

1. Run the following query.

   SELECT * FROM sys.cryptographic_providers;

   Verify the following:

   • The version matched that of the nDSOP installation iso .

   • Path to dll is correct.

   • is_enabled column set to 1 .

2. Run the following query.

   SELECT * FROM sys.dm_cryptographic_provider_properties;

   Verify the following:

   Column	Value
   friendly_name	nCipher SQLEKM Provider
   authentication_type	BASIC
   symmetric_key_support	1
   asymmetric_key_support	1

3. Verify the supported cryptographic algorithms can be queried by running 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

   Notice each key type has its set of valid algorithms.

   Key Type	Algorithm
   Symmetric	AES_128, AES_192, ASE_256
   Asymmetric	RSA_2048, RSA_3072, RSA_4096

1. Verify the OCS or Softcard created above.

   >nfkminfo -c
   Cardset list - 1 cardsets:  (P)ersistent/(N)ot, (R)emoteable/(L)ocal-only
    Operator logical token hash               k/n timeout  name
    6c9c2b7c32fb884a531e08f7a37edc3924fb5e76  1/1  none-NL nDSOPocs
   
   >nfkminfo -s
   SoftCard summary - 1 softcards:
    Operator logical token hash               name
    629a3a902eec139fb5136afec7b0f0801f45b60d  nDSOPsoftcard

2. Navigate to Security > Credentials in SQL Server Management Studio.

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

4. Enter the credential name, and the OCS card name and passphrase. Check Use Encryption Provider and select nDSOP . Select OK .

5. Verify the new credential in Security > Credentials . May need to right-click and select Refresh .

6. Navigate to Security > Logins . Right-click the login used to access the TestDatabase and select Properties .

7. Check Map to Credentials in the dialog. Select the server credential created above in the drop-down to the right. Then select Add , and select OK .

The TDEKEK is created in the master database.

1. Insert the OCS in the HSM slot or TVD. No action is needed if you are using Softcard protection.

2. Run the following query.

   USE master;
   CREATE ASYMMETRIC KEY "<name_of_key_in_database>"
   FROM PROVIDER "<SQLEKM_provider>"
   WITH
   PROVIDER_KEY_NAME = '<name_of_key_in_SQLEKM_provider>',
   CREATION_DISPOSITION = CREATE_NEW,
   ALGORITHM = <asymmetric_algorithm_desc>;
   GO

   name_of_key_in_database

   The name you want to give the key in the database.

   name_of_key_in_SQLEKM_provider

   The name you want to give the key in the SQLEKM provider.

   asymmetric_algorithm_desc

   A valid asymmetric key algorithm descriptor. See Verify the SQLEKM provider configuration .

   Example:

   USE master;
   CREATE ASYMMETRIC KEY "AsymTestWrappingKeyDatabase"
   FROM PROVIDER "nDSOP"
   WITH
   PROVIDER_KEY_NAME = 'AsymTestWrappingKeySQLEKM',
   CREATION_DISPOSITION = CREATE_NEW,
   ALGORITHM = RSA_2048;
   GO

   Notice the newly created key highlighted in the object explorer.

3. The key generated can also be verified by a CLI command.

   >nfkminfo -l
   
   Keys protected by cardsets:
   key_simple_sqlekm-6c9c2b7c32fb884a531e08f7a37edc3924fb5e76-67703babb0c40915c62f6afe4c49c741c414e24b 'AsymTestWrappingKeySQLEKM'

   The rocs utility shows the names and protection methods of the keys.

   >rocs
   'rocs' key recovery tool
   Useful commands: 'help', 'help intro', 'quit'.
   rocs> list keys
     No. Name                     App        Protected by
       1 AsymmetricTestKeySQLEKM  simple     nDSOPocs
   rocs> quit

A tdeLogin and tdeCredential allows an ordinary database user, who is fully authorized to use the database, but has no SQLEKM credentials of their own, to perform query operations using a TDE encrypted database. Without the tdeLogin and tdeCredential, then every user would need their own credentials. It is beyond the scope of this document to provide an example of how to use these credentials, only on how to create them.

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

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

3. Enter the Credential name .

4. Enter the Identity as the name of the OCS or Softcard created above.

5. Enter the Password as the passphrase on the OCS or Softcard.

6. Select Use Encryption Provider . Select the provider. Then select OK .

7. Notice the credential created.

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

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

3. Enter the Login name .

4. Select Mapped to asymmetric key . Then select the asymmetric key that you created earlier.

5. Select Map to Credential . Then select the TDE credential created earlier. Then select Add .

6. Select OK .

7. Notice the login created.

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 AES 256 or your choice.

4. Select Use server asymmetric key . Then select the asymmetric key that you have created earlier.

5. Select Set Database Encryption On . Then select OK . Restart the Microsoft SQL Server Management Studio and repeat these steps if it fails.

6. Run the following query to verify the encryption state.

   /****** Script for SelectTopNRows command from SSMS  ******/
   SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state, CASE e.encryption_state
   WHEN 0 THEN 'No database encryption key present, no encryption'
   WHEN 1 THEN 'Unencrypted'
   WHEN 2 THEN 'Encryption in progress'
   WHEN 3 THEN 'Encrypted'
   WHEN 4 THEN 'Key change in progress'
   WHEN 5 THEN 'Decryption in progress'
   END AS encryption_state_desc, c.name, e.percent_complete FROM sys.dm_database_encryption_keys AS e
   LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint

   The following table shows the value returned for encryption state and the meaning.

   Encryption state	Meaning
   0	Encryption disabled (or no encryption key)
   1	Unencrypted or Decrypted
   2	Unencrypted or Decrypted
   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)

7. Turn off encryption of by clearing Set Database Encryption On in the steps above.

This is the wrapping key called TDE Key Encryption Key, an asymmetric key protected by the SQLEKM provider in the nshield HSM.

1. Follow the procedure in Create a TDEKEK to create a new asymmetric TDEKEK. Give it a new name.

   Example:

   USE master;
   CREATE ASYMMETRIC KEY "AsymTestWrappingKeyDatabase2"
   FROM PROVIDER "nDSOP"
   WITH
   PROVIDER_KEY_NAME = 'AsymTestWrappingKeySQLEKM2',
   CREATION_DISPOSITION = CREATE_NEW,
   ALGORITHM = RSA_2048;
   GO

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

3. Right-click TestDatabase, then select Tasks > Manage Database Encryption .

4. Select Re-Encrypt Database Encryption Key and Use server asymmetric . Select newly created asymmetric key AsymTestWrappingKeyDatabase2 .

5. Unselect Regenerate Database Encryption Key .

6. Select Set Database Encryption On . Select OK .

This is the key called TDE Database Encryption Key, a symmetric used to perform the actual encryption of the database. It is created by SQL Server and cannot be exported from the database. It is protected within the database by encrypting it with a wrapping key TDEKEK.

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

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

3. Unselect Re-Encrypt Database Encryption Key .

4. Select Regenerate Database Encryption Key . Then select AES 256 .

5. Select Set Database Encryption On . Then select OK .

6. Verify the encryption state as shown in Create the TDEDEK and switch on encryption .

The Security World data is inherently encrypted and does not require any further encryption operation to protect it. It can only be used by someone who has access to a quorum of the correct ACS cards, or the OCS card, Softcard, their passphrases, an nshield HSM and nshield Security World Software. Therefore, backup simply consists of making a copy of the Security World files and saving the copy in a safe location, as necessary to restore the keys used by the database.

1. Back up C\:ProgramData\nCipher\Key Management Data .

2. Securely store and keep a record of ACS and OCS cards associated with each Security World, preferable using the serial number on the cards.

3. The Softcard, used instead of OCS, resides in the Key Management Data folder. It is backed up when you back up C\:ProgramData\nCipher\Key Management Data .

4. Keep a record of which database and which Security World backups correspond to each other.

Restoring a Security World simply means restoring a backup copy of the Security World folder C\:ProgramData\nCipher\Key Management Data .

The ACS is required if the Security World you are restoring is not already loaded onto your HSM. See the Installation Guide and the User Guide for the HSM. A short version is available at https://nshieldsupport.entrust.com/hc/en-us/articles/360021378272-How-To-Locally-Set-up-a-new-or-replacement-nShield-Connect .

1. Create the backup devices by running the following query:

   -- Encrypted Backup
   USE master;
   GO
   
   --Provide backup device and locations
   EXEC sp_addumpdevice 'disk', 'EncryptedTestDatabaseBackup',
   'C:\Program Files\Microsoft SQL Server\MSSQL15.INTEROPDATABASE\MSSQL\Backup\TestDatabaseEncrypted.bak';
   GO
   
   EXEC sp_addumpdevice 'disk', 'EncryptedTestDatabaseBackupLog',
   'C:\Program Files\Microsoft SQL Server\MSSQL15.INTEROPDATABASE\MSSQL\Backup\TestDatabaseEncryptedLog.bak';
   GO

   Notice the devices created.

2. Create the backup by running the following query:

   -- Encrypted Backup
   USE master;
   GO
   
   ALTER DATABASE TestDatabase
   SET RECOVERY FULL;
   GO
   
   -- Back up the encrypted database
   BACKUP DATABASE TestDatabase TO EncryptedTestDatabaseBackup;
   GO
   
   -- Back up the encrypted log
   BACKUP LOG TestDatabase TO EncryptedTestDatabaseBackupLog;
   GO

   Notice the backup files created.

   C:\Program Files\Microsoft SQL Server\MSSQL15.INTEROPDATABASE\MSSQL\Backup>dir
    Volume in drive C has no label.
    Volume Serial Number is 2E72-65D9
   
    Directory of C:\Program Files\Microsoft SQL Server\MSSQL15.INTEROPDATABASE\MSSQL\Backup
   
   07/15/2021  04:15 PM    <DIR>          .
   07/15/2021  04:15 PM    <DIR>          ..
   07/15/2021  04:15 PM         3,760,640 TestDatabaseEncrypted.bak
   07/15/2021  04:15 PM            86,528 TestDatabaseEncryptedLog.bak
                  2 File(s)      3,847,168 bytes
                  2 Dir(s)   6,057,644,032 bytes free

If the database is encrypted, the backup will also be encrypted. If the database is not encrypted, then the backup will not be encrypted. If you want to create an encrypted backup from a non-encrypted database, you will have to create the non-encrypted backup file, and then encrypt the file using an independent encryption tool.

Restore a TDE encrypted database in a similar manner as an un-encrypted database. But for TDE encrypted database the Security World needs to be restored before restoring the encrypted database. The OCS, if used, needs to be inserted in the HSM before restoring the encrypted database. Otherwise the restored database will come up as (Restore Pending) .

1. Install the Security World software and the nshield nDSOP if rebuilding the server. Do not create a Security World.

2. Restore the Security World.

3. Insert the OCS in the HSM front panel slot, or the TVD if using OCS protection.

4. Enable EKM and register the SQLEKM provider if rebuilding the server.

5. Create the SQL Server credential if rebuilding the server. The OCS and Softcard are in the restored Security World.

6. Verify the SQLEKM provider configuration if rebuilding the server.

7. The database wrapping key (TDEKEK) should already exist in the restored Security World. Import it into the master database by running the following query:

   USE master;
   GO
   
   -- Import TDEKEK2
   CREATE ASYMMETRIC KEY "AsymTestWrappingKeyDatabase2"
   FROM PROVIDER "nDSOP"
   WITH
   PROVIDER_KEY_NAME = 'AsymTestWrappingKeySQLEKM2',
   CREATION_DISPOSITION = OPEN_EXISTING;
   GO

8. Recreate the TDE logins and credentials by running the following query. Notice the name of the OCS ( nDSOPocs ), and Softcard ([.gui] nDSOPsoftcard ) created earlier.

   OCS:

   USE master;
   GO
   
   -- tdeLogin and tcdCredential
   CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY AsymTestWrappingKeyDatabase2;
   CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'nDSOPocs', SECRET = 'ncipher'
   FOR CRYPTOGRAPHIC PROVIDER nDSOP;
   ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
   GO

   Softcard:

   USE master;
   GO
   
   -- tdeLogin and tdeCredential
   CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY AsymTestWrappingKeyDatabase2;
   CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'nDSOPsoftcard', SECRET = 'ncipher'
   FOR CRYPTOGRAPHIC PROVIDER nDSOP;
   ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
   GO

9. Restore the database by running the following query:

   USE master
   ALTER DATABASE TestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   RESTORE DATABASE [TestDatabase] FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.INTEROPDATABASE\MSSQL\Backup\TestDatabaseEncrypted.bak'
   WITH REPLACE
   GO

10. Return to multiple user mode by running the following script.

    USE master;
    ALTER DATABASE TestDatabase SET
    MULTI_USER;
    GO

1. Insert the OCS in the HSM slot or TVD. No action is needed if you are using Softcard protection.

2. To create an asymmetric key, run the following query:

   USE master;
   CREATE ASYMMETRIC KEY "<name_of_key_in_database>"
   FROM PROVIDER "<SQLEKM_provider>"
   WITH
   PROVIDER_KEY_NAME = '<name_of_key_in_SQLEKM_provider>',
   CREATION_DISPOSITION = CREATE_NEW,
   ALGORITHM = <asymmetric_algorithm_desc>;
   GO

   Where:

   name_of_key_in_database	The name you want to give the key in the database. For example, AsymKey .
   SQLEKM_provider	The SQLEKM provider. For example, nDSOP .
   name_of_key_in_SQLEKM_provider	The name you want to give the key in the SQLEKM provider. For example, AsymKey .
   asymmetric_algorithm_desc	A valid asymmetric key algorithm descriptor. For example, RSA_2048 . See Verify the SQLEKM provider configuration .

3. To create a symmetric key, run the following query:

   USE master;
   CREATE SYMMETRIC KEY "SymKey"
   FROM PROVIDER "nDSOP"
   WITH
   PROVIDER_KEY_NAME = 'SymKey',
   CREATION_DISPOSITION = CREATE_NEW,
   ALGORITHM = AES_256;
   GO

4. To import an existing nDSOP EKM protected key into the SQL Server, run the following query:

   CREATE SYMMETRIC KEY "ExistingSymKey"
   FROM PROVIDER "nDSOP"
   WITH
   PROVIDER_KEY_NAME = 'ExistingSymKey',
   CREATION_DISPOSITION=OPEN_EXISTING
   GO

5. Verify the key:

   >nfkminfo -l
   
   Keys protected by cardsets:
    key_simple_sqlekm-f82cca5299d7f8df581154de03b736998fd59f89-5d272ab1d5d69ab1300209ea5d0d1c50f59fafa4 'SymKey'

1. Create a table:

   USE master;
   CREATE TABLE testdb (
   employee_name varchar (255) NOT NULL,
   employee_secret varbinary (MAX) NOT NULL
   )
   GO

2. Insert a row into the table. The data within the second column is encrypted using the previously created key. For example:

   INSERT INTO testdb
   VALUES ('Employee Name', ENCRYPTBYKEY(KEY_GUID('SymKey'), 'Employee Password'))

3. Run the following to decrypt the values of the column:

   SELECT CONVERT(varchar, DECRYPTBYKEY(employee_secret)) employee_secret FROM testdb

   Asymmetric encryption and decryption examples:

   INSERT INTO testdb
   VALUES ('Employee Name', ENCRYPTBYASYMKEY(ASYMKEY_ID('AsymKey'), 'Secret Value'))

   SELECT CONVERT(varchar, DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey'), employee_secret)) employee_secret FROM testdb

The following procedure will be performed on a Windows Server 2016 with Microsoft SQL Server 2106, and nDSOP v1.0. A database called TestDatabase has been created and encrypted, and will be used in this procedure.

1. Backup the Security World.

2. Backup the database.

3. Run the following query to verify the encryption state.

   /****** Script for SelectTopNRows command from SSMS  ******/
   SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state, CASE e.encryption_state
   WHEN 0 THEN 'No database encryption key present, no encryption'
   WHEN 1 THEN 'Unencrypted'
   WHEN 2 THEN 'Encryption in progress'
   WHEN 3 THEN 'Encrypted'
   WHEN 4 THEN 'Key change in progress'
   WHEN 5 THEN 'Decryption in progress'
   END AS encryption_state_desc, c.name, e.percent_complete FROM sys.dm_database_encryption_keys AS e
   LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint

4. Disable the EKM provider. Select Security Cryptographic Providers . Right-click on the provider and select Disable .

5. Restart the SQL Server from the Windows MSSMS or services.

6. Wait for 60 seconds after the restart. Then check the database status. Notice Recovery Pending next to TestDatabase .

7. Un-install nDSOP v1.01 EKM provider using the Windows Control Panel > Programs > Programs and Features .

8. Install nDSOP v2.1 EKM provider by mounting the .iso file and double-clicking setup .

9. Insert the OCS in the HSM slot or TVD. No action is needed if you are using Softcard protection.

10. Retarget the keys by running the sqlekm_retarget_keys command.

    C:\Users\Administrator>nfkminfo -k
    
    Key list - 2 keys
     AppName pkcs11               Ident uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-56ac051fb249f91e641b065dc12fec8a9fea2419
     AppName pkcs11               Ident uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-c88b06f02bdca29f2a98b9c9352daf9191fc8afd
    
    C:\Users\Administrator>sqlekm_retarget_keys --all
    Found 2 keys to retarget
    Retargetted: key_pkcs11_uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-c88b06f02bdca29f2a98b9c9352daf9191fc8afd
    Retargetted: key_pkcs11_uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-56ac051fb249f91e641b065dc12fec8a9fea2419
    
    C:\Users\Administrator>nfkminfo -k
    
    Key list - 4 keys
     AppName pkcs11               Ident uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-56ac051fb249f91e641b065dc12fec8a9fea2419
     AppName pkcs11               Ident uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-c88b06f02bdca29f2a98b9c9352daf9191fc8afd
     AppName simple               Ident sqlekm-79dfaf7c3311d22d240a7257e5e760ede89fbc70-b1844c5bb4eadbdb1166dcdb64f4c5d59e4e408c
     AppName simple               Ident sqlekm-79dfaf7c3311d22d240a7257e5e760ede89fbc70-fa9380a3e111df122b0e02dd37c1233da89b8e16

11. Open the C:\ProgramData\nCipher\Key Management Data\local folder. Move all pkcs11 keys to another folder. Leave the simple keys in the current folder.

    C:\ProgramData\nCipher\Key Management Data>mkdir local_pcks11_keys
    
    C:\ProgramData\nCipher\Key Management Data>move local\key_pkcs11* local_pcks11_keys\.
    C:\ProgramData\nCipher\Key Management Data\local\key_pkcs11_uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-56ac051fb249f91e641b065dc12fec8a9fea2419
    C:\ProgramData\nCipher\Key Management Data\local\key_pkcs11_uc79dfaf7c3311d22d240a7257e5e760ede89fbc70-c88b06f02bdca29f2a98b9c9352daf9191fc8afd
            2 file(s) moved.
    
    C:\ProgramData\nCipher\Key Management Data>nfkminfo -k
    
    Key list - 2 keys
     AppName simple               Ident sqlekm-79dfaf7c3311d22d240a7257e5e760ede89fbc70-b1844c5bb4eadbdb1166dcdb64f4c5d59e4e408c
     AppName simple               Ident sqlekm-79dfaf7c3311d22d240a7257e5e760ede89fbc70-fa9380a3e111df122b0e02dd37c1233da89b8e16

12. Set the new provider by running he following script.

    --ChangeToNewProvider.sql
    
    ALTER CRYPTOGRAPHIC PROVIDER nDSOP
    FROM FILE = 'C:\Program Files\nCipher\nfast\bin\ncsqlekm.dll';
    GO

13. Enable the EKM provider. Select Security > Cryptographic Providers . Right-click the provider and select Enable .

14. Verify the new EKM provider version by running the following script. Notice the provider_version .

    SELECT * FROM sys.dm_cryptographic_provider_properties;

15. Restart the SQL Server from the Windows MSSMS or services. Wait for 60 seconds after the restart.

16. Check and refresh database status. Notice the Recovery Pending message next to the TestDatabase goes away.

17. Verify the encryption state by running the following script. Notice the encryption_state_desc shown as Encrypted .

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state, CASE e.encryption_state
    WHEN 0 THEN 'No database encryption key present, no encryption'
    WHEN 1 THEN 'Unencrypted'
    WHEN 2 THEN 'Encryption in progress'
    WHEN 3 THEN 'Encrypted'
    WHEN 4 THEN 'Key change in progress'
    WHEN 5 THEN 'Decryption in progress'
    END AS encryption_state_desc, c.name, e.percent_complete FROM sys.dm_database_encryption_keys AS e
    LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint