Using Views to Expose Encrypted Data in SQL Server

By:   |   Updated: 2013-10-21   |   Comments (10)   |   Related: More > Security

Problem

I have used SQL Server's built-in encryption to hide data in one of my SQL Server databases, such as demonstrated in this article: SQL Server Encryption to Block DBAs' Data Access. The problem is that this is a reporting system and my end users need to be able to query the data without having to remember the specialized decryption functions. Is there a way to do this?

Solution

Yes, there is, via the use of views.

Let's use the sample code from that previous article. Here it is:

CREATE DATABASE EncryptionTest;
GO
USE EncryptionTest;
GO 
CREATE TABLE dbo.EncryptedData (
  EncryptedCol VARBINARY(128)
);
GO 
-- Note the use of a symmetric key encrypted with a password
CREATE SYMMETRIC KEY DataEncrypt
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
OPEN SYMMETRIC KEY DataEncrypt
DECRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
GO 
INSERT INTO dbo.EncryptedData
(EncryptedCol)
VALUES
(ENCRYPTBYKEY(KEY_GUID('DataEncrypt'), 'Big Brother DBA is now blind!'));
GO 
-- Only with the correct password does everything work.
OPEN SYMMETRIC KEY DataEncrypt
DECRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
SELECT CONVERT(VARCHAR(MAX), DECRYPTBYKEY(EncryptedCol))
FROM dbo.EncryptedData;

Now let's consider the case where you want to try and keep DBAs out of the data. That's the most difficult case.

Keeping DBAs from Seeing the Password(s)

The first attempt is to simply create a view which uses the basic SELECT statement from the sample code. For instance:

-- This doesn't work because the password can't be specified
CREATE VIEW dbo.SeeEncryptedData
AS
SELECT CONVERT(VARCHAR(MAX), DECRYPTBYKEY(EncryptedCol)) AS 'EncryptedCol'
FROM dbo.EncryptedData;
GO
SELECT EncryptedCol 
FROM dbo.SeeEncryptedData;

However, this doesn't work, as the screenshot shows:

Keeping DBAs from Seeing the Password(s)

The reason this doesn't work is because the symmetric key hasn't been opened. The function DECRYPTBYKEY() has no way of specifying a password. So if you want to build a solution involving passwords and views, you're going to need to go the route of creating an asymmetric key or certificate and then using that to encrypt the symmetric key. The asymmetric key or certificate will be encrypted with a password. Here's how to set this up:

