Column Level Encryption On SQL Server

 

In SQL Server, sometimes you do not want anyone to see the contents of an important column in an important table.

In such a case you can make this column encrypted.

To make the column encrypted, you must first create a master key in the database with the following script.

Since our example is for testing purposes, we gave it a simple password.

When applying for production, you must provide a more complex password.

 

Then we create a certificate as follows.

 

Then we create our Symmetric Key as follows.

 

We create the table to be encrypted as follows.

In this table we will encrypt the CreaditCardNo column.

So we set the data type varbinary. The reason for this is that the return type of the ENCRYPTBYKEY function is varbinary.

 

Now let’s try to enter data into this table.

If you use the following script, it will fail because you have not opened the symmetric key you have defined.

The error message you get will look like this:

Msg 515, Level 16, State 2, Line 21

Cannot insert the value NULL into column ‘CreditCardNo’, table ‘AdventureWorks2014.dbo.CreditCards’; column does not allow nulls. INSERT fails.

 

You should put the following script at the beginning of the query.

 

You should put the following script at the end of the query.

 

So we added our records.

You will see that our data is encrypted when we take a SELECT to the table as follows.

 

How do we get there when we want to reach the unencrypted state of these columns?

We can decrypt the encrypted data by using the following script.

 

As you can see below, our data will come in their original state.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *