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:
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:
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:
However, there are three issues that cannot be overcome:
- DBAs grant access to the view. They themselves would be able to query the data through the view.
- 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.
- 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
- Understand why we use symmetric key algorithms to encrypt data over asymmetric keys.
- Review the article on how to protect encrypted data from DBAs.
- Know which symmetric key algorithms are available to you and which one you should choose.
- Learn why the key length is important with respect to the asymmetric key algorithm.
