Chapter 1: Introduction

SQL Server UDF

The AKM Client Library for Windows will install an additional SQL Server UDF assembly, the TownsendSecurity.EncryptDecryptUdf.dll, along with the Key Client assembly (TownsendSecurity.KeyClient.dll). The SQL Server UDF assembly contains pre-built user-defined functions which you can use to immediately perform key retrieval or remote encryption on the AKM server. Secure key caching is included as a component of key retrieval. Sample code for local encryption demonstrates key retrieval and encryption using Microsoft crypto libraries. Sample code for remote encryption demonstrates encryption and decryption on the AKM server. This guide contains additional information on using views and triggers to automatically encrypt a table.

Who is this for?

This guide is designed for users of SQL Server (all editions) who want to use SQL Server UDFs for key retrieval or remote encryption. It is a companion guide to the AKM Guide for Windows .NET Developers. The examples presented in this document assume that you have installed the AKM Client Library for Windows into your environment and imported certificates and private keys needed for server/client authentication to the Windows certificate store. See the AKM Guide for Windows .NET Developers for more information.

NOTE: If you have SQL Server Enterprise edition or higher, you can use AKM Key Connection for SQL Server, a ready to use client application which utilizes EKM and TDE (Transparent Data Encryption) to provide automatic key retrieval without any changes to your application. See the AKM Key Connection for SQL Server User Guide for more information.

Other resources

The following documents provide additional information on the installation and use of Alliance Key Manager:

Notices

This product and documentation is covered by U.S. and International copyright law. This product may incorporate software licensed under one or more open source license agreements. Government users please note that this product is provided under restricted government use license controls. Please refer to the Alliance Key Manager End User License Agreement for more information.

Change log

The following table provides information on the changes to this documentation:

Version Date Description
2.1.13.001 10/14/2012 Initial documentation release.
2.1.13.002 3/14/2014 Manual format updates.
3.0.0.001 10/9/2014 Update for the AKM Client Library for Windows version 1.1. Name change from “AKM SQL Server Triggers” to “AKM SQL Server UDF Guide”.
4.6.1.001 11/8/2019 Updated links and references to technical information.

Chapter 2: Before You Begin

AKM Guide for Windows .NET Developers

The AKM SQL Server UDF Guide is designed as a companion to the AKM Guide for Windows .NET Developers. Before continuing, review the following chapters in that guide:

  • Chapter 2: Introduction: Review introductory concepts, including the use of certificates and private keys, configuration of the client application, encryption key names vs. key instances, key retrieval and caching, and remote encryption.

  • Chapter 3: Before You Begin: Take steps to prepare for installation, including obtaining a license, locating the required AKM certificates and private keys, installing and setting up the AKM server, and creating encryption keys.

  • Chapter 4: Install the AKM Client Library: Install the AKM Client Library for Windows and review the installed components.

  • Chapter 5: Install Certificates: Import certificates and private keys needed for server/client authentication to the Windows certificate store.

  • Chapter 8: Key Caching: Learn about key caching.

  • Chapter 10: Problem Determination: Learn about problem determination.

Client applications and SDKs

Townsend Security provides the following applications and SDKs to assist with client-side key retrieval or remote encryption:

  • Key Connection for SQL Server: Microsoft Extensible Key Management Provider for Transparent Data Encryption (TDE) and cell level encryption
  • Windows SDK for .NET applications
  • SQL Server UDF for all editions of SQL Server
  • Key Connection for Drupal
  • Key Connection for Encryptionizer

In addition to these offerings, Townsend Security provides software libraries and code samples to assist with custom implementations. Visit this page https://info.townsendsecurity.com/alliance-key-manager-evaluation for a current list of client applications, software libraries, and code samples.

Other resources

The following documents provide additional information on the installation and use of Alliance Key Manager:

Chapter 3: SQL Server UDF

Overview

The SQL Server UDF performs AES CBC encryption with random IV (initialization vector) for SQL Server CLR. It includes functionality for both key retrieval and remote encryption. Key caching is included as a function of key retrieval.

NOTE: For information on how the SQL Server UDF uses the native .NET library System.Security.Cryptography to perform cryptographic operations, review the sample code for the SQL Server UDF.

