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); Dictionaryproviders = 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
No comments:
Post a Comment