SQL Server Always Encrypted - Serving applications using Azure Key Vault and Certificate Store

Here is my second video related to Always Encrypted. This video discusses the way of configuring CMK for serving multiple applications hosted in multiple places, using Local Key Store - Certificate Store and Centralized Key Store - Azure Key Vault.

Here are the codes related to the video. I create a table in my Azure SQL Database, this is the code for it;

CREATE TABLE dbo.Message
(
 MessageId int identity(1,1) primary key
 , MessageCode char(5) COLLATE Latin1_General_BIN2 not null 
 , Message varchar(4000)  COLLATE Latin1_General_BIN2 not null
);
GO

INSERT INTO dbo.Message (MessageCode, Message)
 VALUES ('AA56B', 'This is a test message');
GO

CREATE OR ALTER PROCEDURE dbo.AddMessage @MessageCode char(5)
  , @Message varchar(4000)
AS
BEGIN

 INSERT INTO dbo.Message
  (MessageCode, Message)
 VALUES
  (@MessageCode, @Message);
END
GO


CREATE OR ALTER PROCEDURE dbo.GetMessage @MessageCode char(5)
   , @Message varchar(4000) OUTPUT
AS
BEGIN

 SELECT @Message = Message 
 FROM dbo.Message
 WHERE @MessageCode = MessageCode;
END
GO

And then, I have a .Net Application that accesses Azure Key Vault for taking the CMK and inserting and updating records. Here is the code of it. 

private static ClientCredential _clientCredential;

static void InitializeAzureKeyVaultProvider()
{
    _clientCredential = new ClientCredential("9e67ee1f-50ef-49d1-8ee0-0c48eaf4457b", "HjRqkx7BKLP7Lu+UYgTa5D/zCKAdxx3YITQ6fRrsQTQ=");

    SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
        new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

    Dictionary providers =
        new Dictionary();

    providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
    SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
}

public async static Task GetToken(string authority, string resource, string scope)
{
    var authContext = new AuthenticationContext(authority);
    AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);

    if (result == null)
        throw new InvalidOperationException("Failed to obtain the access token");
    return result.AccessToken;
}

public Form1()
{
    InitializeComponent();
    InitializeAzureKeyVaultProvider();

}

private void buttonSubmit_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection("Password=Pa$$w0rd;Persist Security Info=True;User ID=Dinesh;Initial Catalog=Marketing;Data Source=dinesqlserver.database.windows.net;Column Encryption Setting = Enabled");
    SqlCommand command = new SqlCommand("AddMessage", connection);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter parameterMessageCode = new SqlParameter("MessageCode", SqlDbType.Char, 5);
    parameterMessageCode.Value = textBoxMessageCode.Text;

    SqlParameter parameterMessage = new SqlParameter("Message", SqlDbType.VarChar, 4000);
    parameterMessage.Value = textBoxMessage.Text;

    command.Parameters.Add(parameterMessageCode);
    command.Parameters.Add(parameterMessage);

    connection.Open();
    command.ExecuteScalar();
    connection.Close();

}

private void buttonGet_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection("Password=Pa$$w0rd;Persist Security Info=True;User ID=Dinesh;Initial Catalog=Marketing;Data Source=dinesqlserver.database.windows.net; Column Encryption Setting = Enabled");
    SqlCommand command = new SqlCommand("GetMessage", connection);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter parameterMessageCode = new SqlParameter("MessageCode", SqlDbType.Char, 5);
    parameterMessageCode.Value = textBoxMessageCode.Text;

    SqlParameter parameterMessage = new SqlParameter("Message", SqlDbType.VarChar, 4000);
    parameterMessage.Direction = ParameterDirection.Output;

    command.Parameters.Add(parameterMessageCode);
    command.Parameters.Add(parameterMessage);

    connection.Open();
    command.ExecuteScalar();
    connection.Close();

    MessageBox.Show(parameterMessage.Value.ToString());
}

