Review of NetLib Encryptionizer for SQL Server

by Dale Elizabeth Corey, 1/12/2005

Introduction

Do you process credit card transactions?  Have you ever

worried about a hacker trying to steal your database or backup file and trying

to view (clear text) or attach it elsewhere?  Wouldn't it be nice to secure

the database and SQL server with the same encryption key?  NetLib Encryptionizer

for SQL Server can do this for you!  It is a data encryption product that is

compatible with SQL Server 2000 and 7 databases.  It is server-side and

provides security at the database (Automatic Whole Database Encryption), and

even, column level (Column Encryption APIs).    NetLib

Encryptionizer will allow you to comply with VISA CISP (credit card security

program) requirement #3 and is relevant to HIPAA, GLBA, SDP, etc. guidelines.

Encryptionizer offers two types of encryption:

Automatic Whole Database Encryption allows you to encrypt and

decrypt the entire database on-the-fly using industry standard algorithms such

as DES and AES, using key lengths of 64 bits to 256 bits, and using file

systems such as FAT and NTFS as well as CD/DVD media types (see the Environment section for all

supported encryption algorithms, key lengths, and supported systems/media

types).  The data is encrypted as "data-at-rest" on the

disk.  Decryption occurs in RAM only, not on the disk, making it

transparent to SQL Server and keeping the data on the disk unusable if

stolen.  Only the authorized SQL Server has access to the encrypted files. It protects the entire database, including data, schema, stored procedures, etc.

Column Encryption APIs allows you to authorize

users/groups/roles to access certain columns.  This can also be applied to

the System Administrator account (SA).  The APIs can even limit access to a

table via rows, for example, by creating a view which retrieves decrypted

data.   The Column Encryption API is implemented as a User Defined

Function (UDF) in SQL Server 2000/MSDE 2000 which can be used in SQL commands,

views, stored procedures, other UDFs, triggers, etc.  The Column Encryption

API is implemented as a stored procedure in SQL Server 7.  Column

Encryption APIs can be used in combination with Automatic Whole Database

Encryption or independently.  Fortunately, NetLib Encryptionizer will be

releasing a product sometime after February 2005 called COL-E™ (see the

"Using NetLib Encryptionizer" section).  It is the GUI equivalent

of the Column Encryption APIs.

NetLib also provides a desktop engine product called NetLib

Encryptionizer for MSDE and a desktop application product called NetLib

Encryptionizer DE, both primarily targeted for developers.  NetLib Encryptionizer for SQL Server supports Windows

NT/2000 clustered servers (Active/Active, Active/Passive) but clustering is not

supported by NetLib Encryptionizer for MSDE.  Other APIs included with

NetLib Encryptionizer for SQL Server are for encrypting/decrypting database and non-database

files (i.e., spreadsheets, documents, etc.) to be distributed to

workstations, laptops, or other servers that have NetLib Encryptionizer

SQL Server, MSDE or DE.  When distributing applications, you can use

Encryptionizer in the installation script (requires a distribution license from

Communication Horizons).  You will need to contact Communication Horizons

for registry entries, files, and DLL calls.

Environments Supported by NetLib Encryptionizer for SQL Server

The following versions of SQL Server are

supported (Note:  Encryptionizer

for MDSE also supports MSDE 2000/7 and SQL Server 2000/7 Desktop Editions):

• SQL Server 2000 Enterprise Edition, SQL Server 2000 Standard Edition, SQL

  Server 7 Enterprise Edition, SQL Server 7 Standard Edition

The following versions of Windows are supported (Note:  Encryptionizer

for MDSE also supports NT4 workstation, Windows 95/98/ME):

• Windows NT4 Server: SP5 or later, single- or multi-processor, Windows 2000

  Server: single- or multi-processor, Windows 2003 Server: single- or

  multi-processor, Windows XP

The following encryption algorithms are supported:

• AES, Blowfish,

  DES (Data Encryption Standard), DES-ECB, Triple DES (also known as 3DES or

  DES3) with two or three keys, Triple DES-ECB with two or three keys, LLE (NetLib

  proprietary format), RC5 (available upon request but may require a RSA license fee.)