-- This does work because the password is set with the asymmetric key
CREATE ASYMMETRIC KEY ASymKeyPwd
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
CREATE SYMMETRIC KEY SymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY AsymKeyPwd;
-- Empty any rows
DELETE FROM dbo.EncryptedData;
GO 
-- Insert new data
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY ASYMMETRIC KEY AsymKeyPwd WITH PASSWORD = '17SomeHiddenPassword!76';
INSERT INTO dbo.EncryptedData
(EncryptedCol)
VALUES
(ENCRYPTBYKEY(KEY_GUID('SymKey'), 'Big Brother DBA is now blind!'));
GO 
CLOSE SYMMETRIC KEY SymKey;
GO 
-- Drop the view and recreate it
DROP VIEW dbo.SeeEncryptedData;
GO 
CREATE VIEW dbo.SeeEncryptedData
AS
SELECT CONVERT(VARCHAR(MAX), 
DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd'), N'17SomeHiddenPassword!76', EncryptedCol)) 
AS 'EncryptedCol'
FROM dbo.EncryptedData;
GO
SELECT EncryptedCol 
FROM dbo.SeeEncryptedData;

When it comes to creating the view, the star player is the function DecryptByKeyAutoAsymKey() which does allow us to specify a password for the asymmetric key. If we specify the right asymmetric key, SQL Server will automatically open the symmetric key that is encrypted by the asymmetric key, giving us the results we want. We're done, right? Not exactly. There's a flaw in building the view this way:

If we specify the right asymmetric key, SQL Server will automatically open the symmetric key that is encrypted by the asymmetric key, giving us the results we want. We're done, right? Not exactly.

The problem is that a DBA can look at the view definition and then extract the password for the asymmetric key. That gives the DBA the ability to then open the asymmetric key. An option is to use WITH ENCRYPTION when specifying the CREATE VIEW:

DROP VIEW dbo.SeeEncryptedData;
GO 
CREATE VIEW dbo.SeeEncryptedData
WITH ENCRYPTION
AS
SELECT CONVERT(VARCHAR(MAX), 
DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd'), N'17SomeHiddenPassword!76', EncryptedCol)) 
AS 'EncryptedCol'
FROM dbo.EncryptedData;
GO
SELECT EncryptedCol 
FROM dbo.SeeEncryptedData;

And this works for the merely curious. For instance, if we try the same trick to view the source, we get the following:

That gives the DBA the ability to then open the asymmetric key. An option is to use WITH ENCRYPTION when specifying the CREATE VIEW

However, there are three issues that cannot be overcome:

  1. DBAs grant access to the view. They themselves would be able to query the data through the view.
  2. Likely it's going to be a DBA who runs the CREATE VIEW code in the database. That means he or she will have the password embedded in the code.
  3. There are tools publicly available, including scripts, that can beat this encryption. Some of them are free.

Therefore, if you want the end users to be able to query the encrypted data, either they are going to have to know the functions (and the password to decrypt the key) or you're going to have to build some interface that presents the data for them.

The Simpler Case Where DBAs Can View the Data

If you're not restricted to where DBAs can't view the data, things are much simpler because SQL Server can handle all the key escrow. Let's set up that situation:

-- What about if SQL Server handles key escrow?
-- Clean up
DROP VIEW dbo.SeeEncryptedData;
GO
DELETE FROM dbo.EncryptedData;
GO 
-- Set up the chain 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeStr0ngPassw0rd!'; 
GO 
CREATE ASYMMETRIC KEY ASymKeyChain
WITH ALGORITHM = RSA_2048;
GO 
CREATE SYMMETRIC KEY SymKeyChain
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY AsymKeyChain;
GO
OPEN SYMMETRIC KEY SymKeyChain
DECRYPTION BY ASYMMETRIC KEY ASymKeyChain;
GO 
INSERT INTO dbo.EncryptedData
(EncryptedCol)
VALUES
(ENCRYPTBYKEY(KEY_GUID('SymKeyChain'), 'Big Brother DBA is now blind!'));
GO 
CLOSE SYMMETRIC KEY SymKeyChain;
GO 

The view definition is basically the same except now we don't need a password. In this case, you want to specify NULL, not an empty string ('') which isn't the same thing. The second case SQL Server will treat the asymmetric key as having a blank password and you won't get the decryption right. So here's the revised view definition.

-- Since there is no password on the asymmetric key, we must specify NULL
CREATE VIEW dbo.SeeEncryptedData
AS
SELECT CONVERT(VARCHAR(MAX), 
DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyChain'), NULL, EncryptedCol)) 
AS 'EncryptedCol'
FROM dbo.EncryptedData;
GO
SELECT EncryptedCol 
FROM dbo.SeeEncryptedData;
Next Steps


Last Updated: 2013-10-21


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, March 20, 2015 - 10:21:31 AM - Jaya Krishna Back To Top

Hi,

Thanks for the post. It is very helpful in understanding data encryption and decryption in sql.

I am facing a problem, below is the scenario I tried.

  • Created table(@TABLE) with columns type NVARCHAR(MAX).(Trying to encrypt FirstName, LastName, MiddleName, Country, TIN)
  • Created stored procedure to perform encryption
    • Stored procedure accepts input parameter of type NVARCHAR(MAX), encrypts the value, Convert the value to type NVARCHAR(MAX) and returns the value.

CREATE PROCEDURE DNB_ENCRYPT
(
 @Value NVARCHAR(MAX),
 @EncryptedValue NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
 IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'ASymKeyPwd')
  CREATE ASYMMETRIC KEY ASymKeyPwd WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'18SomeHiddenPassword!96';
 IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SymKey')
  CREATE SYMMETRIC KEY SymKey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY AsymKeyPwd;
 OPEN SYMMETRIC KEY SymKey DECRYPTION BY ASYMMETRIC KEY AsymKeyPwd WITH PASSWORD = N'18SomeHiddenPassword!96' 
 --INSERT INTO TESTTABLE(FIRSTNAME)VALUES(ENCRYPTBYKEY(KEY_GUID('SymKey'), @Value))

 SET @EncryptedValue = CONVERT(NVARCHAR(MAX), EncryptByKey(Key_GUID('SymKey'), @Value))
 CLOSE SYMMETRIC KEY SymKey;
END 

  • Created view on top of @TABLE, view decrypts and returns all the values in a table.

CONVERT(VARCHAR(MAX), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd'), N'18SomeHiddenPassword!96',  PRIMARYTIN))

The issue is with decryption, some of the values are decrypted properly and for few values I am seeing nulls.

Joe - 蠀簴䝌誉ꢁ罫檉 - Joe

Antony - 蠀簴䝌誉ꢁ罫檉 - Antony

123121312-蠀簴䝌誉ꢁ罫檉- NULL

IN-蠀簴䝌誉ꢁ罫檉-IN

US-蠀簴䝌誉ꢁ罫檉-NULL

 

I am unable to trace the problem. Could you please help me in resolving this issue.

 

Thanks,

Jaya Krishna

 

 

 

 

 

 


Monday, November 04, 2013 - 9:53:08 AM - K. Brian Kelley Back To Top

Even if a user has access to a symmetric key, the user must first open it to see data encrypted by it. So the reason you're only seeing data encrypted by Employe01 is because you never explicitly opened the Employe02 symmetric key.


Saturday, November 02, 2013 - 2:38:47 AM - Thanh Nguyen Back To Top

Hell Brian,

I have another question about symmetrick key

CREATE SYMMETRIC KEY Employe01
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = '123456789'

CREATE SYMMETRIC KEY Employe02
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = '987654321'

open SYMMETRIC KEY Employe01 decryption by password '123456789'

open SYMMETRIC KEY Employe02 decryption by password '987654321'

alter SYMMETRIC KEY Employe01 add encryption by symmetrick key Employe02

alter SYMMETRIC KEY Employe02 add encryption by symmetrick key Employe01

close all symmetric keys

 

table Salary with 2 columns : Name & salary

 

open SYMMETRIC KEY Employe01 decryption by password '123456789'

open SYMMETRIC KEY Employe02 decryption by password '987654321'

 

insert into employees (name,salary_crypt) values( 'Employe01', ENCRYPTBYKEY(KEY_GUID('Employe01'), '500'))

insert into employees (name,salary_crypt) values( 'Employe02', ENCRYPTBYKEY(KEY_GUID('Employe02'), '1000'))

close all symmetric keys

 

when i open symmetric key Employe01 I only see data which is encrypted by Employe01

i expect that Employe01 can also see the data which is encrypted by Employe02  because i already add symmetric key  Employe02 into symmetric Employe01

 

Kindly support to check where does it wrong

 


Friday, November 01, 2013 - 11:01:07 AM - Thanh Nguyen Back To Top

Hello Brian,

Thanks, it works as my expectation.

I have another question

Table Employee

Employee_ID; Empoyee_Name

VNXXXX; Thanh

Table Salary

Employee_ID; Year; Salary

VNXXXX; 2012;10

VNXXXX;2013;20

 

How to make the report like this with year is parameter

Emloyee PreviouseYear Salary; CurrentYear, Salary

VNXXXX 2012 10 2013 20

where curent year is 2013

 

 


Tuesday, October 29, 2013 - 11:59:09 AM - K. Brian Kelley Back To Top

There is an option if you create an ASYMMETRIC KEY that your director can access. You can use ALTER SYMMETRIC KEY to add an encryption mechanism without removing or altering the previous encryption method (such as by password). If you do that, he can open the asymmetric key and then query by using DecryptByKeyAutoAsmKey() as shown in the view creation. For more info, see ALTER SYMMETRIC KEY in Books Online:

 

http://technet.microsoft.com/en-us/library/ms189440(v=sql.100).aspx

 


Tuesday, October 29, 2013 - 11:24:36 AM - K. Brian Kelley Back To Top

He can't. The way you created the symmetric keys, with passwords as the encryption mechanism, means you have to know the passwords. Your director would need to know the passwords for each symmetric key and he/she would have to open the symmetric key by decrypting using the appropriate password.

 


Tuesday, October 29, 2013 - 10:54:37 AM - Thanh Nguyen Back To Top

I was using this version of SQL

 

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


Tuesday, October 29, 2013 - 10:39:53 AM - Thanh Nguyen Back To Top

Thanks for prompt reply

but each department has their own key

for example:

CREATE SYMMETRIC KEY HumanResource
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'Abcdefgf' --- password set by Human Resourcre Manager

 

CREATE SYMMETRIC KEY Accountant
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = '123456789' --- password set by Accountant Manager

 

INSERT INTO employeessn
VALUES ('David', ENCRYPTBYKEY(KEY_GUID('HumanResource'), '$10'))

INSERT INTO employeessn
VALUES ('Thanh', ENCRYPTBYKEY(KEY_GUID('Accountant'), '$20'))

 

how my director can decrypt with his own key (password) ???


Tuesday, October 29, 2013 - 10:11:35 AM - K. Brian Kelley Back To Top

If both symmetric keys are encrypted using the same asymmetric key, you can use the technique described in this article using DecryptByKeyAutoAsmKey(). If not, he'll need to open both symmetric keys and then he can query the data using DecryptByKey().


Tuesday, October 29, 2013 - 9:49:28 AM - Thanh Nguyen Back To Top

Thanks for the tip.

Here's my situation

Department A with SYMMETRIC KEY A

they can enter with encrypt/decrypt salary for department A but can't decrypt salary data of department B

Department B with SYMMETRIC KEY B

they can enter with encrypt/decrypt salary for department B but  can't decrypt salary data of department A

how can my my director with masterkey, he can decrypt salary data of all departments



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools