Thales Database Security Option Pack. for Microsoft SQL Server Integration Guide.

(1)

Thales Database Security Option Pack

for Microsoft SQL Server

®

Integration Guide

(2)

Version: 2.0

Date: 4 May 2012

h

iMar1

2

Copyright in this document is the property of Thales e-Security Limited. It is not to be reproduced, modified, adapted, published, translated in any material form (including storage in any medium by electronic means whether or not transiently or incidentally) in whole or in part nor disclosed to any third party without the prior written permission of Thales e-Security Limited neither shall it be used otherwise than for the purpose for which it is supplied.

Codesafe®, Keysafe®, the nCipher logo, nFast®, nForce®, nShield®, Payshield®, and Ultrasign® are registered trademarks of Thales e-Security Limited and/or nCipher Corporation Limited.

CipherTools™, CryptoStor™, CryptoStor Tape™, keyAuthority™, KeyVault™, nCipher™, nCore™, netHSM™, nFast Ultra™, nForce Ultra™, nShield Connect™, nToken™, SAFEBUILDER™, SEE™, the SEE logo, and TRUST APPLIANCE™ are trademarks of Thales e-Security Limited and/or nCipher Corporation Limited.

Microsoft® and SQL Server® are registered trademarks of Microsoft Corporation in the United States and other countries. All other trademarks are the property of the respective trademark holders.

Information in this document is subject to change without notice.

Thales e-Security Limited makes no warranty of any kind with regard to this information, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. Thales e-Security Limited shall not be liable for errors contained herein or for incidental or consequential damages concerned with the furnishing, performance or use of this material.

Commercial Computer Software - proprietary

This computer software and documentation is Commercial Computer Software and Computer Software Documentation, as defined in sub-paragraphs (a)(1) and (a)(5) of DFAR § 252.227-7014, “Rights in Noncommercial Computer Software and Noncommercial Computer Software Documentation”. Use, duplication or disclosure by the Government is subject to the Thales standard US Terms And Conditions for the Product.

Patents

UK Patent GB9714757.3. Corresponding patents/applications in USA, Canada, South Africa, Japan and International Patent Application PCT/GB98/00142.

(3)

Chapter 1: About this guide

Read this guide if...

You want to install and then configure the Database Security Option Pack for Microsoft SQL Server®, an SQLEKM provider for Microsoft® SQL Server® 2008, SQL Server® 2008 R2 and Microsoft® SQL Server® 2012 Enterprise Edition that offloads encryption, decryption and key storage onto Thales nShield hardware security modules (HSMs). The guide also provides troubleshooting information, and instructions for uninstalling and upgrading the SQLEKM provider.

You will find the installer and all the associated configuration files and executables for the Database Security Option Pack for SQL Server on the supplied installation disc.

This guide assumes that:

• One or more Thales nShield HSMs are already installed.

• Security World for nShield, formerly named nCipher Support Software, V11.11 or higher is already installed with a Security World appropriately configured.

• An SQL Server service is already installed.

• You are familiar with the administration and configuration of Microsoft SQL Server. For more information about:

• Installing a Thales nShield HSM, see the Quick Start Guide or Hardware Installation Guide. • Security World Software, see the appropriate User Guide.

• Microsoft SQL Server, visit the dedicated Microsoft web site at

http://www.microsoft.com/sqlserver/.

(6)

Further Information

• The Database Security Option Pack for SQL Server is also referred to as the SQLEKM provider.

• Microsoft SQL Server 2008, Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2012 Enterprise Edition are referred to as simply Microsoft SQL Server.

Typographical conventions

Note The word Note in the margin indicates important supplemental information.

Onscreen text from graphical user interfaces, and the names of files, command-line utilities, and other system items are presented in boldface text.

Examples of onscreen display are presented in a form similar to the following:

install

Further Information

This guide forms one part of the information and support provided by Thales. You can find additional documentation in the document directory of the installation disc for your product.

Contacting Support

To obtain support for your product, visit http://www.thales-esecurity.com/en/Support.aspx. Before contacting the Support team, click Useful Information and use the subtopics to see the information that the team requires.

(7)

Chapter 2: Overview

This chapter describes how the Database Security Option Pack for SQL Server enables you to manage encryption operations performed on Microsoft SQL Server Enterprise edition databases, using Thales nShield HSMs. It describes the cryptographic architecture which the SQLEKM provider enables, and the benefits of deploying such an architecture. A step-by-step guide to performing encryption operations on SQL Server using the provider is also provided.

