SQL Server: Encrypt Column data using Symmetric Key Encryption

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

The Database Security is a very common and one of the major aspects for DBA to keep data secure.
If you don’t know about SQL Server TDE, please read below article which uses for data encryption in SQL Server.

SQL Server: Transparent Data Encryption (TDE) to Encrypt a Database

There are different ways to encrypt your data like TDE, data masking, symmetric key.

In this post, I am sharing a demonstration on how to encrypt your table column using Symmetric key encryption.
For example, When you are storing customer’s credit card information, you must encrypt all cards related columns.

Below is a full demonstration of this:

Create a table with sample data:

Create your master key and give password base on windows policy:

Create a database certificate which is protected by database master key:

Create a symmetric key which uses for encryption/decryption:
I used AES_256 algorithm.

Add one more column in a table:

Open a symmetric key for further use:

Update newly created column by generating encrypted data with use of old column:

Now, You can remove your old column:

SELECT encrypted/decrypted card numbers:

Try to Insert few records with encrypted card number values:

Once you complete with the use symmetric key, CLOSE it:

The result: