private ColumnEncryptionKey CreateColumnEncryptionKey(string testRunId, ColumnMasterKey cmk)
{
ColumnEncryptionKey columnEncryptionKey = new ColumnEncryptionKey
(
keyName: $"MicrosoftDataEncryptionTest_CEK_{testRunId}",
columnMasterKey: cmk,
encryptedValue: "0x01CE000001680074007400700073003A002F002F006A0065007400720069006D006D0065002D006B00650079002D007600610075006C0074002E007600610075006C0074002E0061007A007500720065002E006E00650074002F006B006500790073002F0061006C0077006100790073002D0065006E0063007200790070007400650064002D006100750074006F0031002F0061003700640035006300390038003900640035006100300034003200330066003800640033003500360066003100390035003500330036006500350039003000A4E7182E3CEF4C4A29F287D672868659FF9367C579ADA8D3367BC2804738DA1A0CA8D836B4CEA5D940E0DD7DACFD453BD70793A23E725C50E1CB538ECDCE2B660A9482CBF561B2B7B5BBAC18074145EEAB8C8DE38A0B1297413C1C5411D88602A46BE58854DFD21915FC408BD7C36A3C6A883B20D50FDC18A5800059EBD4515BDDD4B9AC3D065183740651B2DCBA43C9293C64C0F67E6DA4F14A1BC1D5760CF32EA8B0B0AD474BE3E73864B8DA4E9A53070651A9106683B5D7DCC0D01D3F53CE1E4C558CD5E466F0E713201F6327CFA06EB968E2FC2186295A1E072371C8E461928877AF3360D4DEB27114CF37BE82573C4DFF0CBD96705478D09735DFA58305128A1AF38DA9D0A95A3B2A374FB48FE927D23436754D0931277DE513E8022E4EAA55A3991EA932958A8F34D6D02B6F7A4217835E2B10A9109F953C970C5FEB728B43AB03999CE5C6144D386E7BE6BE71287D660DCE0C81DA37D7FE3EE1AE2EE40D78B0B38EE9BA6B2E366979F88547B1036EBFCB8C07756F446CFF6C523F457AF081DE50215C9B68C548E11B864E690FB64927C10D8F23328DDB663399154E4A314CB453172F7F39D36ACF7B7F5B6B6BDBEEC52E8D79E7971FF3CA4D3461272B61249642861EA484819958423CED8705A69ED671BF05E071743B800D97C2395684DD53D09AA359887E39E48EE5AF25F69D79E85C35586D8730FEC03931D05F85"
);
columnEncryptionKey.Create(SqlConnectionAE);
DatabaseObjects.Add(columnEncryptionKey);
return(columnEncryptionKey);
}
public void CreateAndDropColumnEncryptionKey()
{
string cmkName = nameof(CreateAndDropColumnEncryptionKey);
string keyPath = "CurrentUser/My/BBF037EC4A133ADCA89FFAEC16CA5BFA8878FB94";
ColumnMasterKey columnMasterKey = new ColumnMasterKey(cmkName, KeyStoreProvider.WindowsCertificateStoreProvider, keyPath);
string cekName = nameof(CreateAndDropColumnEncryptionKey);
ColumnEncryptionKey columnEncryptionKey = new ColumnEncryptionKey(cekName, columnMasterKey, "0x555");
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should not exist in the database.");
Assert.False(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should not exist in the database.");
columnMasterKey.Create(sqlConnection);
columnEncryptionKey.Create(sqlConnection);
Assert.True(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should exist in the database.");
Assert.True(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should exist in the database.");
using (SqlCommand command = sqlConnection.CreateCommand())
{
command.CommandText = $@"
SELECT cmk.name, v.encrypted_value
FROM sys.column_encryption_keys cek JOIN sys.column_encryption_key_values v
ON (cek.column_encryption_key_id = v.column_encryption_key_id)
JOIN sys.column_master_keys cmk
ON (cmk.column_master_key_id = v.column_master_key_id)
WHERE cek.name = 'CreateAndDropColumnEncryptionKey'";
using (SqlDataReader reader = command.ExecuteReader())
{
Assert.True(reader.HasRows, "The sql query should have returned at least one row.");
while (reader.Read())
{
Assert.Equal(columnEncryptionKey.ColumnMasterKeyName, reader.GetString(0));
Assert.NotNull(reader.GetValue(1));
}
}
}
columnEncryptionKey.Drop(sqlConnection);
columnMasterKey.Drop(sqlConnection);
Assert.False(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should not exist in the database.");
Assert.False(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should not exist in the database.");
}
}
public void AddColumnEncryptionCorrectly()
{
string tableName = nameof(AddColumnEncryptionCorrectly);
string columnName1 = tableName + "Column1";
string columnName2 = tableName + "Column2";
string columnMasterKeyName = tableName + "_CMK";
string columnEncryptionName = tableName + "_CEK";
ColumnMasterKey columnMasterKey = new ColumnMasterKey(columnMasterKeyName, KeyStoreProvider.AzureKeyVaultProvider, "Test/Path");
ColumnEncryptionKey columnEncryptionKey = new ColumnEncryptionKey(columnEncryptionName, columnMasterKey.Name, "0x555");
ColumnEncryption columnEncryption1 = new ColumnEncryption(columnEncryptionKey, ColumnEncryptionType.Deterministic);
Column column1 = new Column(columnName1, DataType.Char())
{
ColumnEncryption = columnEncryption1,
Collation = "Latin1_General_BIN2"
};
ColumnEncryption columnEncryption2 = new ColumnEncryption(columnEncryptionKey, ColumnEncryptionType.Randomized);
Column column2 = new Column(columnName2, DataType.NVarChar())
{
ColumnEncryption = columnEncryption2
};
Table table = new Table(tableName);
table.Columns.AddAll(column1, column2);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should not exist in the database.");
columnMasterKey.Create(sqlConnection);
Assert.True(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should exist in the database.");
Assert.False(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should not exist in the database.");
columnEncryptionKey.Create(sqlConnection);
Assert.True(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should exist in the database.");
Assert.False(table.IsTablePresentInDatabase(sqlConnection), "Table should not exist in the database.");
table.Create(sqlConnection);
Assert.True(table.IsTablePresentInDatabase(sqlConnection), "Table should exist in the database.");
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
foreach (Column column in table.Columns)
{
string sql = $@"
Select c.encryption_type_desc, k.name
FROM sys.columns c JOIN sys.column_encryption_keys k ON (c.column_encryption_key_id = k.column_encryption_key_id)
WHERE c.name = '{column.Name}'";
sqlCommand.CommandText = sql;
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
Assert.Equal(column.ColumnEncryption.ColumnEncryptionType.GetStringValue(), reader.GetString(0));
Assert.Equal(column.ColumnEncryption.ColumnEncryptionKeyName, reader.GetString(1));
}
}
}
}
table.Drop(sqlConnection);
columnEncryptionKey.Drop(sqlConnection);
columnMasterKey.Drop(sqlConnection);
}
}