The provider supports the two types of encryption that are available through SQL Server: • Transparent Data Encryption (TDE), which is used to encrypt an entire database in a way that

does not require changes to existing queries and applications.

• Cell-level encryption, which uses one or more keys to encrypt individual cells or columns within database tables.

(8)

Cryptographic architecture

Figure 1 Cryptographic architecture

A Microsoft SQL Server service permits the creation of:

• One or more databases. When a client request is made to SQL Server, it determines which of the databases are the subject of the query and may load the databases into memory from disk storage.

• One or more cryptographic keys. You can use these keys to perform encryption either across an entire database (transparent data encryption (TDE) mode) or across a user-specified set of fields within a database table (cell-level encryption mode).

• One or more SQLEKM providers. An SQLEKM provider, such as the Database Security

Option Pack for SQL Server, is used in association with a HSM to perform cryptographic and key management operations.

SQL Server: Database 1 _SQLEKM provider Logic Client HSM Disk storage

Off-site back-up storage:

Disk storage

Disk storage TDE Cell-level encryption

Master database

Card set Softcard ‘abc’ Database 2

(9)

The Database Security Option Pack for SQL Server implements the Extensible Key Management (EKM) API introduced in Microsoft SQL Server, designed to enable the integration of HSMs (such as nShield Connect and nShield Solo) into your existing database infrastructure. A set of smart cards or a softcard is used to control access to the secure environment of the HSM, where the cryptographic keys that are used to encrypt and decrypt your SQL Server databases are created, stored and managed.

The smart cards or softcard protects the encrypted data stored on disk. If the smart cards or softcard (and the corresponding passphrase) are not available to an attacker, the attacker cannot reconstruct the original, unencrypted data from the data stored on the disk.

In addition, if the encrypted data is stored elsewhere, on off-line back-up media, the attacker cannot reconstruct the data without access to a Thales nShield HSM with the same cryptographic key information as the HSM used by the SQL Server instance.

You can configure the SQLEKM provider to perform TDE or cell level encryption. A database encrypted with TDE is automatically decrypted when SQL Server loads it into memory from disk storage, which means that a client can query the database within the server environment without having to perform any decryption operations. The database is encrypted again when saved to disk storage.

Cell level encryption is the ability to apply fine grained access policy to the most sensitive data in a database. Although the client must specify in their query what encrypted data they would like to access, and what cryptographic key to use to decrypt that data, this mode of encryption can minimize data exposure within the database server and client applications. You can apply cell level encryption to database tables that are also encrypted using TDE.

For more information about:

• Configuring the SQLEKM provider to perform encryption operations on SQL Server, see

Configuration on page 16.

• Thales nShield HSMs, see the appropriate User Guide.

Querying encrypted data

When the client sends a query to SQL Server, the SQLEKM provider checks the level of encryption on the database that is the subject of the query.

(10)

Figure 2 Querying encrypted data: process diagram

When SQL Server loads a database encrypted with TDE into memory, the database is automatically decrypted. The form of the query does not have to specify any decryption

operation, and the client does not need to be made aware of the encryption status of the database. However, if there is cell level encryption across the fields of the database the client wants to query (such as a particular column of data in a table), the query must specify:

• The fields to decrypt.

• The cryptographic key that is used for decrypting those fields.

Example queries

The following example queries use a database table of customer information that includes first names, second names and payment card numbers. The queries concern the details of customers whose first names are Joe.

SQL Server query Is database loaded? No Load database into memory Yes Is database encrypted with TDE? Decrypt database No Process query (performing any cell

level encryption operations specified)

Perform query

(11)

Example 1: TDE encryption only

In this example, the entire database is encrypted with TDE.

Figure 3 TDE encryption only

The database is decrypted when it is loaded into memory from disk storage. As this happens before the query is performed the query does not have to specify any decryption operation:

SELECT * FROM Customers WHERE First name LIKE ('*Joe*');

Example 2:

In this example, the database is encrypted with TDE and the column of credit card numbers in the table of customers is protected with cell-level encryption.

Cust

ID First name Second name CardNumber

01 Joe

Joe Smith

Bloggs [16-dig credit card number]

02 03

[16-dig credit card number] [16-dig credit card number]

Iain Hood

Database: TestDatabase

Table: Customers

TD

(12)

Figure 4 TDE and cell level encryption

The query does not have to take account of TDE on the database because the database is decrypted on loading into memory from disk storage before the query is performed. However, the query must specify the decryption of the column of credit card numbers in the table of customers before the details of customers called Joe can be returned.

