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;

view plainprint?

1. CREATE TABLE dbo.Message  
2. (  
3.  MessageId int identity(1,1) primary key  
4.  , MessageCode char(5) COLLATE Latin1_General_BIN2 not null   
5.  , Message varchar(4000)  COLLATE Latin1_General_BIN2 not null  
6. );  
7. GO  
8.   
9. INSERT INTO dbo.Message (MessageCode, Message)  
10.  VALUES ('AA56B', 'This is a test message');  
11. GO  
12.   
13. CREATE OR ALTER PROCEDURE dbo.AddMessage @MessageCode char(5)  
14.   , @Message varchar(4000)  
15. AS  
16. BEGIN  
17.   
18.  INSERT INTO dbo.Message  
19.   (MessageCode, Message)  
20.  VALUES  
21.   (@MessageCode, @Message);  
22. END  
23. GO  
24.   
25.   
26. CREATE OR ALTER PROCEDURE dbo.GetMessage @MessageCode char(5)  
27.    , @Message varchar(4000) OUTPUT  
28. AS  
29. BEGIN  
30.   
31.  SELECT @Message = Message   
32.  FROM dbo.Message  
33.  WHERE @MessageCode = MessageCode;  
34. END  
35. GO  

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. 

view plainprint?

1. private static ClientCredential _clientCredential;  
2.   
3. static void InitializeAzureKeyVaultProvider()  
4. {  
5.     _clientCredential = new ClientCredential("9e67ee1f-50ef-49d1-8ee0-0c48eaf4457b", "HjRqkx7BKLP7Lu+UYgTa5D/zCKAdxx3YITQ6fRrsQTQ=");  
6.   
7.     SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =  
8.         new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);  
9.   
10.     Dictionary<string sqlcolumnencryptionkeystoreprovider=""> providers =  
11.         new Dictionary<string sqlcolumnencryptionkeystoreprovider="">();  
12.   
13.     providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);  
14.     SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);  
15. }  
16.   
17. public async static Task<string> GetToken(string authority, string resource, string scope)  
18. {  
19.     var authContext = new AuthenticationContext(authority);  
20.     AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);  
21.   
22.     if (result == null)  
23.         throw new InvalidOperationException("Failed to obtain the access token");  
24.     return result.AccessToken;  
25. }  
26.   
27. public Form1()  
28. {  
29.     InitializeComponent();  
30.     InitializeAzureKeyVaultProvider();  
31.   
32. }  
33.   
34. private void buttonSubmit_Click(object sender, EventArgs e)  
35. {  
36.     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");  
37.     SqlCommand command = new SqlCommand("AddMessage", connection);  
38.     command.CommandType = CommandType.StoredProcedure;  
39.   
40.     SqlParameter parameterMessageCode = new SqlParameter("MessageCode", SqlDbType.Char, 5);  
41.     parameterMessageCode.Value = textBoxMessageCode.Text;  
42.   
43.     SqlParameter parameterMessage = new SqlParameter("Message", SqlDbType.VarChar, 4000);  
44.     parameterMessage.Value = textBoxMessage.Text;  
45.   
46.     command.Parameters.Add(parameterMessageCode);  
47.     command.Parameters.Add(parameterMessage);  
48.   
49.     connection.Open();  
50.     command.ExecuteScalar();  
51.     connection.Close();  
52.   
53. }  
54.   
55. private void buttonGet_Click(object sender, EventArgs e)  
56. {  
57.     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");  
58.     SqlCommand command = new SqlCommand("GetMessage", connection);  
59.     command.CommandType = CommandType.StoredProcedure;  
60.   
61.     SqlParameter parameterMessageCode = new SqlParameter("MessageCode", SqlDbType.Char, 5);  
62.     parameterMessageCode.Value = textBoxMessageCode.Text;  
63.   
64.     SqlParameter parameterMessage = new SqlParameter("Message", SqlDbType.VarChar, 4000);  
65.     parameterMessage.Direction = ParameterDirection.Output;  
66.   
67.     command.Parameters.Add(parameterMessageCode);  
68.     command.Parameters.Add(parameterMessage);  
69.   
70.     connection.Open();  
71.     command.ExecuteScalar();  
72.     connection.Close();  
73.   
74.     MessageBox.Show(parameterMessage.Value.ToString());  
75. }  
76. </string></string></string>  

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