public void CreateAndDropColumnMasterKey()
{
string cmkName = nameof(CreateAndDropColumnMasterKey);
string keyPath = "CurrentUser/My/BBF037EC4A133ADCA89FFAEC16CA5BFA8878FB94";
ColumnMasterKey cmk = new ColumnMasterKey(cmkName, KeyStoreProvider.WindowsCertificateStoreProvider, keyPath);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(cmk.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should not exist in the database.");
cmk.Create(sqlConnection);
Assert.True(cmk.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should exist in the database.");
using (SqlCommand command = sqlConnection.CreateCommand())
{
command.CommandText = $"SELECT key_store_provider_name, key_path, allow_enclave_computations, signature FROM sys.column_master_keys WHERE name = '{nameof(CreateAndDropColumnMasterKey)}'";
using (SqlDataReader reader = command.ExecuteReader())
{
Assert.True(reader.HasRows, "The sql query should have returned at least one row.");
while (reader.Read())
{
Assert.Equal(cmk.KeyStoreProviderName, reader.GetString(0));
Assert.Equal(cmk.KeyPath, reader.GetString(1));
Assert.Equal(0, reader.GetInt32(2));
Assert.IsType <DBNull>(reader.GetValue(3));
}
}
}
cmk.Drop(sqlConnection);
Assert.False(cmk.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should not exist in the database.");
}
}
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);
}
}
