By: K. Brian Kelley | Updated: 2009-05-27 | Comments | Related: > Encryption
Problem
I want to use SQL Server-based encryption, but I want some additional "protection" so that even if someone has access to the keys, they can't arbitrarily decrypt the data. I've read about authenticators but am not sure how to use them. How do they work?
Solution
Authenticators are additional data that gets encrypted along with the data to be stored in an encrypted manner. When it comes to decrypt the data, if the right authenticator isn't specified, SQL Server doesn't return the data in the decrypted form. Rather, a NULL value is returned, just as if the wrong key was used. For instance, consider the case with storing credit card numbers. We can use the 3 digit security code as the authenticator. If the proper security code isn't passed in when the credit card is retrieved, the credit card number can't be retrieved.
The first thing we need to do is make sure we have a master key, certificate and symmetric key for this process. The code below will create these in the database you are testing this in:
-- Create database master key which will be used to encrypt the certificate private key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeStr0ngPassw0rd!'; GO -- Create the certificate which will be used to encrypt the symmetric key CREATE CERTIFICATE EncryptKeyForCreditCard WITH SUBJECT = 'Certificate Used to Encrypt Symmetric Key for credit card number handling'; GO -- Create the symmetric key CREATE SYMMETRIC KEY CreditCardEncryptionKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE EncryptKeyForCreditCard; GO
The script below creates a table and a stored procedure to help illustrate this.
/* Create the table to store credit card information
Normally we'd have a foreign key for CustomerID referring to the
Customer table. However, we won't create such for this example. */
CREATE TABLE dbo.CreditCard (
CreditCardID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerID INT NOT NULL,
CardFriendlyName VARCHAR(50) NOT NULL,
NameOnCard VARCHAR(100) NOT NULL,
ExpirationDate SMALLDATETIME NOT NULL,
CardNumber VARBINARY(128) NOT NULL
);
GO
CREATE PROC dbo.InsertCard
@CustomerID INT,
@CardFriendlyName VARCHAR(50),
@NameOnCard VARCHAR(100),
@ExpirationDate SMALLDATETIME,
@CardNumber CHAR(16),
@SecurityCode CHAR(3)
AS
BEGIN
OPEN SYMMETRIC KEY CreditCardEncryptionKey DECRYPTION BY CERTIFICATE EncryptKeyForCreditCard;
INSERT INTO dbo.CreditCard
(CustomerID, CardFriendlyName, NameOnCard, ExpirationDate, CardNumber)
VALUES
(@CustomerID, @CardFriendlyName, @NameOnCard, @ExpirationDate,
EncryptByKey(Key_GUID('CreditCardEncryptionKey'), @CardNumber, 1, @SecurityCode) );
CLOSE SYMMETRIC KEY CreditCardEncryptionKey;
END;
GO
CREATE PROC dbo.RetrieveCard
@CustomerID INT,
@CardFriendlyName VARCHAR(50),
@SecurityCode CHAR(3)
AS
BEGIN
SELECT NameOnCard, ExpirationDate,
CONVERT(CHAR(16), DecryptByKeyAutoCert(cert_ID('EncryptKeyForCreditCard'), NULL, CardNumber, 1, @SecurityCode)) AS CardNumber
FROM dbo.CreditCard
WHERE CustomerID = @CustomerID
AND CardFriendlyName = @CardFriendlyName;
END;
GO
When the credit card number is stored in the CreditCard table, it will be stored in an encrypted format. When we go to store it, if we use @SecurityCode as the authenticator, it will need to be used as an authenticator to get the credit card number back in a plaintext (unencrypted) format. So when we specify the EncryptByKey() function, we'll be sure to use the parameter that tells that function we're going to use an authenticator and then pass the @SecurityCode parameter as the authenticator. When it comes time to retrieve the card, we'll use the DecryptByKeyAutoCert() function because we just need to specify the certificate and it'll automatically decrypt the symmetric key and then access the data. Again, we'll specify the parameter to indicate an authenticator is being used and we'll pass the @SecurityCode parameter as the authenticator. If it's correct, the credit card number will be returned, otherwise SQL Server will return a NULL.
Let's look at an example.
EXEC dbo.InsertCard 1, 'MyCard', 'John Doe', '20120601', '1111222233334444', '888';
The authenticator being used is '888' and if anything else is specified, the credit card number won't be returned. For instance:
EXEC dbo.RetrieveCard 1, 'MyCard', '777';
Returns the following:
But if we specify the right value for @SecurityCode:
EXEC dbo.RetrieveCard 1, 'MyCard', '888';
We get the credit card number back:
And in this case we see that the correct authenticator makes all the difference. Therefore, if an attacker were able to get the database, even if he or she were able to access the keys, without the authenticators, the data will not be successfully retrieved.
Next Steps
- Download the scripts
- Take a look at these other tips that are related to certificates and keys
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
Article Last Updated: 2009-05-27
