Uninstalling and upgrading

If you delete a SQLEKM provider login credential you will no longer be able to use it for the SQLEKM provider.
If you delete an associated SQL Server login you will no longer be able to use it to access the SQL Server or SQLEKM provider and will be locked out.

Turning off TDE and removing TDE setup

You must turn off TDE on all your databases and remove TDE setup before uninstalling the nShield Database Security Option Pack. Otherwise, you will not be able to decrypt any databases encrypted with TDE.

Before disabling and removing TDE encryption you are advised to back up the encrypted database (see Backing up a database with SQL Server Management studio) and associated Security World.

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

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

  3. Ensure Set Database Encryption On is deselected, then click OK.

  4. Wait for the decryption process to finish. Check this by referring to How to check the TDE encryption/decryption state of a database.

  5. When the database has completed decryption, drop the encryption key using the following T‑SQL query:

    USE TestDatabase
DROP DATABASE ENCRYPTION KEY;
GO

  6. Restart the database instance. If you are using a database failover cluster, you may have to do this directly on the active server. In SQL Server Management Studio right-click on the instance and select Restart.

  7. In SQL Server Management Studio, navigate to Security > Logins, and select the TDE login you wish to remove (for example, tdeLogin). Right-click on the selected login and select Properties.

  8. Ensure the associated credential (for example, tdeCredential) is highlighted then choose Remove. Untick the box Map to credential. Click OK.

  9. In SQL Server Management Studio, navigate to Security > Credentials, and select the same credential you previously removed from the login (for example, tdeCredential). Right-click on the credential and select Delete. In the following screen, select OK.

  10. In SQL Server Management Studio, navigate to Security > Logins, and select the TDE login you wish to remove (for example, tdeLogin). Right-click on the selected login and select Delete. In the following screen, select OK.

  11. In SQL Server Management Studio, navigate to Databases > System Databases > master >Security > Asymmetric keys.

    • Select the key you wish to remove (for example, dbAsymWrappingKey). Right-click on the key and select Delete.

    • Alternatively, you can use the following query:

      USE master
DROP ASYMMETRIC KEY dbAsymWrappingKey REMOVE PROVIDER KEY;
GO

Uninstalling the nShield Database Security Option Pack

Do not uninstall the nShield Database Security Option Pack until you have:

  • decrypted any data encrypted using the SQLEKM provider in all your databases

  • turned off TDE.

To uninstall the nShield Database Security Option Pack:

  1. Remove the loginCredential from the logged-in user:

    1. In SQL Server Management Studio, select Security > Logins and open up the properties of the logged-in user.

    2. Select loginCredential, then click Remove, then OK.

  2. Select Security > Credentials, and delete the loginCredential.

  3. Disable and remove the SQLEKM provider:

    1. Select Security > Cryptographic Providers.

    2. Right-click to select the SQLEKM provider and click Disable Provider.

    3. A dialog is displayed which shows that this action was successful. Click Close.

    4. Right-click to select the disabled SQLEKM provider, then click Delete, then OK.

  4. Select Start > Control Panel > Administrative Tools > Services (or Start > Administrative Tools > Services, depending on your version of Windows). Select SQL Server (MSQLSERVER) and click Action > Stop.

  5. Select Start > Control Panel > Add/Remove programs (or Uninstall program, depending on your version of Windows). Select nShield Database Security Option Pack then click Uninstall.

  6. A dialog is displayed asking if you want to continue with uninstalling the nShield Database Security Option Pack. Click Yes.

  7. A setup status screen is displayed while the nShield Database Security Option Pack is uninstalled. Click Finish to complete the removal of the program from your system.

  8. Select Start > Control Panel > Administrative Tools > Services (or Start > Administrative Tools > Services, depending on your version of Windows). Select SQL Server (MSQLSERVER) then click Action > Start.

Upgrading

Follow the instructions for your system:

Upgrading a standalone system

Enhancements will be made to the nShield Database Security Option Pack over time, and product upgrades made available to customers.

Upgrading is a service affecting operation.

Before upgrading, please confirm the following:

  • The system and the database are in a working state.

  • The version of the SQLEKM provider:

    SELECT * FROM sys.dm_cryptographic_provider_properties;

To upgrade the product:

  1. Disable the SQL provider using the following query:

    ALTER CRYPTOGRAPHIC PROVIDER SQLEKM
