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); } }