Credit Card Encryption in SQL Server

SQL Server procedure to write code based on table columns. Useful for creating update procedures, recordset loaders, and form updates.

For customer orders, let's encrypt the credit card numbers and only store the last 4 digits un-enrypted. This will allow us to display card numbers as ************1234 without pulling back full numbers, but retains the full card number if there is ever a need to get to it.

I have created a certificate on the dev SQL server for this. To recreate the key on any new server, we will need to run my InitializeEncryption sproc one time. I used DES encryption since it will be supported on any SQL Server (some XP and Window 2000 servers do not support AES_256).


create proc dbo.InitializeEncryption AS

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = '23987hxJKL95QYV4369#ghf0%lohsek3fd117r$$#1946kcj$n44nwebdlj'


CREATE CERTIFICATE LohseCC
   WITH SUBJECT = 'Customer Credit Card Numbers'


CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE LohseCC;


GO

When we save credit card numbers to the database, we will save the last four, then call the UpdateCreditCardNumber sproc to store the full card number in an encrypted varbinary field.


CREATE PROC dbo.UpdateCreditCardNumber(@ID int, @CC varchar(20), @Last_Four varchar(10)) AS

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE LohseCC;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted. 
UPDATE Orders
SET Card_Number_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'), @CC, 1, HashBytes('SHA1', CONVERT( varbinary, @Last_Four)))
WHERE id = @ID

GO

For example, I did this:


exec UpdateCreditCardNumber @ID=1, @CC ='4856210012345678', @Last_Four = '5678'

To get full, unecrypted card numbers back out, we use my Decrypted_Credit_Card function (won't work if you don't open the key first, so see the next step)


CREATE FUNCTION dbo.Decrypted_Credit_Card (@CC varbinary(128), @key varchar(10))
returns varchar(20)
AS
BEGIN
-- Must open the symmetric key in a sproc before calling this function

declare @result varchar(20)

SELECT @result = CONVERT(varchar, DecryptByKey(@CC, 1 , HashBytes('SHA1', CONVERT(varbinary, @key))))

return @result
END

Now we can write sprocs to get the full card number back out.


ALTER PROC dbo.GetCustomerOrderPrivate (@id int) as

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE LohseCC;

SELECT card_number, dbo.Decrypted_Credit_Card(card_number_encrypted, card_number) as full_card_number FROM Orders WHERE id = @id and recordStatus = 1

For example,


EXEC GetCustomerOrderPrivate 1