DISABLE;
GO

  2. Restart SQL Server.

  3. Uninstall the existing nShield Database Security Option Pack:

    1. Ensure that you are signed in as Administrator or as a user with local administrator rights.

    2. Select select Start > Control Panel.

    3. Depending on your version of Windows, select Add/Remove programs or Uninstall program.

    4. Select nShield Database Security Option Pack, then click Uninstall.

    5. A dialog is displayed, asking if you want to continue with uninstalling the nShield Database Security Option Pack. Click Yes.

    6. A setup status screen is displayed while the nShield Database Security Option Pack is uninstalled. Click Finish to complete the removal of the program from your system.

  4. Install the upgraded version of the nShield Database Security Option Pack:

    1. Still signed in as Administrator or as a user with local administrator rights, use the provided installation media and launch setup.msi manually.

    2. Follow the onscreen instructions. Accept the license terms, and click Next to continue.

    3. The SQLEKM provider will be installed to %NFAST_HOME%. Click Install to start installation.

    4. Click Finish to complete the installation.

  5. Retarget your existing keys using the sqlekm_retarget_keys tool.

    This will preserve your existing pkcs11 keys, creating copies usable by the new SQLEKM provider.

    Open a command prompt, and execute sqlekm_retarget_keys, specifying either --all (to retarget all existing pkcs11 keys) or --key-idents <the list of key idents> (to retarget only the specified pkcs11 keys). For example:

    sqlekm_retarget_keys --all

  6. Specify the new SQLEKM provider with the following query (specifying the path as appropriate):

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

  7. Enable the new SQLEKM provider using the following query:

    ALTER CRYPTOGRAPHIC PROVIDER SQLEKM
ENABLE;
GO

  8. Restart SQL Server and check that the system and database are in a working state. Confirm that the version of the SQLEKM provider is as expected with the following query:

    SELECT * FROM sys.dm_cryptographic_provider_properties;

Upgrading a clustered system

Enhancements will be made to the nShield Database Security Option Pack over time, and product upgrades made available to customers.

Upgrading is a service affecting operation.

Before upgrading, please confirm the following:

  • The system and the database are in a working state.

  • Each of the nodes can become the active node.

  • The version of the SQLEKM provider:

    SELECT * FROM sys.dm_cryptographic_provider_properties;

The procedure is based on upgrading a passive node. This means that the particular node will have to be taken out of service. The following steps are dependent on being able to pause a passive node in the cluster, using the Failover Cluster Manager via the Windows Server Manager (Server Manager Dashboard > Tools > Failover Cluster Manager). The paused node is the one to be upgraded. Windows Server 2016 and later versions are automatically cluster-aware. Therefore the Failover Cluster Manager should be available on all versions of Windows that can run SQL Server Enterprise.

To upgrade the product:

  1. Select a passive node (Node U) that will be temporarily out of service while it is upgraded. At least one other node (Node A) must remain active to continue service.

  2. Using the Failover Cluster Manager (Server Manager Dashboard > Tools > Failover Cluster Manager), open the Nodes tree, and select Node U. Pause Node U by selecting Pause > Drain roles.

  3. On node U, disable the SQL provider using the following query:

    ALTER CRYPTOGRAPHIC PROVIDER SQLEKM
DISABLE;
GO

  4. On node U, uninstall the existing nShield Database Security Option Pack:

    1. Ensure that you are signed in as Administrator or as a user with local administrator rights.

    2. Select Start > Control Panel.

    3. Depending on your version of Windows, select Add/Remove programs or Uninstall program.

    4. Select nShield Database Security Option Pack, then click Uninstall.

    5. A dialog is displayed, asking if you want to continue with uninstalling the nShield Database Security Option Pack. Click Yes.

    6. A setup status screen is displayed while the nShield Database Security Option Pack is uninstalled. Click Finish to complete the removal of the program from your system.

  5. On node U, install the upgraded version of the nShield Database Security Option Pack:

    1. Still signed in as Administrator or as a user with local administrator rights, use the provided installation media and launch setup.msi manually.

    2. Follow the onscreen instructions. Accept the license terms, and click Next to continue.

    3. The SQLEKM provider will be installed to %NFAST_HOME%. Click Install to start installation.

    4. Click Finish to complete the installation.

  6. On node U, retarget your existing keys using the sqlekm_retarget_keys tool.

    This will preserve your existing pkcs11 keys, creating copies usable by the new SQLEKM provider.

    Open a command prompt on node U, and execute sqlekm_retarget_keys, specifying either --all (to retarget all existing pkcs11 keys) or --key-idents <the list of key idents> (to retarget only the specified pkcs11 keys). For example: sqlekm_retarget_keys.py --all

  7. On Node U, specify the new SQLEKM provider with the following query (specifying the path as appropriate):

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

  8. On Node U, enable the new SQLEKM provider using the following query:

    ALTER CRYPTOGRAPHIC PROVIDER SQLEKM
ENABLE;
GO

  9. Using the Failover Cluster Manager (Server Manager Dashboard > Tools > Failover Cluster Manager), open the Nodes tree, and select Node U. Resume Node U by selecting Resume > Do not fail roles.

  10. On Node U, check that the system and database are in a working state. Confirm that the version of the SQLEKM provider is as expected with the following query:

    SELECT * FROM sys.dm_cryptographic_provider_properties;

  11. Check that all databases held in common by both Node A and Node U are synchronized. If so, Node U can now be used for active service in the cluster if required.

  12. Repeat all steps above, except step 6, for all remaining nodes that you wish to upgrade.

    Step 6 converts all pkcs11 keys in the security world to nCore simple keys. This only needs to be done once. Therefore, step 6 does not need to be repeated after the first node in a cluster has been upgraded.