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 ThrowExceptionWhenWhenEnclaveIsEnabledButNoSignature()
{
ColumnMasterKey cmk = new ColumnMasterKey("testCmkName", "testProvider", "testKeyPath")
{
IsEnclaveEnabled = true
};
Assert.Throws <InvalidColumnMasterKeyDefinitionException>(() => cmk.Create(null));
}
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 static bool IsColumnMasterKeyPresentInDatabase(this ColumnMasterKey columnMasterKey, SqlConnection sqlConnection)
{
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string cmkName = columnMasterKey.Name;
string sql = "SELECT column_master_key_id from sys.column_master_keys where name = @cmkName";
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add(new SqlParameter("cmkName", cmkName));
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
return(reader.HasRows);
}
}
}
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.");
}
}
private ColumnMasterKey CreateColumnMasterKey(string testRunId)
{
ColumnMasterKey columnMasterKey = new ColumnMasterKey
(
keyName: $"MicrosoftDataEncryptionTest_CMK_{testRunId}",
keyStoreProviderName: KeyStoreProvider.AzureKeyVaultProvider,
keyPath: keyEncryptionKeyPath
)
{
IsEnclaveEnabled = true,
Signature = "0xA33BAC501315F4E407A5A52ED36860F286622B42D7074AD63AD5D6CB65466290DCE28DAF0AC5AFB368C6F8D7940AAC69C3A011BA85C75A22175DE397E8058DA7320B0442F9B6B51CF649A0F5E1EF9A18EFAA0EF1CB5B15435265246B41FC54808FBFF665087F7048288BBEE4FCD3B4E55D6B50226B0A120B089A0A6D571A9CDFFA25D4623FA8076F1C4B250926CB9EDE187FEDD9404934EF0846C2C0206F165EFEC402386D85C2074E5EFD98A96BD32C6F97CB84FAB2BF3C263AC0762159D4504B4645D193CC761ECB107C6EC059FD449D8D89BA98D69BAB92534FAE20B8B577BE269FD2414E8273B42D67DC3ABB0E75902A3FED3F051F2CE23206F74545BEF6"
};
columnMasterKey.Create(SqlConnectionAE);
DatabaseObjects.Add(columnMasterKey);
return(columnMasterKey);
}
public EnclaveAzureDatabaseTests()
{
if (DataTestUtility.IsEnclaveAzureDatabaseSetup())
{
// Initialize AKV provider
sqlColumnEncryptionAzureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(AADUtility.AzureActiveDirectoryAuthenticationCallback);
if (!SQLSetupStrategyAzureKeyVault.isAKVProviderRegistered)
{
// Register AKV provider
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(customProviders: new Dictionary <string, SqlColumnEncryptionKeyStoreProvider>(capacity: 1, comparer: StringComparer.OrdinalIgnoreCase)
{
{ SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, sqlColumnEncryptionAzureKeyVaultProvider }
});
SQLSetupStrategyAzureKeyVault.isAKVProviderRegistered = true;
}
akvColumnMasterKey = new AkvColumnMasterKey(DatabaseHelper.GenerateUniqueName("AKVCMK"), akvUrl: DataTestUtility.AKVUrl, sqlColumnEncryptionAzureKeyVaultProvider, DataTestUtility.EnclaveEnabled);
databaseObjects.Add(akvColumnMasterKey);
akvColumnEncryptionKey = new ColumnEncryptionKey(DatabaseHelper.GenerateUniqueName("AKVCEK"),
akvColumnMasterKey,
sqlColumnEncryptionAzureKeyVaultProvider);
databaseObjects.Add(akvColumnEncryptionKey);
SqlConnectionStringBuilder connString1 = new SqlConnectionStringBuilder(DataTestUtility.EnclaveAzureDatabaseConnString);
connString1.InitialCatalog = "testdb001";
SqlConnectionStringBuilder connString2 = new SqlConnectionStringBuilder(DataTestUtility.EnclaveAzureDatabaseConnString);
connString2.InitialCatalog = "testdb002";
connStrings.Add(connString1.ToString());
connStrings.Add(connString2.ToString());
foreach (string connString in connStrings)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
databaseObjects.ForEach(o => o.Create(connection));
}
}
}
}
public EnclaveAzureDatabaseTests()
{
if (DataTestUtility.IsEnclaveAzureDatabaseSetup())
{
sqlColumnEncryptionAzureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new SqlClientCustomTokenCredential());
if (!SQLSetupStrategyAzureKeyVault.IsAKVProviderRegistered)
{
SQLSetupStrategyAzureKeyVault.RegisterGlobalProviders(sqlColumnEncryptionAzureKeyVaultProvider);
}
akvColumnMasterKey = new AkvColumnMasterKey(DatabaseHelper.GenerateUniqueName("AKVCMK"), akvUrl: DataTestUtility.AKVUrl, sqlColumnEncryptionAzureKeyVaultProvider, DataTestUtility.EnclaveEnabled);
databaseObjects.Add(akvColumnMasterKey);
akvColumnEncryptionKey = new ColumnEncryptionKey(DatabaseHelper.GenerateUniqueName("AKVCEK"),
akvColumnMasterKey,
sqlColumnEncryptionAzureKeyVaultProvider);
databaseObjects.Add(akvColumnEncryptionKey);
SqlConnectionStringBuilder connString1 = new SqlConnectionStringBuilder(DataTestUtility.EnclaveAzureDatabaseConnString);
connString1.InitialCatalog = "testdb001";
SqlConnectionStringBuilder connString2 = new SqlConnectionStringBuilder(DataTestUtility.EnclaveAzureDatabaseConnString);
connString2.InitialCatalog = "testdb002";
connStrings.Add(connString1.ToString());
connStrings.Add(connString2.ToString());
foreach (string connString in connStrings)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
databaseObjects.ForEach(o => o.Create(connection));
}
}
}
}
public void ThrowExceptionWhenDropWithNullSqlConnection()
{
ColumnMasterKey cmk = new ColumnMasterKey("testCmkName", "testProvider", "testKeyPath");
Assert.Throws <ArgumentNullException>(() => cmk.Drop(null));
}
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);
}
}
