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.”

Certificate or Asymmetric Key?

One of the first questions regarding the security objects in SQL Server is what is the difference between certificates and asymmetric keys.

Similarily, certificates and asymmetric keys both encapsulate RSA keys. Certificates offer more flexibility, in that they may be imported from external sources. For example, a .CER file. Once the certificate or asymmetric key is available from within the database, the range of implementation options is essentially the same.

Certificates in SQL Server 2005

The most common uses for certiciates in SQL Server are to sign code, encrypt symmetric keys or encrypt data. The most compelling reason to use certificates for encrypting keys, is that they can be backed up and restored.