SELECT [First name], [Second name], CAST(DecryptByKey('CardNumber') AS VARCHAR(MAX)) AS 'Decrypted card number' FROM Customers WHERE [First name] LIKE ('*Joe*');

Cust

01 Joe

Joe Smith

02 03

Iain Hood Database: TestDatabase Table: Customers TD E Cell-level encryption

(13)

Chapter 3: Installation and configuration

This chapter describes how to:

• Install and enable the Database Security Option Pack for SQL Server (also referred to as the SQLEKM provider) on SQL Server.

• Configure the SQLEKM provider for both TDE and cell level encryption on SQL Server databases.

Note In the example T-SQL statements featured in the remaining part of this guide, the names used for cryptographic keys (such as AES256Key) and databases (such as TestDatabase) are example names only. The only exception to this rule is the master database, which is a real database.

The example T-SQL statements are also listed in the examples.txt file, available from the document directory of your product CD-ROM.

Supported platforms and environments

The Database Security Option Pack for SQL Server is fully compatible with V11.11 or higher of the Security World Software and a range of Thales nShield HSMs. You must install the Security World Software and the HSM before you can install and configure the SQLEKM provider. The SQLEKM provider supports the following Thales nShield HSMs:

• nShield Solo 500, 2000, 4000, 500e, 6000e. • netHSM 500, 2000.

• nShield Connect 500, 1500, 6000.

The SQLEKM provider supports the Enterprise Edition of Microsoft SQL Server 2008 (with Service Pack 1 and Cumulative Update 1 patch) and Microsoft SQL Server 2008 R2 on the following platforms:

(14)

Supported platforms and environments

• Windows Server 2008 R2 Enterprise Edition (64-bit configuration).

The integration between the HSM and the SQLEKM provider has been tested for the following combinations:

You can access the following nCipher functionality when you integrate an nCipher HSM with the MS SQL: Windows Server operating system version Microsoft SQL Server version Security World Software version nShield Solo support netHSM support nShield Connect support 2008 R2 SP2 64-bit 2012 11.50 Yes — Yes 2008 R2 64-bit 2008 R2 11.50 — — Yes 2008 64-bit 2008 R2 11.50 — — Yes 2008 32-bit 2008 R2 11.50 — Yes — 2003 R2 32-bit 2008 R2 11.50 Yes — — 2003 64-bit 2008 R2 11.50 Yes — — 2003 32-bit 2008 11.50 Yes — — 2008 R2 64-bit 2008 R2 11.40 — — Yes 2008 R2 64-bit 2008 11.40 Yes — — 2008 64-bit 2008 R2 11.40 Yes — — 2008 32-bit 2008 R2 11.40 — Yes — 2003 R2 32-bit 2008 R2 11.40 — — Yes 2003 64-bit 2008 R2 11.40 Yes — —

2003 32-bit 2008 11.40 Yes — Yes

2008 64-bit 2008 11.30 Yes — —

2008 32-bit 2008 11.30 — — Yes

2003 64-bit 2008 11.30 — Yes —

2003 32-bit 2008 11.30 Yes — —

Soft cards Yes Key Management Yes

Strict FIPS support Yes Key Recovery Yes

Module Only Key — K of N Card Set Yes

(15)

Installation

To install the Database Security Option Pack for SQL Server on Microsoft SQL Server:

1 Ensure that %NFAST_HOME%\toolkits\pkcs11 is added to the PATH environment variable. 2 For a cluster configuration, you must:

a Copy the local subdirectory (of the Key Management Data directory) to a network share and set NFAST_KMLOCAL as a system environment variable to reference this location. b Install the Security World Software on the additional nodes, setting NFAST_KMLOCAL

as a system environment variable to point to the local subdirectory on the network share. 3 Place the SQLEKM provider installation disc in the optical disc drive. Launch setup.exe

manually if the installer does not run automatically.

Note If you are installing a version of the Security World Software other than V11.11 or V11.30, a message is displayed stating that those versions of software are not installed and asking if you wish to continue. It is safe to click Yes and continue with the installation.

The Welcome screen of the InstallShield wizard is displayed. 4 Click Next.

5 To accept the license agreement, click Yes.

Note You also have the option to print the license agreement.

6 A setup status screen is displayed, showing the progress of the installation. When the setup files finish installing, you are asked if you want to restart the machine now or later. You must restart the machine in order to use the SQLEKM provider.

