Column level encryption

Table Column data can be protected by an Entrust nShield HSM protected key. These nDSOP EKM keys can encrypt/decrypt data in a column.

  1. Create a new key

  2. Import an existing key

  3. Encrypt a column with a symmetric key

  4. Encrypt a column with an asymmetric key

  5. Encrypt a column with the imported asymmetric key

Create a new key

Create a new key within the SQL Server database to encrypt a column. This key will be protected by the Entrust nShield HSM.

  1. Insert the OCS in the HSM slot or TVD. If using Softcard protection, no action is needed.

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

    USE TestDatabase;
CREATE SYMMETRIC KEY "DBSymKey"
FROM PROVIDER "nDSOP"
WITH
PROVIDER_KEY_NAME = 'EKMSymKey', IDENTITY_VALUE = '$DBSymKey',
CREATION_DISPOSITION = CREATE_NEW,
ALGORITHM = AES_256;
GO

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

    USE TestDatabase;
CREATE ASYMMETRIC KEY "DBASymKey"
FROM PROVIDER "nDSOP"
WITH
PROVIDER_KEY_NAME = 'EKMASymKey',
CREATION_DISPOSITION = CREATE_NEW,
ALGORITHM = RSA_2048;
GO

  4. Verify the keys created above.

    column encryption keys created 
    > nfkminfo -l

Keys protected by cardsets:
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-94fa54413d4f9064af7bb47f553d18109c6c9585 `EKMASymKey'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-ecaaf2c3e8cb8f0dd3756678b757468a4de120c4 `AsymTestWrappingKeySQLEKM'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-ecc0d19430a8052bf3a55617a0b13522a917f039 `EKMSymKey'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-f110419800476ccf0bd04b3cd531a59ce3cd2af6 `AsymTestWrappingKeySQLEKM2'

Import an existing key

The Entrust nShield HSM utility generatekey will be used to create a asymmetric key. Then this key will be imported in the SQL Server.

  1. Run the utility generatekey interactive as show below

    The ident: Key identifier? [] must begin with sqlekm-. 
    > generatekey simple
protect: Protected by? (token, softcard, module) [token] >
slot: Slot to read cards from? (0-5) [0] >
recovery: Key recovery? (yes/no) [yes] >
type: Key type? (AES, DES2, DES3, DH, DHEx, DSA, EC, ECDH, ECDSA, HMACSHA1,
                 HMACSHA256, HMACSHA384, HMACSHA512, Rijndael, RSA) [RSA]
>
size: Key size? (bits, minimum 1024) [2048] >
OPTIONAL: pubexp: Public exponent for RSA key (hex)? []
>
ident: Key identifier? [] > sqlekm-EKMExistingASymKey
plainname: Key name? [] > EKMExistingASymKey
nvram: Blob in NVRAM (needs ACS)? (yes/no) [no] >
key generation parameters:
 operation    Operation to perform               generate
 application  Application                        simple
 protect      Protected by                       token
 slot         Slot to read cards from            0
 recovery     Key recovery                       yes
 verify       Verify security of key             yes
 type         Key type                           RSA
 size         Key size                           2048
 pubexp       Public exponent for RSA key (hex)
 ident        Key identifier                     sqlekm-EKMExistingASymKey
 plainname    Key name                           EKMExistingASymKey
 nvram        Blob in NVRAM (needs ACS)          no

Loading cardset(s):
 Module 1 slot 0: `testOCS' #2
 Module 1 slot 2: Admin Card #15
 Module 1 slot 3: empty
 Module 1 slot 4: empty
 Module 1 slot 5: empty
 Module 1 slot 0:- passphrase supplied - reading card
Card reading complete.

Key successfully generated.
Path to key: C:\ProgramData\nCipher\Key Management Data\local\key_simple_sqlekm-ekmexistingasymkey

  2. Notice the newly created key.

    > nfkminfo -l

Keys protected by cardsets:
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-94fa54413d4f9064af7bb47f553d18109c6c9585 `EKMASymKey'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-ecaaf2c3e8cb8f0dd3756678b757468a4de120c4 `AsymTestWrappingKeySQLEKM'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-ecc0d19430a8052bf3a55617a0b13522a917f039 `EKMSymKey'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-f110419800476ccf0bd04b3cd531a59ce3cd2af6 `AsymTestWrappingKeySQLEKM2'
 key_simple_sqlekm-ekmexistingasymkey `EKMExistingASymKey'

  3. Import the newly created key by running the following query.

    USE TestDatabase;
GO
CREATE ASYMMETRIC KEY "DBExistingASymKey"
FROM PROVIDER "nDSOP"
WITH
PROVIDER_KEY_NAME = 'EKMExistingASymKey',
CREATION_DISPOSITION = OPEN_EXISTING;
GO
    column encryption import key

Encrypt a column with a symmetric key

To encrypt a column with a symmetric key:

  1. Consider the table TestTable in database TestDatabase.

    column encryption table 1

  2. Run the following query to create a new Encrypted_Password column containing the encrypted passwords with the symmetric key created above, and populate the Password column with blanks.

    USE TestDatabase;
ALTER TABLE TestTable 
ADD Encrypted_Password VARBINARY (256);
GO
UPDATE TestTable
SET Encrypted_Password = ENCRYPTBYKEY(KEY_GUID('DBSymKey'), Password);
UPDATE TestTable
SET Password = '';
GO

  3. Notice the new Encrypted_Password column containing the encrypted passwords.

    column encryption symmetric encrypted

  4. Run the following query to decrypted the column above.

    USE TestDatabase;
UPDATE TestTable
SET Password = DecryptByKey(Encrypted_Password);
GO

  5. Notice the Password column is now populated with the decrypted password.

    column encryption symmetric decrypted

Encrypt a column with an asymmetric key

To encrypt a column with an asymmetric key:

  1. Run the following query to encrypt the passwords with the asymmetric key created above, and populate the Password column with blanks.

    USE TestDatabase;
UPDATE TestTable
SET Encrypted_Password = ENCRYPTBYASYMKEY(ASYMKEY_ID('DBASymKey'), Password);
UPDATE TestTable
SET Password = '';
GO

  2. Notice the Encrypted_Password column has new values corresponding to the asymmetric key.

    column encryption asymmetric encrypted

  3. Run the following query to decrypted the column above.

    USE TestDatabase;
UPDATE TestTable
SET Password = DECRYPTBYASYMKEY(ASYMKEY_ID('DBASymKey'), Encrypted_Password);
GO

  4. Notice the Password column is now populated with the decrypted password.

    column encryption asymmetric decrypted

Encrypt a column with the imported asymmetric key

To encrypt a column with the imported asymmetric key:

  1. Run the following query to encrypt the passwords with the imported asymmetric, and populate the Password column with blanks.

    USE TestDatabase;
UPDATE TestTable
SET Encrypted_Password = ENCRYPTBYASYMKEY(ASYMKEY_ID('DBExistingASymKey'), Password);
UPDATE TestTable
SET Password = '';
GO

  2. Notice the Encrypted_Password column has new values corresponding to the imported key.

    column encryption imported asymmetric encrypted

  3. Run the following query to decrypted the column above.

    USE TestDatabase;
UPDATE TestTable
SET Password = DECRYPTBYASYMKEY(ASYMKEY_ID('DBExistingASymKey'), Encrypted_Password);
GO

  4. Notice the Password column is now populated with the decrypted password.

    column encryption imported asymmetric decrypted