Permissions for certificates and symmetric keys

I’ve taken this example from BOL and worked it a bit to get a desirable solution…

The code is the first code block ¬†from this URL…¬†http://technet.microsoft.com/en-us/library/ms179331(v=sql.100).aspx

Here is the first code block… (This is using AdventureWork2008)

USE AdventureWorks;
GO

--If there is no master key, create one now. 
IF NOT EXISTS 
 (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
 CREATE MASTER KEY ENCRYPTION BY 
 PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
GO

CREATE CERTIFICATE HumanResources037
 WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
 WITH ALGORITHM = AES_256
 ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
 ADD EncryptedNationalIDNumber varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

One thing that is missing here is the CLOSE statement…

CLOSE SYMMETRIC KEY SSN_Key_01

Now, the next block of code, wrap this in a stored procedure like so…

CREATE PROC dbo.myEncryptedProc AS 
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;


-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber 
 AS 'Encrypted ID Number',
 CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
 AS 'Decrypted ID Number'
 FROM HumanResources.Employee;

CLOSE SYMMETRIC KEY SSN_Key_01
GO

We need a user to test this with so create a SQL login and call it mySQLUser

CREATE LOGIN mySQLUser WITH PASSWORD = 'Pa$$w0rd1', DEFAULT_DATABASE = AdventureWorks2008
USE AdventureWorks2008
CREATE USER mySQLUser FROM LOGIN mySQLUser
EXEC sp_addrolemember db_datareader, mySQLUser
EXEC sp_addrolemember db_datawriter, mySQLUser
GRANT EXEC ON dbo.myEncryptedProc TO mySQLUser

Open a new connection running as mySQLUser and call the stored proc and see what happens.

 

You need to grant additional permissions on the key and the certificate. The minimal permissions required are:

GRANT REFERENCES ON SYMMETRIC KEY::SSN_Key_01 TO mySQLUser
GRANT CONTROL ON CERTIFICATE::HumanResources037 TO mySQLUser

Everything should work now.

 

This entry was posted in Encryption. Bookmark the permalink.