Click Finish while Yes, I want to restart my computer now is selected to restart the machine and complete setup.

7 In order for Thales SQLEKM to support multithreaded access to Microsoft SQL Server, a hotfix is required. Please contact Thales support to obtain this hotfix.

(16)

Configuration

The following procedure and sub-procedures assume that SQL Server Management Studio is open and that you are connected to the SQL Server instance. Open a query window to execute a query.

Enabling the SQLEKM provider

To enable the SQLEKM provider on SQL server for both TDE and cell level encryption: 1 Add the following line to the cknfastrc file (in NFAST_HOME):

CKNFAST_LOADSHARING=1

Note For a cluster configuration, you must add this line to the cknfastrc file on all nodes within the cluster.

2 Enable support for SQLEKM providers within SQL Server by executing the following query:

sp_configure 'show advanced options', 1; RECONFIGURE;

GO

sp_configure 'EKM provider enabled', 1; RECONFIGURE;

(17)

Configuration

3 Register the SQLEKM provider with the SQL Server 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 in subsequent T-SQL statements.

- <Path to provider> is the fully qualified path to the ncsqlekm.dll file in the installation directory.

For example:

CREATE CRYPTOGRAPHIC PROVIDER nCipher_Provider FROM FILE = 'C:\Program Files (x86)\nCipher\nfast\bin\ncsqlekm.dll'; GO

Note The SQLEKM provider installation wizard copies a 32-bit DLL into ncsqlekm.dll on 32-bit systems, and a 64-bit DLL into ncsqlekm.dll on 64-bit systems. The

alternative bit length version is named either ncsqlekm32.dll or ncsqlekm64.dll, depending on the installation platform.

4 To check that the SQLEKM provider is listed:

a Open Object Explorer on the Management Studio. b Go to Security > Cryptographic Providers.

Creating a credential

A credential represents the smart card set (or softcard) and passphrase that is used to access the keys protected by the SQLEKM provider. You must map a credential from an SQL login to the SQLEKM provider before you can use the SQLEKM provider.

To create a credential and map that credential to the logged-in user (in this case, the user is

TestLogin):

(18)

Configuration

4 Set Identity to <OCS>, where <OCS> matches the name of the Operator Card Set (OCS) or softcard.

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

6 Set Use Encryption Provider to <Name of provider>, where <Name of provider> is the name that is used to refer to the SQLEKM provider. Click OK.

7 In Object Explorer, navigate to Security > Logins.

8 Right click to select the current logged in user, then select Properties.

9 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 on SQL server for both TDE and cell level encryption:

1 Check that the SQLEKM provider was registered correctly by executing 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 and the .dll path are correct. - The is_enabled column is set to 1.

2 Check the SQLEKM provider properties by executing 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:

- The version of the SQLEKM provider is correct for the build. - The friendly_name is nCipher SQLEKM Provider.

(19)

Disaster recovery

3 To check that the supported cryptographic algorithms can be queried, execute 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

Note If a strict FIPS Security World is used DES key type support is removed.

A table is displayed with the supported cryptographic algorithms. For more information about the algorithms that should be displayed, see Supported cryptographic algorithms on page 20.

Disaster recovery

The Database Security Option Pack for Microsoft SQL Server builds on the Security World abstraction that is provided by the Security World Software. Correct management of the Security World data files and card sets is necessary to implement a disaster recovery procedure and to prevent irretrievable data loss. For more information about Security Worlds, see your HSM User Guide.

The local subdirectory of the Key Management Data directory (referenced by the

NFAST_KMDATA environment variable) contains the world, keys, cards, and softcard data, and must be backed up regularly. If set, the NFAST_KMLOCAL environment variable references an alternative location for this data, which should be backed up instead.

You must perform regular database backups according to your corporate disaster recovery policy. A Security World card set consists of a group of N cards. An equal or smaller number of cards within this set, the quorum K, is required to authorize an action. Operator Card Sets used with SQL Server should have a quorum of 1 of N. For disaster recovery, a sufficient N must be chosen to allow for failure or loss of individual smart cards. You must retain some of these cards in a secure (off-site) location.

The Administrator Card Set (ACS) is not used directly by the Database Security Option Pack for Microsoft SQL Server. This card set is used to manage the Security World and other card sets. You must always keep the ACS in a secure (off-site) location.

(20)

Encryption

When you have completed the configuration of the SQLEKM provider, you can use the SQLEKM provider to:

• Manage cryptographic keys within the Thales nShield HSM.

