Article sections
1 – Create an SQL Server Database Master Key
The first step is to create a database master key. This is accomplished using the CREATE MASTER KEY method. The “encrypt by password” argument is required and defines the password used to encrypt the key. The Database Master Key (DMK) does not directly encrypt data, but provides the ability to create keys that are used for data encryption. It is important that you keep the encryption password in a safe place and/or keep backups of your SQL Server Database Master Key.
Query
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘23987hxJKL95QYV4369#ghf0%lekjg5k3fd1’
2 – Create a Self Signed SQL Server Certificate
The next step is to create a self-signed certificate that is protected by the database master key. An optional argument when creating a certificate is ENCRYPTION BY PASSWORD. This argument defines a password protection method of the certificate’s private key. In our creation of the certificate we have chosen to not include this argument; by doing so we are specifying that the certificate is to be protected by the database master key.
Query
CREATE CERTIFICATE PANCert WITH SUBJECT = ‘CARD COLUMN ENCRYPTION’
3 – Create an SQL Server Symmetric Key
A symmetric key uses the same cryptographic keys for both encryption of plaintext and decryption of ciphertext. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database
Query
CREATE SYMMETRIC KEY PANKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE PANCert
4 – Create a column to store the encrypted data
An Encrypted column can only be of datatype varbinary and since the column we want to encrypt is of datatype varchar, we have to create a new column and populate it with encrypted values.
Query
ALTER TABLE TBLINFO ADD XPAN VARBINARY(256)
5 – Apply encryption to new column
To encrypt the data we will use the ENCRYPTBYKEY command. Note that the symmetric key needs to be opened before we can encrypt the data and be sure you manually close the key else it will remain open for the current session.
Query
–-Open Symmetric Key to encrypt data
OPEN SYMMETRIC KEY PANKey DECRYPTION BY CERTIFICATE PANCert
–-Encrypt values in the CARD_NO column using the symmetric key PANKey and save the result in the XPAN column
UPDATE TBLINFO SET XPAN = ENCRYPTBYKEY(KEY_GUID(‘PanKey’),CARD_NO)
6 – Decrypt the Encrypted Column
Query
OPEN SYMMETRIC KEY PANKey DECRYPTION BY CERTIFICATE PANCert
SELECT CUST_NAME, CARD_NO, CONVERT(VARCHAR(50), DECRYPTBYKEY(XPAN)) AS CARD_DETAIL FROM TBLINFO
7 – Remove Plain Text Column
To finalize the process, remove the plain-text card number column so that the table is left with only the encrypted data
Query
ALTER TABLE TBLINFO DROP COLUMN CARD_NO
