By: K. Brian Kelley | Comments | Related: > Security
Problem
In our organization we have two teams that stored encrypted data in Microsoft SQL Server. The data is encrypted using symmetric keys and those keys are encrypted with passwords. However, we have a director who needs to be able to see the data for both teams, but we'd like it so that he only has to remember one password, preferably separate from the passwords used by the two teams. Is that possible?
Solution
Yes, it is. Microsoft SQL Server supports the ability for encryption keys to be encrypted via multiple mechanisms. For instance, a symmetric key can be encrypted with a password and an asymmetric key. If you have access to the symmetric key and at least one of those mechanisms, you can open the key. The best way to see this is via examples. Let's set up a database with the given scenario.
First, the database and the keys:
USE master;
GO
IF DB_ID('EncryptionDemo') IS NOT NULL
DROP DATABASE EncryptionDemo;
GO
CREATE DATABASE EncryptionDemo;
GO
USE EncryptionDemo;
GO
CREATE TABLE dbo.EncryptedData
(
EncryptedCol VARBINARY(128) NOT NULL
);
GO
CREATE SYMMETRIC KEY Team1Key
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'Team1sPassword!';
GO
CREATE SYMMETRIC KEY Team2Key
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'Test2sPassword!';
GO Now let's set up the users. Note that we're creating a user for each team as well as the director.
USE EncryptionDemo; GO CREATE USER Team1User WITHOUT LOGIN; GO CREATE USER Team2User WITHOUT LOGIN; GO CREATE USER Director WITHOUT LOGIN; GO CREATE ROLE Team1; GO CREATE ROLE Team2; GO CREATE ROLE TeamsDirector; GO EXEC sp_addrolemember @membername = 'Team1User', @rolename = 'Team1'; GO EXEC sp_addrolemember @membername = 'Team2User', @rolename = 'Team2'; GO EXEC sp_addrolemember @membername = 'Director', @rolename = 'TeamsDirector'; GO GRANT VIEW DEFINITION ON SYMMETRIC KEY::Team1Key TO Team1; GO GRANT VIEW DEFINITION ON SYMMETRIC KEY::Team2Key TO Team2; GO GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.EncryptedData TO Team1; GO GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.EncryptedData TO Team2; GO GRANT VIEW DEFINITION ON SYMMETRIC KEY::Team1Key TO TeamsDirector; GO GRANT VIEW DEFINITION ON SYMMETRIC KEY::Team2Key TO TeamsDirector; GO GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.EncryptedData TO TeamsDirector; GO
If you're wondering about the permissions, in order to be able to open a symmetric key, you must have at least VIEW DEFINITION permissions on that key. The other applicable permissions are ALTER and CONTROL, which we don't want to give out. That's why we're using VIEW DEFINITION.
Let's see the keys in action:
USE EncryptionDemo;
GO
-- Team 1 Data
EXECUTE AS USER = 'Team1User';
GO
OPEN SYMMETRIC KEY Team1Key
DECRYPTION BY PASSWORD = 'Team1sPassword!';
GO
INSERT INTO dbo.EncryptedData
(EncryptedCol)
VALUES
(ENCRYPTBYKEY(KEY_GUID('Team1Key'), 'Data only team 1 can see.'));
GO
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(EncryptedCol)) AS 'DecryptedCol'
FROM dbo.EncryptedData;
GO
CLOSE SYMMETRIC KEY Team1Key;
GO
REVERT;
GO
-- Team 2 Data
EXECUTE AS USER = 'Team2User';
GO
OPEN SYMMETRIC KEY Team2Key
DECRYPTION BY PASSWORD = 'Test2sPassword!';
GO
INSERT INTO dbo.EncryptedData
(EncryptedCol)
VALUES
(ENCRYPTBYKEY(KEY_GUID('Team2Key'), 'Data only team 2 can see.'));
GO
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(EncryptedCol)) AS 'DecryptedCol'
FROM dbo.EncryptedData;
GO
CLOSE SYMMETRIC KEY Team2Key;
GO
REVERT;
GO
-- Verifying data can't be seen with keys closed
SELECT EncryptedCol FROM dbo.EncryptedData;
GO
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(EncryptedCol)) AS 'DecryptedCol'
FROM dbo.EncryptedData;
-- Show Team 1 User Can't Open Team 2 Key and vice versa
EXECUTE AS USER = 'Team1User';
GO
OPEN SYMMETRIC KEY Team2Key
DECRYPTION BY PASSWORD = 'Test2sPassword!';
GO
REVERT;
GO
EXECUTE AS USER = 'Team2User';
GO
OPEN SYMMETRIC KEY Team1Key
DECRYPTION BY PASSWORD = 'Team1sPassword!';
GO
REVERT;
GO If you looked at the output carefully when Team2User ran the SELECT query, you noted that the first row was NULL. This is because the Team2User didn't open the Team1Key used to encrypt that row. SQL Server won't return an error, it'll just return NULL for that column in that row.
So what if Team2User wanted to see that data? Team2User would have to be able to open the Team1Key. Note, though, that if you don't have access to the key, you can't open it. This is important: that's why the TeamsDirector role was given view definition rights on the symmetric keys as well. You'll get an error indicating that either the key doesn't exist or the user doesn't have permission to access the key:
Now let's see how to make things work for the director.
USE EncryptionDemo;
GO
CREATE ASYMMETRIC KEY DirectorKey
WITH ALGORITHM =RSA_2048
ENCRYPTION BY PASSWORD = 'DirectorsPassword!';
GO
GRANT CONTROL ON ASYMMETRIC KEY::DirectorKey TO TeamsDirector;
GO
-- Opening the symmetric key is required before
-- altering it
OPEN SYMMETRIC KEY Team1Key
DECRYPTION BY PASSWORD = 'Team1sPassword!';
GO
ALTER SYMMETRIC KEY Team1Key
ADD ENCRYPTION BY ASYMMETRIC KEY DirectorKey;
CLOSE SYMMETRIC KEY Team1Key;
GO
OPEN SYMMETRIC KEY Team2Key
DECRYPTION BY PASSWORD = 'Test2sPassword!';
GO
ALTER SYMMETRIC KEY Team2Key
ADD ENCRYPTION BY ASYMMETRIC KEY DirectorKey;
CLOSE SYMMETRIC KEY Team2Key;
GO
EXECUTE AS USER = 'Director';
GO
SELECT CONVERT(VARCHAR, DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('DirectorKey'),
N'DirectorsPassword!', EncryptedCol)) AS 'DecryptedCol'
FROM dbo.EncryptedData;
GO
REVERT;
GO With DecryptByKeyAutoAsymKey(), the user only has to be able to specify the right asymmetric key and it's password. As long as the user has some sort of permission to the symmetric key (VIEW DEFINITION counts), then SQL Server will open the symmetric keys automatically, allowing the user to see the data.
One last thing to point out is that we had to give CONTROL permission against the asymmetric key. This isn't a mistake. While VIEW DEFINITION is okay with symmetric keys, you must have CONTROL for asymmetric keys, as per Books Online (see the Permissions section). If we used a certificate instead of an asymmetric key, the permission required is still the same: CONTROL.
Next Steps
- Review why we choose symmetric keys over asymmetries to encrypt data.
- Understand what symmetric key algorithms are safe to use.
- Know what the minimum key length is for asymmetric algorithms.
- See how you can use views to reveal encrypted data without the end user knowing the passwords.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