• Encrypt or decrypt entire databases or fields within database tables within your SQL Server service.

Supported cryptographic algorithms

The algorithms that you can use for encryption can depend on whether or not the Thales nShield HSM operates at a strict or a non-strict level of compliance with the FIPS 140-2 Level 3 security standard.

For more information about cryptographic algorithms and FIPS 140-2 Level 3, see the appropriate User Guide.

The following table lists cryptographic algorithms that you can use with symmetric keys.

The following table lists cryptographic algorithms that you can use with asymmetric cryptographic keys.

Note DES and RSA_512 keys are not recommended for use with Thales nShield products. You must modify the PKCS #11 library configuration file to use these keys. For more information, contact Support.

Algorithm Non-strict FIPS 140-2 Level 3 Strict FIPS 140-2 Level 3

DES Yes No

Triple_DES Yes Yes

Triple_DES_3KEY Yes Yes

AES_128 Yes Yes

AES_192 Yes Yes

AES_256 Yes Yes

Algorithm Non-strict FIPS 140-2 Level 3 Strict FIPS 140-2 Level 3

RSA_512 Yes Yes

RSA_1024 Yes Yes

(21)

Encryption

Creating and managing symmetric keys

The following procedures assume that SQL Server Management Studio is open and that you are connected to the SQL Server instance. Open a query window to execute a query.

Note If you are using a card set (OCS), ensure that your card set is inserted in the card reader before attempting to create and manage symmetric keys.

To create a symmetric key (AES256Key) using the SQLEKM provider, execute the following query:

USE TestDatabase

CREATE SYMMETRIC KEY AES256Key

FROM PROVIDER <Name of provider> WITH PROVIDER_KEY_NAME=’AES256Key’, CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=AES_256;

GO

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

To remove a symmetric key (AES256Key, created in the above procedure) from the database only (TestDatabase), execute the following query:

USE TestDatabase

DROP SYMMETRIC KEY AES256Key; GO

To remove a symmetric key (AES256Key) from the database (TestDatabase) and the Thales nShield HSM, execute the following query:

USE TestDatabase

DROP SYMMETRIC KEY AES256Key REMOVE PROVIDER KEY; GO

Note Refer to your security policies before considering deleting a provider key on the HSM.

To import a symmetric key (AES256Key) into the database (TestDatabase), execute the following query:

USE TestDatabase

CREATE SYMMETRIC KEY AES256Key FROM PROVIDER <Name of provider> WITH PROVIDER_KEY_NAME='AES256Key', CREATION_DISPOSITION = OPEN_EXISTING;

(22)

Encryption

Note If you have removed a symmetric key from the database, you can import it into the database again using the above procedure. However, you cannot import a key into the database once you have deleted that key from the database and the HSM.

To find out what symmetric keys have been created, complete the following steps: 1 Open Object Explorer on the Management Studio.

2 Go to Databases > TestDatabase > Security > Symmetric Keys.

Creating and managing asymmetric keys

The following procedures assume that SQL Server Management Studio is open and that you are connected to the SQL Server instance. Open a query window to execute a query.

To create an asymmetric key (RSA2048Key) using the SQLEKM provider, execute the following query:

USE TestDatabase

CREATE ASYMMETRIC KEY RSA2048Key FROM PROVIDER <Name of provider>

WITH PROVIDER_KEY_NAME=’RSA2048Key’, CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048; GO

To remove an asymmetric key (RSA2048Key, created in the above procedure) from the database only (TestDatabase), execute the following query:

USE TestDatabase

DROP ASYMMETRIC KEY RSA2048Key; GO

To remove an asymmetric key (RSA2048Key) from the database (TestDatabase) and the Thales nShield HSM, execute the following query:

USE TestDatabase

DROP ASYMMETRIC KEY RSA2048Key REMOVE PROVIDER KEY; GO

(23)

Encryption

To import an asymmetric key (RSA2048Key) into the database (TestDatabase), execute the following query:

USE TestDatabase

CREATE ASYMMETRIC KEY RSA2048Key

FROM PROVIDER <Name of provider> WITH PROVIDER_KEY_NAME='RSA2048Key', CREATION_DISPOSITION = OPEN_EXISTING; GO

Note If you have removed an asymmetric key from the database, you can import it into the database again using the above procedure. However, you cannot import a key into the database once you have deleted that key from the database and the HSM.

To find out what asymmetric keys have been created, complete the following steps: 1 Open Object Explorer on the Management Studio.

2 Go to Databases > TestDatabase > Security > Asymmetric Keys.

Creating a symmetric wrapped key from an asymmetric wrapping key

The following procedure assumes that SQL Server Management Studio is open and that you are connected to the SQL Server instance. Open a query window to execute a query.

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

USE TestDatabase

CREATE ASYMMETRIC KEY WrappingKey FROM PROVIDER <Name of provider>

WITH PROVIDER_KEY_NAME='RSA2048Key', CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048; GO

CREATE SYMMETRIC KEY WrappedKey

WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY WrappingKey; GO

TDE

(24)

Encryption

To create a TDE Key Encryption Key (TDEKEK) (WrappingKey) for database encryption, execute the following query:

USE master

CREATE ASYMMETRIC KEY WrappingKey FROM PROVIDER <Name of provider>

WITH PROVIDER_KEY_NAME='WrappingKey', CREATION_DISPOSITION = CREATE_NEW, ALGORITHM = RSA_2048; GO

Note The TDEKEK is the only key you must create in the master database. To find the TDEKEK, navigate to Databases > System Databases > master > Security >

Asymmetric Keys.

To set up the TDE login and credential:

1 In Object Explorer, navigate to Security > Credentials. 2 Right click Credentials, then select New Credential. 3 Set Credential name to tdeCredential.

4 Set Identity to <OCS>, where <OCS> matches the name of the Operator Card Set (OCS) or softcard.

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

6 Set Use Encryption Provider to <Name of provider>, where <Name of provider> is the name that is used to refer to the SQLEKM provider. Click OK.

7 In Object Explorer, navigate to Security > Logins. 8 Right click Logins, then select New Login.

9 Set Login name to tde_login.

10 Ensure Mapped to asymmetric key is selected, then select WrappingKey (the TDEKEK created in the previous procedure) from the drop down list.

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

(25)

Encryption

Figure 5 Creating the TDEDEK

To create the TDE Database Encryption Key (TDEDEK) (using the TDEKEK created above (WrappingKey)) for database encryption, and enable TDE on the database (TestDatabase): 1 In Object Explorer, 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 WrappingKey from the drop down list.

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

Note DES and TRIPLE_DES mechanisms are not supported by SQL Server 2008 for TDEDEKs.

(26)

Encryption

Figure 6 Checking that TDE has occurred

To check that TDE has occurred successfully on the database (TestDatabase): 1 In Object Explorer, navigate to Databases > TestDatabase.

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

3 Select the Properties page. Ensure that EncryptionState is set to Encrypted, then click OK. To replace the TDEKEK (from WrappingKey to a new key):

1 Following the procedure above for WrappingKey, create a new asymmetric TDEKEK

AnotherWrappingKey.

2 Following the procedure above for tde_login, create a new TDE login another_tde_login

mapped to AnotherWrappingKey and mapped to a new anotherTdeCredential credential. 3 In Object Explorer, navigate to Databases > TestDatabase.

(27)

Encryption

Figure 7 Replacing the TDEKEK

5 Ensure Re-Encrypt Database Encryption Key and Use server asymmetric key are selected. Select AnotherWrappingKey from the drop down list.

6 Ensure Regenerate Database Encryption Key is not selected. 7 Ensure Set Database Encryption On is selected, then click OK. To replace the TDEDEK:

1 In Object Explorer, navigate to Databases > TestDatabase.

(28)

Encryption

Figure 8 Replacing the TDEDEK

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.

Cell-level encryption

The following procedures assume that SQL Server Management Studio is open and that you are connected to the SQL Server. Open a query window to execute a query.

An example database table, called Customers, is referenced in the procedures. The database that contains the table is called TestDatabase. The table comprises:

• A first column called FirstName that contains the first name of payment card holders. • A second column called SecondName that contains the second name of payment card holders. • A third column called CardNumber that contains the payment card numbers of card holders. The encryption and decryption operations described in the procedures below are performed on

(29)

Encryption

Figure 9 Example table: Customers

Creating a table with cells of encrypted data

To create a database table with data (Customers), where individual cells of data held in the third column (CardNumber) are encrypted with a symmetric key (SymmetricKey), execute the following query:

USE TestDatabase GO

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

INSERT INTO Customers (FirstName, SecondName, CardNumber)

VALUES ('Joe', 'Bloggs', ENCRYPTBYKEY(KEY_GUID('SymmetricKey'), '16-Dig Card Number'));

Where 16-Dig Card Number is the 16-digit payment card number to be encrypted.

Note To prevent corruption or truncation of the stored encrypted data, the column or field where the encrypted data is stored must be of the type 'varbinary(MAX)'.

If an asymmetric key (AsymmetricKey) is used, the INSERT INTO statement has the following form:

Cust

01 Joe

Joe Smith

02 03

Iain Hood

Database: TestDatabase

(30)

Encryption

Where 16-Dig Card Number is the 16-digit payment card number to be encrypted.

Listing cell-encrypted data in a table

To list all the data in a database table (Customers, created in the previous section), showing just the plain text names and encrypted payment card numbers:

1 In Object Explorer, navigate to Databases > TestDatabase > Tables > dbo.Customers. 2 Right click dbo.Customers, then select Select Top 1000 Rows.

To list the first names (FirstName), second names (SecondName) and the decrypted payment card numbers (which were encrypted with a symmetric key) in an existing table (Customers), execute the following query:

USE TestDatabase GO

SELECT FirstName, SecondName, CONVERT(varchar, DECRYPTBYKEY(CardNumber)) AS 'CardNumber' FROM Customers;

GO

If an asymmetric key (AsymmetricKey) was used to encrypt the payment card numbers, the statement has the following form:

USE TestDatabase GO

SELECT FirstName, SecondName, CONVERT(varchar, DECRYPTBYASYMKEY(ASYMKEY_ID('AsymmetricKey'), CardNumber)) AS 'CardNumber' FROM Customers;

GO

Note DECRYPTBYASYMKEY in the asymmetric key decryption operation requires the name of the key (AsymmetricKey), whereas DECRYPTBYKEY in the symmetric key decryption operation does not.

Encrypting and decrypting columns of data

As well as performing encryption operations on data within individual table cells, you can also choose to encrypt and decrypt entire columns of data.

To encrypt a column of data (CardNumber) in an existing table (Customers) with a symmetric key (SymmetricKey), execute the following query:

(31)

Encryption

If an asymmetric key (AsymmetricKey) is used to encrypt a column of data (CardNumber), the statement has the following form:

USE TestDatabase

UPDATE Customers SET CardNumber = ENCRYPTBYASYMKEY(AsymKey_ID('AsymmetricKey'), CardNumber); GO

To decrypt a column of data (CardNumber) encrypted by a symmetric key in an existing table (Customers), execute the following query:

USE TestDatabase

UPDATE Customers SET CardNumber = DECRYPTBYKEY(CardNumber); GO

If a column of data (CardNumber) encrypted by an asymmetric key (AsymmetricKey) is being decrypted, the statement has the following form:

USE TestDatabase

UPDATE Customers SET CardNumber = DECRYPTBYASYMKEY(AsymKey_ID(‘AsymmetricKey’), CardNumber); GO

Note DECRYPTBYASYMKEY in the asymmetric key decryption operation requires the name of the key (AsymmetricKey), whereas DECRYPTBYKEY in the symmetric key decryption operation does not.

Viewing tables in Object Explorer

To check that data in a table (Customers) was either encrypted or decrypted successfully, complete the following steps:

1 Open Object Explorer on the Management Studio. 2 Go to Databases > TestDatabase > Tables.

3 Right click dbo.Customers and select Select Top 1000 Rows to view the encrypted or decrypted data.

(32)

Troubleshooting

Issue Diagnosis

An error message is outputted in Microsoft SQL Server Management Studio that states that a session could not be opened for the SQLEKM provider.

There is either no smart card in the card reader, or an incorrect smart card in the card reader. Alternatively, the wrong OCS name or

passphrase has been entered into the credentials.

An error message is outputted in Microsoft SQL Server Management Studio that states that a DES key could not be created.

The DES key cannot be created because the Thales nShield HSM is operating at a strict level of compliance with the FIPS 140-2 Level 3 security standard.

DES keys can only be created where the Thales nShield HSM is operating at a non-strict level of compliance.

An error message is outputted in Microsoft SQL Server Management Studio that states that the key type property of the key returned by the SQLEKM provider does not match the expected value.

An attempt was made to create an asymmetric or a symmetric key with an unsupported algorithm.

(33)

Chapter 4: Uninstalling and Upgrading

The following procedures assume that SQL Server Management Studio is open and that you are connected to SQL Server.

Turning off TDE and removing TDE setup

Remember to turn off TDE on all your databases and remove TDE setup before you uninstall the Database Security Option Pack for SQL Server. If you do not do so before uninstalling the SQLEKM provider, you will not be able to decrypt any databases encrypted with TDE.