Before using the SQL Server UDF assembly, you will need to enable CLR integration and register the Townsend Security assemblies and SQL Server UDFs. For more information, see the following websites:

See below for a detailed guide to enabling CLR integration and registering the Townsend Security assemblies.

Sample code

The source code for the SQL Server UDF is included as sample code with the AKM Client Library installation in the Sample Code\SQL Server UDF directory, and you may modify it to suit your needs. See the file Disclaimer.txt in the Sample Code directory for information on use of the sample code.

The sample code demonstrates both local encryption (using Microsoft crypto libraries) and remote encryption on the AKM Server. The key name, key instance, and IV are packed in with the output ciphertext returned by the encrypt functions.

Enable CLR integration

If you have not yet enabled CLR integration in SQL Server, you will need to do so before continuing. You will then need to register the Townsend Security assemblies.

These commands enable CLR integration:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

You can run sp_configure in a specific database or in the master database as per your preference.

NOTE: You may want to run sp_configure in the master database if you are setting up AKM for multiple user databases. However, the master database is a system table and is often locked for non-admin users, so running application specific items in the master database may not work as well as using a specific database.

Register Townsend Security assemblies

These commands register both the TownsendSecurity.EncryptDecryptUdf.dll assembly and the TownsendSecurity.KeyClient.dll assembly, which is also needed.

First, an asymmetric key must be created in the master database (this is the only step that must be done in the master database). This key is used with a SQL Server login that will be created to give the Townsend Security assemblies permission to connect to the AKM server.

use master

CREATE ASYMMETRIC KEY townsend_login_key FROM EXECUTABLE FILE = 'C:\Program Files\Townsend Security\AKM Client Library\bin\TownsendSecurity.KeyClient.dll'

CREATE LOGIN townsend_login FROM ASYMMETRIC KEY townsend_login_key  

GRANT UNSAFE ASSEMBLY TO townsend_login

You can supply any name for the asymmetric key and login. These names are used internally by SQL Server and .NET; once created, you will not need to reference the key and login names in code or configuration.

Now you will register the assemblies in the application’s database (any database that will use the AKM SQL Server UDFs):

use my_database; 

create assembly keyclient
from 'C:\Program Files\Townsend Security\AKM Client Library\bin\TownsendSecurity.KeyClient.dll'
with permission_set = unsafe

create assembly encryptdecrypt
from 'C:\Program Files\Townsend Security\AKM Client Library\bin\TownsendSecurity.EncryptDecryptUdf.dll'
with permission_set = unsafe

Register the user-defined functions

Once the assemblies are registered, you then register the user-defined functions that are part of the Townsend Security SQL Server UDF assembly. You can register these functions with any database that will use the AKM SQL Server UDFs.

NOTE: The name in the CREATE FUNCTION clause (for example, LocalEncrypt below) is arbitrary and can be specified by the user. However, the EXTERNAL NAME clause must match the actual names from the assembly (as shown). It is also best practice to use the dbo prefix for function names.

These functions perform local encryption and decryption:

CREATE FUNCTION dbo.LocalEncrypt
(@keyName NVARCHAR (4000), @plaintext VARBINARY (MAX))
RETURNS VARBINARY (MAX)
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Local].Encrypt
go

CREATE FUNCTION dbo.LocalDecrypt
(@packedCiphertext VARBINARY (MAX))
RETURNS VARBINARY (MAX)
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Local].Decrypt
go

These functions roll the key-caching key, clear the key cache, set the key cache timespan, and look up key-caching key filenames for the current user:

CREATE FUNCTION dbo.RollKeyCachingKey()
RETURNS INT
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Local].RollKeyCachingKey
go

CREATE FUNCTION dbo.ClearKeyCache()
RETURNS INT
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Local].ClearKeyCache
go

CREATE FUNCTION dbo.SetKeyCacheTimespan(@seconds INT)
RETURNS INT
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Local].SetKeyCacheTimespan
go

These functions perform remote encryption and decryption:

CREATE FUNCTION dbo.RemoteEncrypt
(@keyName NVARCHAR (4000), @plaintext VARBINARY (MAX))
RETURNS VARBINARY (MAX)
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Remote].Encrypt
go