The following key lengths are supported:

• 64, 128 or 256 bits (8, 16, and 32 bytes respectively).  The

  international version is limited to 128 bits.

The following media/files systems are supported:

• FAT,

  FAT32,

  NTFS, CDROM/DVD*,

  RAW*

  * May require a change to the registry.

The following are requirements for COL-E:

• NetLib Encryptionizer for SQL Server Column APIs must already be installed

  and requires DotNet 1.1 or later.

All data types are supported by Whole-Database Encryption. However, the following data types are supported by column encryption (look for the next major release to add support for the other data types):

• char
• nchar

• binary

• varchar

• nvarchar

• varbinary

• money (but not smallmoney)

• float (but not real)

Vendor Comments: The next major release will add support for other data types.

Installation

The installation (NLSETUP.EXE) is very straightforward but has

to be run on the server itself, not on a workstation/server connected to the

server.  The SQL Server service should not be running.  If you are

installing a cluster, you need to stop the cluster as well.  Encryptionizer

needs to be installed on all the clustered servers.

The installation procedure consists of

a splash screen, license agreement, user information, installation path, the

option to install the APIs (see figures 1 - 3), and restart.  Once

restarted, the Windows' Programs Menu will contain the options shown in Figure

4.

Figure 1

Figure 2

Figure 3

Figure 4

Using NetLib Encryptionizer for SQL Server

Before you begin to set up Whole Database Encryption, you must stop the SQL Server service 'cause

you cannot encrypt an attached database.  Also, make sure you backup the

database.  Then, to use NetLib Encryptionizer, you need to do two things

and not in any specific order.  One, you need to run the "Encrypt

Decrypt Wizard" (SECNCRPT.EXE).  Two, you need to run the "Encryptionizer

Admin Wizard" (SECADMIN.EXE).  It's recommended that you temporarily

disable all "Auto-start services" until after you manually restart SQL

Server.

Encrypt Decrypt Wizard

When you run the "Encrypt Decrypt Wizard", you'll

receive this screen first (Figure 5):

Figure 5

Next, you'll be asked whether you are encrypting,

decrypting, re-encrypting, or validating key encryption (Figure 6):

Figure 6

When the "Encrypt" option is chosen, you

will be asked to select the source and the destination directory (Figure 7):

Figure 7

Be careful when you choose your source and

destination directories.  If they are the same, you will run into the

message shown below warning you about overwriting your original source file (Figure 8).  To avoid this, I created a separate

directory, as recommended, called "Test_Encrypt".  Upon finishing

the Encrypt Decrypt Wizard, Encryptionizer created an exact copy of the

database, only it was encrypted per my specifications.

Vendor Comments:

The advantages of overwritting the databases are that less space is required and file fragmentation is not affected.

Figure 8

Now, it's time to set up the encryption options (Figure 9)

which include the algorithm, key length, key value, and compression (see Figure

10).  Note:   Uncheck "Mask Key" to generate a random

key.  Leaving "Mask Key" checked allows you to create your own

key but it is masked by asterisks as you type (if you do not enter enough characters for the key length in hex digits that

you selected, it will be padded with binary zeros).  "Permanent Mask

Key" means that you cannot toggle to see the key entered.  If you

choose a "Random Key", be sure you keep the key (ASCII or binary) in a safe place 'cause

you will be asked for it when you run the "Administration Wizard"

(i.e., I copied mine into Notepad and saved it).

Figure 9

Figure 10

The next screen is informational based on the selections you

chose (Figure 11).  Click Finish to confirm.

Figure 11

You'll receive the following screens during the

encryption process (Figures 12 - 14):

Administration Wizard

When you run the "Administration Wizard", you'll receive this

screen first (Figure 15):

Figure 15

Next, you will be asked which Server Instance to enable/disable

encryption (Figure 16):

Figure 16

Now, you need to enter the relevant key information that matches

the information entered in the "Encrypt/Decrypt Wizard" (Figure

17).  You can have up to two keys per server.

