Azure SQL database column encryption

Azure SQL provides an option to encrypt your columns if you are storing any sensitive data in the database such as user passwords, credit card numbers, etc.

You can use any encryption algorithm to encrypt the data and only authentic users can decrypt this data.

In this article, you will learn how to encrypt the columns of a table in an Azure SQL database with the help of an encryption algorithm.

I will demonstrate how you can encrypt a column in your Azure SQL database with the help of an example. I have created a table named dbo.Login. This table has two columns:

1. Username: The usernames of the users
2. Password: Password of the users stored in plain text

We will add a new column in this table to store the user password in an encrypted format.

• Create master key in Azure SQL
• Azure SQL Server column level encryption

Create master key in Azure SQL

• Firstly, I will create a master key in our master database. You might be wondering what is a master key.
• A master key is an encrypted key that we use to protect the other encrypted keys used in the database.
• For example, if have encrypted some certificates in the database, I have a key to encrypt or decrypt the certificates. That key is encypted with the help of master key.
• In simple words, a key protects the certificates and that key is protected by the master key.
• We create a master key with the CREATE MASTER KEY statement. We have to supply a password to create the master key.
• Also, the password must follow the password policy for creating any password in Azure. I am using a simple password. But you should specify a strong password. Using a password generator to create a strong password is a good practice.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Test@123'
GO

Also, read, How to create SQL authentication user in Azure SQL database

Azure SQL Server column level encryption

Once you have created the master key, you can proceed further to encrypt the column in a table. The encryption process is explained below:

• In the Azure portal, open your database and open the Query Editor window.
• Firstly, you have to create a certificate. Execute the below query to create a certificate with the name LoginCertificate and a subject.

CREATE CERTIFICATE LoginCertificate 
   WITH SUBJECT = 'User passwords in the database';  
GO 

• The next step is to create a symmetric key that you will need to encypt or decrypt the column values. You can choose one of the commonly used encryption algorithms like AES_128, AES_192, AES_256, etc.
• Also, you have to use the certificate for authentication.

CREATE SYMMETRIC KEY PasswordKey  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE LoginCertificate;
GO

• Now we will add a column in our dbo.Login table into which we will store all the encrypted values of the Password column.

ALTER TABLE dbo.Login  
    ADD EncryptedPassword varbinary(128);   
GO

• Once you have added the new column, it is the time to encrypt the column values.
• Open the symmetric key with the OPEN SYMMETRIC KEY statement and then update the column values in the new column.

OPEN SYMMETRIC KEY PasswordKey  
   DECRYPTION BY CERTIFICATE LoginCertificate;  

  
UPDATE dbo.Login  
SET EncryptedPassword= EncryptByKey(Key_GUID('PasswordKey'), Password);  
GO  

• Now let us see if the column values are updated or not. Execute the SELECT statement for the table:

SELECT TOP(1000) * FROM dbo.Login

• You can see that there is a new column in which encrypted values are stored.
• Now let us decrypt these values and compare with the plain text values and check if they are same.

OPEN SYMMETRIC KEY PasswordKey  
   DECRYPTION BY CERTIFICATE LoginCertificate;  
GO  
SELECT Password, EncryptedPassword   
    AS 'Encrypted Password',  
    CONVERT(nvarchar, DecryptByKey(EncryptedPassword))   
    AS 'Decrypted Password'  
    FROM dbo.Login;  
GO

• You can clearly see that the the original password values and the decrypted password values are same showing the encryption and the decryption process.

Thus, in this way you can secure your sensitive data by encrypting it with any encryption algorithm. Only the person knowing about the certificate and the encryption key will have the access to the encrypted columns.

• Create master key in Azure SQL
• Azure SQL Server column level encryption

Related Posts:

• Reset Password Azure SQL database
• Azure sql password validation failed
• Bulk loading data to Azure SQL
• Migrate SQL Server to Azure SQL database
• Configuring email notifications in Azure SQL database
• Create stored procedures in Azure SQL database