CREATE FUNCTION dbo.RemoteDecrypt
(@packedCiphertext VARBINARY (MAX))
RETURNS VARBINARY (MAX)
AS
 EXTERNAL NAME encryptdecrypt.[TownsendSecurity.EncryptDecryptUdf.Remote].Decrypt
go

Client configuration

To use the user-defined functions included in the TownsendSecurity.EncryptDecryptUdf.dll assembly, you must first supply some configuration values in your application, which you provide in a .NET application configuration file for the SQL Server executable. You can also set the client configuration directly in the source code (see below).

You will need to provide the following information in the configuration:

  • IP addresses of the primary AKM server and any additional secondary servers

  • Ports on which those AKM servers are configured to perform key retrieval (default: 6000) or remote encryption (default: 6003)

  • Thumbprint of the client certificate

  • Thumbprint of AKM’s root CA certificate

Configure the client in the application configuration file

The configuration file is an XML file that you put into the Binn directory (that is “Binn” with two ‘n’ characters) where sqlservr.exe resides, for example:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\
MSSQL\Binn

The configuration file must be named sqlservr.exe.config (note the spelling of “servr” in the configuration file name). MSSQL10 refers to the version of SQL Server you are using, and may be different. This file may not yet exist. Note that whenever you create or edit this file, you need to restart the SQL Server instance for the changes to take effect (this is .NET standard behavior for application configuration files.) Administrative rights are required to edit or create a file in the default MSSQLSERVER directory under Program Files.

The configuration is as documented for the AKM Client Library for Windows. Here is an example:

<?xml version="1.0"?>
<configuration>
  	<configSections>
	<!--This section name is used by the EncryptDecryptUdf DLL.-->
	<section name="EncryptDecryptUdf" type="System.Configuration.NameValueSectionHandler" requirePermission="false"/>
  	</configSections>
  	<EncryptDecryptUdf>
	<!--These key-value pairs are used to configure the KeyService or EncryptionService object properties.-->
	<add key="ClientCertificateThumbprint" value="e8 8b 7e 7a 33 7f d9 8e c2 cc 60 26 39 f9 79 59 83 bd 64 fd"/>
    	<add key="ServerCertificateThumbprint" value="ee fd 06 98 cc ee 2c 0e af d3 42 f3 6d 8a c5 46 46 f0 03 f8"/>
    	<add key="Server" value="192.168.1.80,192.168.1.81"/>
    	<add key="EncryptionPort" value="6003"/>
    	<add key="KeyRetrievalPort" value="6000"/>
    	<add key="CheckCertificateRevocation" value="false"/>
    	<add key="AllowCertificateNameMismatch" value="true"/>
    	<add key="KeyCacheTimeSpan" value="00:00:00"/>
    	</EncryptDecryptUdf>
	</configuration>

Supply a section name for this configuration (Example: EncryptDecryptUDF).

IMPORTANT: If a sqlservr.exe.config file already exists, care must be taken in the placement of the new configuration section within the file. The section must be added as the first element under the [configuration] element.

Supply the client certificate thumbprint for the ClientCertificateThumbprint value.

Supply the CA certificate thumbprint for the ServerCertificateThumprint value.

Supply the IP address or DNS name of your AKM server(s). Multiple server values are separated by commas, where the first listed is the primary server, and the second and so on are failover servers which are tried if the first cannot be reached on the network. All listed servers share the ServerCertificateThumbprint, etc., settings.

NOTE: If there is a failure to connect to the AKM server, an attempt is made to connect to the next AKM server in the array, and so on to the end of the array. (However, only a connection failure invokes this behavior. A request failure, such as a key-not-found error, for example, will not cause the next server configuration to be tried.)

AllowCertificateNameMismatch defaults to false. If you are using AKM-generated certificates, be sure to set this to true as these certificates do not contain the server DNS name.

If key caching is needed, set the KeyCacheTimeSpan. Setting it to 00:00:00 or not specifying it will not enable key caching. See the AKM Guide for Windows .NET Developers for more information on key caching.

Configuring the client in application configuration file has the following limitations when using this SQL Server UDF without modification:

  • You cannot configure multiple key clients

  • The encryption port cannot be specified in the app.config file, and is the default value of 6003 unless you change it programmatically after instantiating the EncryptionService object