Figure 17

Next, you have other options available that you can choose from

(Figure 18).  You can set up a proxy, automatically encrypt all backups and

new databases, lock profile and proxy to the machine, and enforce CRC checks

(this option can cause problems with service packs).  Note:  "New

Databases and Backups" include databases created with CREATE DATABASE,

BACKUP DATABASE, RESTORE DATABASE, or Enterprise Manager (refer to

Encryptionizer's developers notes for toggling the "Encrypt New

Database" flag).  Also, when applying new Service Packs for SQL

Server, you need to check with NetLib Encryptionizer for the latest Service Pack

Guide in case your database(s) and/or column(s) need to be decrypted first

(which is usually not necessary in most cases according to NetLib).

Figure 18

You will then be taken to this informational screen (Figure 19)

and then you are done (Figure 20):

Figure 19

Figure 20

The encryption really is transparent once you attach the

database.  The database looks like the same as always.  To verify

whether it is really encrypted, you can use the Encrypt Decrypt Wizard's Verify

option (see Figure 6), copy the MDF and LDF files to another server and attach

(it won't be recognized), or open the database in a text editor and there should

be no plain text in the file (see Figure 21).

Excerpt from Northwind database before encryption (text file)

?:?s[1]?CREATE VIEW syssegments (segmemt, name, status) AS       SELECT  0, 'system'     , 0  UNION       SELECT      1, 'default'    , 1  UNION       SELECT      2, 'logsegment' , 0 0

Excerpt from Northwind database after encryption (text file)

v??@Ku??\??#??&????e???"?    Z?&?m            ?          "{???? c?^R$[1]?B4N?k% 9~j???=V?Tx1?d6?????*1???w???????.??o?????sD????????*????S??????tV>??y?HR fl?/?????k?N? 4SCd??Y tG?

Figure 21

Column Encryption APIs

Currently, column level encryption is implemented programmatically.  You

can build these functions into applications or even into views and

triggers.  The Column Encryption APIs can get a bit detailed which we don't

have room for here.  Just know that you can use key handles (locally or

globally) to encrypt and decrypt data.  You don't need the actual key

information.  Here's an excerpt from NetLib's API documentation concerning

the key handles (Figure 22):

Local Key The InitKey function (fn_n_initkey) generates a temporary key handle that provides a session pointer for the encrypt and decrypt functions. It does not permanently store the key anywhere; it can be used by the encrypt and decrypt functions provided the key handle is active. This key handle ceases to exist when the connection is closed or SQL Server is stopped. Therefore, each time you connect to SQL Server, you must reissue InitKey to set a new handle reference for the key. In addition, two separate SQL connections cannot share a key handle set by InitKey. So Initkey should be executed for each SQL connection (or likely, at the start up of the application). The key password can be delivered to the InitKey function in many ways: direct reference from application, stored in registry entry, or entry by user when starting application.

Global Key You can also set a global key reference (handle). This key handle can be shared by many SQL connections. You can set this key using the function SetKey (fn_n_setkey). This key handle persists as long as the SQL instance is running. Once again, the key is not stored on the hard drive. Once the SQL instance stops running, the key handle is no longer available. You must have a way to make sure you run the SetKey() function when your application starts up; you might consider having it run as a startup job when SQL Server starts. You can do interesting things like save the key phrase for SetKey in the registry, thereby tying it to the machine if you wish.

Server Key There is one exception to the above if you use the Encryptionizer Administration Wizard (SecAdmin.exe).  This creates an encryption key profile that is stored on disk. It is the equivalent of using SetKey() that returns a key handle of 1. This is persistent as long as SQL Server is running (when SQL Starts up it executes the equivalent of the SetKey function). There are additional security options available that allow you to lock the encryption key profile to the machine, as well as store the encryption key profile in an alternate location such as a USB flash device, CDROM, floppy, or network location. The Encryptionizer for SQL User Guide and the SecAdmin Help files provide more information on using this utility. Use of the global server key is demonstrated in some of the sample script files provided with the Encryptionizer installation.

Figure 22

When using Column Encryption APIs, you need to be sure to look closely at the

API Reference Guide for hints, traps, precautions, etc.  Column encryption

products, not just this one, can run into

performance issues when using CONTAINS, LIKE, etc.; issues when using views

(i.e., using transparent views on large tables creates performance issues);

issues changing column definitions while the column is encrypted; issues with

check constraints; and issues with computed columns.

NetLib Encryptionizer provides sample code for Column Encryption APIs that

are modeled after the Northwind database.  You are free to use these. 

Click the following the link below to access a PDF file of all the Whole

Encryption and Column Encryption APIs provided (check

the API Reference Guide for more detail on each API):

NetLib Ecryptionizer API Quick Reference

Col-E (Beta Release Candidate 2)

As you can see, using Column Encryption APIs can be quite detailed. 

NetLib now has Col-E in Beta release and is due for full release sometime after

February 2005.  The Column Encryption Manager is the GUI equivalent of Column Encryption APIs. 

However, NetLib Encryptionizer for SQL Server Column APIs must already be installed

and it requires DotNet 1.1 or later.  It's really easy to install. 

You'll see screens for the splash, license agreement, user information,

installation information (basically, the ReadMe file), installation folder,

shortcut folder, and installation progress.  Then, you will be asked if you

want DotNet Framework 1.1 installed (Figure 23).  If you do, it will fetch

it and install it.  If not, the installation is completed.  Once

complete, you will see a the new Program Menu options for Col-E (Figure 24).

The first thing I noticed after installing and launching Col-E

was that the memory usage was a bit high.  My system seemed to slow

down as I attempted my first try to set up column encryption with the Encryption

Manager (i.e., choosing the server instance, database, etc.), and Task Manager reported the memory usage as 10,400K.  This was more than SQL Server itself (which Task Manager reported at 6,828K).  Still, once I was connected to the Server instance, I was happy to see a tree structured GUI that I could work with and I liked the fact that columns that contained data types that are incapable of being encrypted were grayed out (Figure 25).  The menu options are Connect, Disconnect, Encrypt, Decrypt, Rebuild, Server Keys (to generate a new key if you haven't used the Admin Wizard), and Permissions. 

Vendor Comments:

The performance results observed reflect the performance of encrypting an entire column during installation. Since this is normally a one-time event, the performance impact observed here should be noted, but it will not be an ongoing concern.

Figure 25

To encrypt a column, all you have to do is click the box next to

it and then click Encrypt (Figure 26).  This will bring up the Encryption

Wizard.   You will be asked how many keys you want and what type of

view to make it, Transparent or New (Figure 27).  Next, you will be asked

about permissions and whether you want to set up a new role (Figure 28). 

Once a column is encrypted (Figure 29), NEVER make an update (i.e. changing a

column name) to the original base table or else the integrity of the table can't

be guaranteed.  You need to do that with built in insert and update

triggers.  Col-E works by creating views of base tables and working within

the view, so in order to change a column definition/name or table name, decrypt

it first.  Col-E renames

encrypted tables with a prefix of SYSNL as a reminder that these tables

are encrypted.  In case of a problem, you can repair the views by

decrypting and re-encrypting the columns in the affected table or use the

Rebuild Tool.

Figure 26

Notes from NetLib on View Types Transparent This view type allows encryption to be completely transparent to existing applications. A view is created that is named to the original name of the base table. Queries to the original table name will show data decrypted for users authorized to see the decrypted data using the assigned database roles, but all updates and inserts will be encrypted in the base table. The base table is renamed to SYSNL_DONOTMODIFY$$%Table%. Update/Insert triggers are created with the view that will maintain the integrity of the encrypted column data in the base table. You will determine the Roles that are authorized to view the data as decrypted in a later step. New View This view type is primarily for more technical users who will be customizing their own views, triggers, and procedures.  It will not rename the base table. A new view will be created: %table%_new. This view will both have the appropriate Update/Insert triggers to maintain and control encryption in the base table. Any updates or inserts to the original base table will NOT be automatically encrypted. Automatic encryption can be controlled through the New View. Queries to the new view name will show data decrypted for users authorized to see the decrypted data using the assigned database roles. Authorized database roles are determined in a later step. IMPORTANT! We do not recommend use of the New View unless you are very careful with the naming schemes of your tables and the table names called by the applications. If you use the New View, we recommend that Update/Insert commands not be authorized for the base table. To maintain encryption integrity, all updates should be performed through the newly created views. TIP! If you are decrypting columns, but not decrypting all columns that are marked as encrypted for a table, Col-E assumes that the view created will be the same as when the table was originally encrypted. If you wish to decrypt columns but want to have a new type of view created for the remaining columns, decrypt all the columns for the table and re-encrypt the remaining columns in the desired view type.

Figure 27

Figure 28

As with any column encryption product, they are not without a plethora of potential

hazards.  The Col-E documentation is loaded with warnings and tips such as

these so be sure you understand all the implications before you attempt column

encryption:

Because Col-E works by creating views of base tables and working in them, it is essential that table structure remain static; otherwise Col-E will not be able to write data to the correct location. If table or column names are changed in tables where any of the columns are encrypted, views created by Col-E will no longer work.

Check Constraints - Because encrypted values may not meet SQL Server constraints, columns with constraints may not be encryptable. For instance, consider a situation where there is a column called Price, and its value is restricted to a monetary value. If the value were encrypted, the result may not fit that definition. Therefore, when you try to encrypt this column, you may get an error message because the encrypted value does not fit within the constraint requirement. To encrypt the column, you may need to consider alternate methods of constraining values.

Computed Columns - Encrypting columns is not supported in the Col-E Manager since these values are not actually stored in the column, but are calculated at run time. In addition, the columns that are the factors of a computed column cannot be encrypted.

Default Values - Col-E supports default values in encrypted and unencrypted columns. However, if changes are made to default values on any column in a table that has encrypted columns, you must run the Rebuild Tool.

Identity Column - The identity column cannot be encrypted.

When considering column-level encryption, you must be aware of how encryption affects query performance, particularly if you are encrypting columns that participate in an index...  Any clause that does not look for a unique index value has the potential to be very slow on a large table.

Another query issue particularly affects transparent views, i.e., views that present an encrypted column as decrypted.

If you disable Encryptionizer, thereby removing the server encryption key, you will not have access to your encrypted columns, and it is likely that access to the encrypted columns will cause an error. You will not be able to decrypt these columns unless the key is specifically known.

If you must change a column definition, column name, or table name, decrypt it first or the data may be corrupted.

Be sure not to encrypt data using another encryption tool or APIs; if you do, your data may be compromised.

If you are encrypting a column in a table that already has another column encrypted, the view type will be used automatically. To use a different view type, decrypt any encrypted columns and reencrypt them using the desired view type. However, you can choose different keys for columns within the same table.

We do not recommend use of the New View unless you are very careful with the naming schemes of your tables and the table names called by the applications. If you use the New View, we recommend that Update/Insert commands not be authorized for the base table. To maintain encryption integrity, all updates should be performed through the newly created views.

If you are decrypting columns, but not decrypting all columns that are marked as encrypted for a table, Col-E assumes that the view created will be the same as when the table was originally encrypted. If you wish to decrypt columns but want to have a new type of view created for the remaining columns, decrypt all the columns for the table and re-encrypt the remaining columns in the desired view type.

Do not uninstall Encryptionizer before you have disabled Col-E and decrypted any encrypted columns.

We recommend that you temporarily disable Auto-Start for SQL Server on all servers until all of the relevant steps in all sections are completed successfully. Once you have completed all steps and successfully, manually started SQL Server, you can re-enable Auto-Start.

While column encryption can be used in conjunction with whole database encryption, it is not recommended that you combine methods of column encryption (i.e., automatic column encryption using Col-E Manager and the column encryption APIs).

It is recommended that columns included in indices of a large table or columns that are used frequently for searches of a large table NOT be encrypted.

Col-E requires an identity column in any table that will have encrypted columns. The identity column ensures a unique identity for each record.

Support

Support includes access to the Encryptionizer Knowledgebase, an

online support form, and the following Gold, Silver, and Bronze level support

options when you purchase a NetLib Encryptionizer License (you are required to

purchase support for the first year):

• Bronze Support (not available for Column Encryption): email

  support, no product updates* 5% ($250 minimum)

• Silver Support: email support, plus product updates 15%

  ($750 minimum)

• Gold Support: phone & email support, product updates 20%

  ($1000 minimum)

• Platinum support - Gold level 24/7 - available upon request

Contact NetLib for volume or distribution license pricing.

Conclusions

NetLib Encryptionizer is an excellent database encryption

product for SQL server 2000/7.  It provides security at the database level,

an area that is easily overlooked.  It's great for financial institutions,

insurance industries, and health services.  The column encryption part of NetLib Encryptionizer competes with other products such as XP_CRYPT, DbEncrypt, and Protegrity.   One wonders what will happen to these column encryption products with SQL Server 2005 and it's native encryption built in, but I digress!

All in all, I found NetLib Encryptionizer any easy product to install and use, relatively speaking.  However, I could have spent 12 more hours testing column encryption alone.  The Whole Database Encryption is perfect for the end user and the API reference is structured nicely for developers/programmers.  The Column Encryption APIs were a little scary for the "non-developer" types.  The new COL-E interface should cure that nicely.  Data key storage follows best practice guidelines fairly well:

• Key(s) can be embedded into the application with an API call.
• Keys can be stored in a strongly encrypted file (called a Profile) on the

  local drive.

• Keys can be stored in a Profile on a floppy disk, CD or USB key. The

  authorized user must insert the floppy disk, CD or USB key in order to start

  SQL Server. (The disk can be removed once the application starts).

• Keys can be stored in a Profile on a remote machine (refe rred to as a

  Proxy location). If the Proxy machine is not found, SQL Sever cannot be

  started.

• A designated person can enter the key manually when SQL Server is started.

  This is suitable only where an authorized ?starter? will always be on

  hand.

NetLib Encryptionizer provides several advantages over just using Microsoft's Encrypted File System (EFS).  It supports a wider array of media types and operating systems, you can bundle/install it with an application, and it provides an additional layer of security.

There were only a few things that I would like to have seen:

1. The online documentation needs some updating. 

   Dating the pages would be a great help.  Here are some examples. 

   a.) The online document describing Encryption and CISP, GLBA, HIPAA, etc.

   refers to product updates that were due in the second quarter of

   2002.  What?  b.)  The FAQ for SQL Server Security made this

   statement, "Some of the new methods we are working on for clients are

   PKI and Dongle."  Has this happened?  When was this last

   updated?  c.)  The Technical Overview for NetLib Encryptionizer

   never mentions LLE (NetLib's proprietary encryption) but is mentioned in the

   installed PDF.  NetLib explained that this was because it was for

   backwards compatibility.

2. It would be nice to have a packet layer encryption

   product bundled with this as well.  However, I realize that this is not

   the market strategy for this type of product.

3. Support for CAST, IDEA, PKI, smart card needs to be

   included.

4. Support for ntext, int, smallint, datetime, bit, real, small money, and image data

   types needs to be included.

5. When installing COL-E, I noticed this statement in the ReadMe file about supporting columns less than 8 characters and also data types, "Supported Data Types - This section indicates that columns must be eight characters or longer. Col-E now supports columns of under eight characters. In addition, the following data types are supported, although not listed: char, nchar, and binary."  This was very confusing to me.

   Vendor Comments:

   Columns under 8 characters are supported and the comment above is a typo in the documentation. It has been corrected.

6. Performance needs to be improved with the COL-E interface

   although I do realize this is a Beta version and it is typically run only

   once to encrypt the columns.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best

and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.netlib.com

Developer: NetLib, A Subsidiary of Communication Horizons, LLC

Pricing as of January, 2005 (subject to change):