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