If you would like to configure multiple key clients or specify a different encryption port, you can configure the client in code (see below).

Configure the client in code

You can configure the connection in a ClientConfiguration object if you modify the SQL Server UDF source code to build your own version of the UDF.

The ClientConfiguration object has an array of ServerConfiguration objects. This array can have just one server configuration, or multiple, and works just like the list above: the first in the array is the primary, and any others are failover.

Here is an example of setting the configuration directly in the code:

var serverConfiguration = new ServerConfiguration()
  {
    AllowCertificateNameMismatch = true,
    EncryptionPort = 6003,
    KeyRetrievalPort = 6000,
    Hostname = "192.168.1.80",
    ServerCertificateThumbprint =
      "ee fd 06 98 cc ee 2c 0e af d3 42 f3 6d 8a c5 46 46 f0 03 f8"
  };

var clientConfiguration = new ClientConfiguration()
  {
    ClientCertificateThumbprint =
      "e8 8b 7e 7a 33 7f d9 8e c2 cc 60 26 39 f9 79 59 83 bd 64 fd",
    ServerConfigurations = new ServerConfiguration[]
      { serverConfiguration }
  };

var keyService = new KeyService(clientConfiguration);

Key retrieval and remote encryption commands

Key retrieval

This SQL command will encrypt the string “Hello World” with a key retrieved from the AKM server, the key named “AES128”:

select dbo.LocalEncrypt('AES128', cast('Hello World' as varbinary))

This SQL command decrypts the result:

select convert(varchar, dbo.LocalDecrypt(dbo.LocalEncrypt('AES128', cast('Hello World' as varbinary))))

Remote encryption

This SQL command will perform remote encryption on the server. The data is sent to the server and encrypted remotely on AKM Server and the result sent back:

select convert(varchar, dbo.RemoteDecrypt(dbo.RemoteEncrypt('AES128', cast('Hello World' as varbinary))))

It doesn’t matter where you encrypt or decrypt, locally or remotely, for example:

select convert(varchar, dbo.LocalDecrypt(dbo.RemoteEncrypt('AES128', cast('Hello World' as varbinary))))
select convert(varchar, dbo.RemoteDecrypt(dbo.LocalEncrypt('AES128', cast('Hello World' as varbinary))))

Chapter 4: Encrypt a Table

Overview

This chapter describes how to achieve automatic encryption by replacing a table with a view. You create a new table where one or more columns are encrypted and move your data into the new table. You can then create a view on that table which lets your application see the data in the same format as it always has, that is, unencrypted. Triggers on the view can let your application manipulate the data as if it were still in the old, unencrypted table. In most cases (other than perhaps those using complex queries), once the view has been renamed to have the same name as the original table, the application need not be aware of the switch at all.

This chapter guides you through this process using a sample original table, assuming that you have already created the UDFs as described in the previous chapter.

Limitations

The database example in this document is extremely simple. If your data tables use complicated joins, triggers, or other more complex SQL functions, these techniques may not work for you, or may take more extensive work to implement.

Create an sample original table

In this example you have a table phone_list that contains two columns, name and phone_number, and you will change this table so that the phone number is encrypted.

First, select the user database:

use my_database

Or, create a new database:

create my_database

Then create the table and populate it with sample data:

CREATE TABLE phone_list  
  ( 
     name         VARCHAR(256), 
     phone_number VARCHAR(256) 
  ); 

Now you can load some data into the table:

INSERT INTO phone_list 
VALUES      ('John Smith', 
             '555-2368'); 

INSERT INTO phone_list 
VALUES      ('Mary Johnson', 
             '555-0632'); 

INSERT INTO phone_list 
VALUES      ('Pat Williams', 
             '555-1098'); 

Create a new table to hold encrypted data

We create a new table to contain the data in the old table. Once the data has been moved over and encrypted, the old table will be obsolete. (You could also split the data so that only sensitive columns are moved to a new table, but this example is very simple.) The new table will store the phone number in an encrypted binary format.

CREATE TABLE phone_list_encrypted 
  ( 
     name                    VARCHAR(256), 
     phone_number_ciphertext VARBINARY(max) 
  ); 

Create a view over the new table

You do not need to access the new table directly. Instead, you can create a view over the new table like this:

