UPDATE: SQL Encryption Assistant v.1.0.6.238

Devenius has just released an update to SQL Encryption Assistant. The latest version is now 1.0.6.238.

This update applies to the following editions:


Standard
Standard 2008
Professional
Professional 2008

Some of the new features include:

- Expanded EKM functionality
- "Color-Coded" Encryption Status
- Extensible Key Management device support or asymmetric and symmetric keys

To apply the update, please see the post "How do I get the latest version of SQL Encryption Assistant?".

SEA: Did You Know?

Cool feature even I forget about!

Did you know...that from the object explorer in SEA, you can select an object and drag-and-drop it to the query window? Nice!

Case of the SQL Encryption Mondays

While we were developing SQL Encryption Assistant (SEA), we discovered an interesting if not scary tidbit with the encryption process. It involves encrypting a column of data with a symmetric key. Here’s the setup: A simple 3 column table, we’ll call it CUSTOMER_DATA. The columns are as follows:

• CUS_ID – INT identity column
• CUS_NAME – VARCHAR customer name
• CUS_SSN – NVARCHAR(MAX) customer social security number

CREATE TABLE [dbo].[CUSTOMER_DATA]
(
[CUS_ID] [int] IDENTITY(1,1) NOT NULL,
[CUS_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_SSN] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_CUSTOMER_DATA] PRIMARY KEY CLUSTERED
(
[CUS_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Add a row of data to the table.

INSERT INTO dbo.CUSTOMER_DATA (CUS_Name,CUS_SSN,CUS_DOB)
VALUES ('New Customer','123-456-789','11/12/1970')

Using SEA, we created a password-encrypted symmetric key named ColumnEncryptionKey. This key will be used to encrypt a column of data in the table. (Can you guess which one?) Here’s what the T-SQL would look like:

CREATE SYMMETRIC KEY [ColumnEncryptionKey]
AUTHORIZATION [dbo]
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = N'1234qwer'

Now, using that symmetric key we encrypt the column of data. To do this we need the decryption password of the key. No problem. But, as usual, It’s a Monday and I haven’t had enough “fill-in-the-blank with your favorite caffeinated concoction”, and I ham-sandwiched the password. Doh! No problem, I’ll just enter it again, c-o-r-r-e-c-t-l-y and our column of SSNs will be safe. But wait, there’s more! There’s a red message from SSMS. What could it be saying…


Msg 15313, Level 16, State 1, Line 1 The key is not encrypted using the specified decryptor.

Right, got it. I know. Wrong password. But, that’s not the interesting piece. Next line, please (drumroll)...

(1 row(s) affected)

WHAT!?!?! It didn’t update the…I entered the wrong…what the…who the...why would it do that? Let me check the table.

SELECT * FROM dbo.CUSTOMER_DATA



Noooooo! It nulled out the data! Yep, turns out if you enter the wrong decryptor, SQL Server goes ahead anyway with the failed encryption. Your data is gone. So, how do we fix this? One, don’t enter the wrong decryptor. Excellent, I’ll try and be more perfect next time. Or, use SEA. It will not let this happen and save you from yourself when you have a case of the “Mondays.”

Digital Signatures with SEA

One of the features of SQL Encryption Assistant is providing an easy mechanism to digitally sign programmables in a SQL Server Database. For this example I have created the following

1. A table named EMPLOYEES


2. A procedure to select all rows from EMPLOYEES named getEmployees


3. Digital Certificate named CERT_01

I can now launch SQL Encryption Assistant inside Management Studio. Navigate to the certificate you wish to use for signing and right click to get to the Add Signature menu item.



From here I can sign procedures, functions, triggers, or assemblies. In the interest of keeping this simple, I will have a follow up post on counter signing on a later date.


The Add Signature window will open upon selecting the menu item. As you can see below, I have selected the procedure getEmployees to be signed. I have also entered the password that was used to protect Cert_01.



Click OK to sign the procedure. I also would like to mention that once a programmable is signed, it will show up in the dependencies window for the certificate used in the signing.

SEA Pro 1.0.5.908 Update

SQL Encryption Assistant Professional Edition 1.0.5.908 has been released.

To obtain the latest version see the FAQ post in our forum:
How do I get the latest version of SQL Encryption Assistant?

Release Details

Issue
Encrypt data by symmetric key protected by a password. Enter invalid password and click "OK". Data will be null in selected column due to incorrect decryptor.

Resolution
Encrypting and decrypting data by symmetric key protected by a password will not allow incorrect password to be entered. If an incorrect decryptor is entered the encryption or decryption process will be terminated and the data will not be null.

SEA Pro 2008 1.0.5.908 Update

SQL Encryption Assistant Professional 2008 Edition 1.0.5.908 has been released.

To obtain the latest version see the FAQ post in our forum:
How do I get the latest version of SQL Encryption Assistant?

Release Details

Issue
Encrypt data by symmetric key protected by a password. Enter invalid password and click "OK". Data will be null in selected column due to incorrect decryptor.

Resolution
Encrypting and decrypting data by symmetric key protected by a password will not allow incorrect password to be entered. If an incorrect decryptor is entered the encryption or decryption process will be terminated and the data will not be null.

HOW TO: Enable Transparent Data Encryption (TDE)

To enable TDE, follow these steps.

1. Create a certificate protected by the master key.
2. Create a database encryption key and protect it by the certificate.

Select the database that will implement TDE. Right-click and select 'Database Key -> Create'.



Select the certificate created in step 2.

3. Enable TDE by selecting the database, right-click and select 'TDE -> Enable'

HOW TO: Encrypt data with SQL Encryption Assistant

1. First let's setup a sample table in the database. This table will have 4 columns:

Code:
CREATE TABLE [dbo].[CUSTOMER_DATA](
[CUS_ID] [int] IDENTITY(1,1) NOT NULL,
[CUS_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_SSN] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_DOB] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_CUSTOMER_DATA] PRIMARY KEY CLUSTERED
(
[CUS_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2. Prime the table with some data.

3. Open SQL Encryption Assistant.

4. Create a symmetric key named 'EncryptKey', protected by a password.

5. Select the key created in step 4, right-click and select Tasks -> Encrypt Data



6. Choose 'Password' for the key decryption and enter the key's password.

7. In the 'Select Table' group box, select the table created in step 1, 'TEST'.

8. Select the column 'TST_Data' and click '>>'. This is the data we will encrypt.

9. Click 'Ok'.

The data is now encrypted using a symmetric key.