You can find more information and standard codes related to Azure Key Vault usage with Always Encrypted at: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault

SQL Server - Adding an Authenticator when encrypting data

Among multiple methods given for securing data stored in the SQL Server database, even though the latest one which is Always Encrypted is available, we still use Keys. Passphrases and Certificates for encrypting data. When keys such as Symmetric Keys or Asymmetric Keys, or Passphrases are used for encrypting data, an additional parameter can be supplied which is called Authenticator. Since I recently used this for one of my database solutions, thought to make a note on it.

What is the usage of Authenticator? Why we should use it. Let's take an example and understand with it.

The following code creates a database and a table that holds Customers. The Security Code of the customer will be encrypted.

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = AES_128  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

-- Creating a table that holds customers
-- Note the Securiry Code Column, it is varbinary 
-- because code will be encrypted and stored
CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , SecurityCode varbinary(256) NOT NULL
);
GO

Let's insert some records.

OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ;
GO

INSERT INTO dbo.Customer
 (Name, SecurityCode)
VALUES
 ('Dinesh', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ'))

INSERT INTO dbo.Customer
 (Name, SecurityCode)
VALUES
 ('Yeshan', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3'))

Once inserted, data will be looked like below;

And if I try to retrieve records, I need to decrypt encrypted values;

So far so good. Now let's try understand the usage of Authenticator. Assume that Yeshan needs to access some Securables that can be accessed only by Dinesh through an application. For that, all Yeshan needs is, log in to the application using Dinesh's Security Code. Since he does not know Dinesh's Security Code, one way of accessing the application using Dinesh's account is, replacing the Dinesh's code with his code. Let's see whether it is possible.

The following code updates Dinesh's account with Yeshan's code. And as you see, it gets updated and now Yeshan can use Dinesh account as he knows the code.

This should not be allowed and even if it is possible, what if we make sure that encrypted code cannot be replaced like that. That is what we can do with the Authenticator.

Look at the following code. It passes two additional values for encrypting. The third one which is 1 says that this has an Authenticator. The forth parameter is the data from which to derive an Authenticator.

OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ;
GO

-- Update Security codes with CustomerId as the Authenticator
UPDATE dbo.Customer
 SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ', 1, Convert(varbinary(256), CustomerId))
WHERE CustomerId = 1;

UPDATE dbo.Customer
 SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3', 1, Convert(varbinary(256), CustomerId))
WHERE CustomerId = 2;

Just like the previous code, values are encrypted now. However, if Yeshan tried to do the same, see the result;

As you see, even though Dinesh's code has been replaced with Yeshan's code, when try to decrypt value of Dinesh, it results null because Authenticator is different. This is the usage of the Authenticator.

Note that we used CustomerId as the Authenticator but you can use something else, something uncommon as the Authenticator to make it more secured and avoid malicious activities like this.

Incorrect syntax near 'TRIPLE_DES'. - SQL Server throws an error when try to use algorithms

Assume that you use SQL Server 2016 and trying to create a Symmetric Key or Asymmetric Key for encrypting data. If you try use an algorithm like TRIPLE_DES, you will get the mentioned error;

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near 'TRIPLE_DES'.

Here is a sample code for seeing this error;

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
-- However, this will throw an error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Now, what is the reason for this. The reason for this is, this algorithm is deprecated in SQL Server 2016. Not only that, All Algorithms except AES_128, AES_192, and AES_256 are deprecated in SQL Server 2016. 

What if you still need one of these deprecated algorithms? Yes, it is possible, but you need to downgrade the database by changing the Compatibility Level 120 or below.

This code shows the way of doing it.

USE master;
GO

-- Change the compatibility level to 120
ALTER DATABASE Sales
SET COMPATIBILITY_LEVEL = 120;

-- And check again
USE Sales;
GO

-- This will work now without any error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Even though you can use the algorithm after changing the Compatibility Level, remember lower Compatibility Level might not let you to use all functionalities available with SQL Server 2016. Therefore, if possible, use allowed algorithms only.