Figure 10 Turning off TDE

To turn off TDE on a database (TestDatabase):

1 In Object Explorer, navigate to Databases > TestDatabase.

(34)

Turning off TDE and removing TDE setup

Figure 11 Checking that decryption is complete

To check that the database (TestDatabase) decrypted successfully, following the above procedure: 1 In Object Explorer, navigate to Databases > TestDatabase.

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

3 Select the Properties page. Ensure that EncryptionState is set to Unencrypted, then click OK. Provided that the database (TestDatabase) was successfully decrypted, remove the TDE Database Encryption Key (TDEDEK) that was created in the database by executing the following query:

USE TestDatabase

DROP DATABASE ENCRYPTION KEY; GO

(35)

Turning off TDE and removing TDE setup

Figure 12 Logins screen

To remove the tdeCredential from tde_login:

1 In Object Explorer, navigate to Security > Logins and open the properties of tde_login. 2 Select tdeCredential then click Remove.

3 Click OK.

To delete the tde_login:

1 In Object Explorer, navigate to Security > Logins. 2 Delete the tde_login.

(36)

Uninstalling the Database Security Option Pack for SQL Server

Figure 13 Credentials screen

To delete the tdeCredential:

1 In Object Explorer, navigate to Security > Credentials. 2 Delete the tdeCredential.

To remove the TDE Key Encryption Key (TDEKEK), execute the following query:

USE master

DROP ASYMMETRIC KEY WrappingKey REMOVE PROVIDER KEY; GO

Uninstalling the Database Security Option Pack for SQL

Server

Note Depending on your version of Windows, you may be asked for your permission to continue with an action. If asked, click Continue.

(37)

Uninstalling the Database Security Option Pack for SQL Server

To uninstall the Database Security Option Pack for SQL Server from Microsoft SQL Server: 1 Remove the loginCredential from the logged-in user:

a In Object Explorer, navigate to Security > Logins and open up the properties of the logged-in user.

b Select the loginCredential then click Remove, then OK. 2 Navigate to Security > Credentials and delete the loginCredential. 3 Disable and remove the SQLEKM provider:

a Navigate to Security > Cryptographic Providers.

b Right click to select the SQLEKM provider and click Disable Provider.

c A dialog is displayed which shows that this action was successful. Click Close. d Right click to select the disabled SQLEKM provider then click Delete, then OK.

4 Navigate to 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 Navigate to Start > Control Panel > Add/Remove programs (or Uninstall program, depending on your version of Windows). Select Database Security Option Pack for SQL Server then click Uninstall.

6 A dialog is displayed that asks if you want to continue with uninstalling the Database Security Option Pack for SQL Server. Click Yes.

7 A setup status screen is displayed while the Database Security Option Pack for SQL Server is uninstalled. When InstallShield has finished uninstalling the program, click Finish to complete the removal of the program from your system.

(38)

Upgrading

Enhancements will be made to the Database Security Option Pack for SQL Server over time, and product upgrades made available to customers. When the time comes to upgrade your product: 1 In Object explorer, navigate to 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.

2 Uninstall the existing Database Security Option Pack for SQL Server, using the procedure described in Uninstalling the Database Security Option Pack for SQL Server on page 36. 3 Install the upgraded version of the Database Security Option Pack for SQL Server, using the

procedure described in Installation on page 15.

Note You must install the upgraded SQLEKM provider to the same directory as the previous installation. This ensures that the replacement provider files are found automatically when the Microsoft SQL Server instances are started.

(39)

Internet addresses

Americas

2200 North Commerce Parkway, Suite 200, Weston, Florida 33326, USA Tel: +1 888 744 4976 or + 1 954 888 6200

[email protected]

Europe, Middle East, Africa

Meadow View House, Long Crendon, Aylesbury, Buckinghamshire HP18 9EQ, UK Tel: + 44 (0)1844 201800

[email protected]

Asia Pacific

Units 4101, 41/F. 248 Queen’s Road East, Wanchai, Hong Kong, PRC Tel: + 852 2815 8633

[email protected]

Web site: www.thales-esecurity.com

Support: www.thales-esecurity.com/en/Support.aspx Online documentation: www.thales-esecurity.com/Resources.aspx

International sales offices: www.thales-esecurity.com/en/Company/Contact%20Us.aspx

Thales Database Security Option Pack. for Microsoft SQL Server Integration Guide.