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.
|
1 2 3 4 5 |
USE AdventureWorks2014 GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CLE123' GO |
Then we create a certificate as follows.
|
1 2 3 4 5 |
USE AdventureWorks2014 GO CREATE CERTIFICATE CreditCardCertificate WITH SUBJECT = 'Credit Card Numbers'; GO |
Then we create our Symmetric Key as follows.
|
1 2 3 4 5 |
USE AdventureWorks2014 GO CREATE SYMMETRIC KEY CreditCardKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CreditCardCertificate ; |
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.
|
1 2 3 4 5 6 7 8 9 10 |
USE AdventureWorks2014 GO CREATE TABLE dbo.CreditCards ( ID int NOT NULL IDENTITY (1, 1), Name varchar(200) NOT NULL, Surname varchar(200) NOT NULL, CreditCardNo varbinary(128) NOT NULL ) ON [PRIMARY] GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @Key AS UNIQUEIDENTIFIER SET @Key = Key_GUID('CreditCardKey ') IF( @Key is not null ) BEGIN INSERT INTO [dbo].[CreditCards]([Name],[Surname],[CreditCardNo]) VALUES ( 'Nurullah','CAKIR', encryptbykey( @Key, N'1111-1111-1111-1111')) INSERT INTO [dbo].[CreditCards]([Name],[Surname],[CreditCardNo]) VALUES ( 'Faruk','ERDEM', encryptbykey( @Key, N'2222-2222-2222-2222')) INSERT INTO [dbo].[CreditCards]([Name],[Surname],[CreditCardNo]) VALUES ( 'Dilara','AYDIN', encryptbykey( @Key, N'3333-3333-3333-3333')) END ELSE BEGIN PRINT 'Symmetric was not found..' END |
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.
|
1 2 |
OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCertificate; |
You should put the following script at the end of the query.
|
1 |
CLOSE SYMMETRIC KEY CreditCardKey; |
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.
|
1 2 3 4 5 |
OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCertificate; SELECT ID,Name,Surname, CONVERT(NVARCHAR, DecryptByKey(CreditCardNo)) as 'Credit Card Numbers' FROM [AdventureWorks2014].[dbo].[CreditCards] CLOSE SYMMETRIC KEY CreditCardKey; |
As you can see below, our data will come in their original state.
