Setup

Prerequisites:

  • A Windows Server with Microsoft SQL server.

  • SQL Server Management Studio installed.

  • The database TestDatabase has been created and is available for the integration.

Perform the following steps:

  1. Install the Security World software and create a Security World

  2. Install the nShield nDSOP

  3. Create the Operator Card Set (OCS) or Softcard

  4. Enable EKM and register the SQLEKM provider

  5. Verify the SQLEKM provider configuration

  6. Create the user SQL Server credential

Install the Security World software and create a Security World

To install the Security World software and create a Security World:

  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:

    Access to the Entrust nShield Support Portal is available to customers under maintenance. To request an account, contact nshield.support@entrust.com.

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

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

    > nfkminfo
World
 generation  2
 state       0x37270008 Initialised Usable ...
 ...
Module #1
 generation 2
 state      0x2 Usable
 ...

Install the nShield nDSOP

To install the nShield nDSOP:

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

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

Create the Operator Card Set (OCS) or Softcard

The OCS or Softcard and associated passphrase will be used to authorize access to specific keys protected by the SQLEKM provider. Typically, an organization’s security policies dictate the use of one or the other.

Create the OCS

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 of one.

Recovering from a power failure requires the OCS to be inserted in the HSM or the TVD.

  1. Ensure the cardlist file located in the path C:\ProgramData\nCipher\Key Management Data\config\ 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 the OCS values. 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 testOCS -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 = edb3d45a28e5a6b22b033684ce589d9e198272c2

    Add the -p (persistent) option to the command above if you want:

    • 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 ability to persist after a reboot.

    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. If the TVD loses connection to the Remote Administration client the database will be inaccessible.

  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
 edb3d45a28e5a6b22b033684ce589d9e198272c2  1/1  none-NL testOCS

Create the Softcard

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.

Unlike OCS protection, which requires a smart card and a passcode, a softcard does not require additional input for recovery after a power failure.

  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 at the prompt.

    > ppmk -n testSC

Enter new pass phrase:
Enter new pass phrase again:
New softcard created: HKLTU 925f67e72ea3c354cae4e6797bde3753d24e7744

  3. Verify the Softcard created:

    > nfkminfo -s
SoftCard summary - 1 softcards:
 Operator logical token hash               name
 925f67e72ea3c354cae4e6797bde3753d24e7744  testSC

    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 testOCS                  0 (0)        1 of 5
  2 testSC                   0 (0)        (softcard)
rocs> quit

Enable EKM and register the SQLEKM provider

To enable EKM and register the SQLEKM provider:

  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
    EKM 1 Enable

  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. nDSOP should be visible. Right-click it to verify that it is enabled.

    EKM 2 Crypto Provider

Verify the SQLEKM provider configuration

To verify the SQLEKM provider configuration:

  1. Run the following query:

    SELECT * FROM sys.cryptographic_providers;
    verify sqlekm 1

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

    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
    verify sqlekm 3

    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

Create the user SQL Server credential

To create the user SQL Server credential:

  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
 edb3d45a28e5a6b22b033684ce589d9e198272c2  1/5  none-NL testOCS

> nfkminfo -s
SoftCard summary - 1 softcards:
 Operator logical token hash               name
 925f67e72ea3c354cae4e6797bde3753d24e7744  testSC

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

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

  4. Under New Credential:

    1. Enter the Credential name.

    2. For Identity, enter the OCS card name.

    3. Enter a Password, and confirm the password.

    4. Select Use Encryption Provider.

    5. For Provider, select nDSOP.

    6. Select OK.

      server credential 1

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

    server credential 2

  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.

    server credential 3