CREATE VIEW phone_list_2 
AS 
  SELECT name, 
         CONVERT(VARCHAR, dbo.LocalDecrypt(phone_number_ciphertext))
           AS phone_number 
  FROM   phone_list_encrypted; 

Create triggers on the view for updating

Next, you can create some triggers on the view to manipulate the table with UPDATE and DELETE as if it were a real table:

CREATE TRIGGER phone_list_2_insert 
ON phone_list_2 
instead OF INSERT 
AS 
  BEGIN 
      INSERT INTO phone_list_encrypted 
                  (name, 
                   phone_number_ciphertext) 
      SELECT name, 
             dbo.LocalEncrypt('AES256', 
             CONVERT(VARBINARY, phone_number)) 
      FROM   inserted; 
  END 

CREATE TRIGGER phone_list_2_update 
ON phone_list_2 
instead OF UPDATE 
AS 
  BEGIN 
      UPDATE phone_list_encrypted 
      SET    name = (SELECT name 
                     FROM   inserted), 
             phone_number_ciphertext = (SELECT 
             dbo.LocalEncrypt('AES256', 
             CONVERT(VARBINARY, phone_number)) 
              FROM   inserted) 
      WHERE  name = (SELECT name 
                     FROM   deleted); 
  END 

NOTE: The key name “AES256” is used in this example. You will use the name of an encryption key on your AKM server.

Transfer the data to the new table

The next step would be to move the data from the original table to the new table:

INSERT INTO phone_list_2 
            (name, 
             phone_number) 
SELECT name, 
       phone_number 
FROM   phone_list; 

The insert trigger will automatically encrypt the phone number values. After this step the original phone_list table is no longer needed. It is completely replaced by the new phone_list_2 view and phone_list_encrypted table.

Manipulate the data in the new table

If you select from phone_list_encrypted, you will not be able to read the phone numbers:

SELECT * 
FROM   phone_list_encrypted;
John Smith    0x07000000574849534B455918000000467871645272435A354255505A6F6F3835577A3278413D3D1000000078285ACDB822C616787FB181DA94A8AD100000005B1C5B2D97C851C610889523BD85BF2A
Mary Johnson    0x07000000574849534B455918000000467871645272435A354255505A6F6F3835577A3278413D3D1000000096A0AC8E6BAD9430F7ADCE90ED77BE88200000005B1C77E0A8DC2171D0B102525514E0232D05E985B4C08EA74D125837B49BD215
Pat Williams    0x07000000574849534B455918000000467871645272435A354255505A6F6F3835577A3278413D3D1000000080E8E59C0D2F17C07E9460975F7CEF5A1000000036C0B278625D9A4EABA91F27745A5110

But if you select from the phone_list_2 view, you will see the original values. These are decrypted by the view automatically:

SELECT * 
FROM   phone_list_2;
John Smith 555-2368
Mary Johnson 555-0632
Pat Williams 555-1098

You can also use UPDATE and DELETE statements on the view, as if it were the original table:

INSERT INTO phone_list_2 
VALUES      ('Townsend Security', 
             '356-4400'); 

UPDATE phone_list_2 
SET    name = 'Townsend Security, Inc.' 
WHERE  name = 'Townsend Security' 

UPDATE phone_list_2 
SET    phone_number = '360 356-4400' 
WHERE  name = 'Townsend Security, Inc.' 

UPDATE phone_list_2 
SET    name = 'Townsend Security', 
       phone_number = '356-4400' 
WHERE  name = 'Townsend Security, Inc.'; 

DELETE FROM phone_list_2 
WHERE  name = 'Townsend Security'; 

DELETE FROM phone_list_2 
WHERE  phone_number = '356-4400'; 

Either DELETE statement works, but of course they both delete the same row in this example so whichever one you try first will delete the row.

Rename the original table and the new view

Once the view is working, you can drop the original table. You should make a backup before deleting the table. Then, rename the view so that it has the name of the original table. This way, queries in the application will run against the view, without any changes to the application. The commands to drop the table and rename the view are:

drop table phone_list
sp_rename phone_list_2, phone_list

Note that when you rename the view, the triggers that you have already defined, referring to the original view name phone_list_2, will be automatically updated to work on the new view name, phone_list.