public void InvalidAlgorithmVersion()
{
byte[] encrypteCekLocal = ColumnEncryptionKey.GenerateInvalidEncryptedCek(encryptedCek, ColumnEncryptionKey.ECEKCorruption.ALGORITHM_VERSION);
Exception ex1 = Assert.Throws <ArgumentException>(() => fixture.AkvStoreProvider.DecryptColumnEncryptionKey(DataTestUtility.AKVUrl, MasterKeyEncAlgo, encrypteCekLocal));
Assert.Matches($@"Specified encrypted column encryption key contains an invalid encryption algorithm version '10'. Expected version is '01'.\s+\(?Parameter (name: )?'?encryptedColumnEncryptionKey('\))?", ex1.Message);
Exception ex2 = Assert.Throws <ArgumentException>(() => fixture.AkvStoreProvider.EncryptColumnEncryptionKey(DataTestUtility.AKVUrl, "RSA_CORRUPT", encryptedCek));
Assert.Contains("Invalid key encryption algorithm specified: 'RSA_CORRUPT'. Expected value: 'RSA_OAEP' or 'RSA-OAEP'.", ex2.Message);
}
public void InvalidCertificateSignature()
{
// Put an invalid signature
byte[] encrypteCekLocal = ColumnEncryptionKey.GenerateInvalidEncryptedCek(encryptedCek, ColumnEncryptionKey.ECEKCorruption.SIGNATURE);
string errorMessage =
$"The specified encrypted column encryption key signature does not match the signature computed with the column master key (Asymmetric key in Azure Key Vault) in '{DataTestUtility.AKVUrl}'. The encrypted column encryption key may be corrupt, or the specified path may be incorrect.{Environment.NewLine}Parameter name: encryptedColumnEncryptionKey";
Exception ex1 = Assert.Throws <ArgumentException>(() => fixture.AkvStoreProvider.DecryptColumnEncryptionKey(DataTestUtility.AKVUrl, MasterKeyEncAlgo, encrypteCekLocal));
Assert.Contains(errorMessage, ex1.Message);
}
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 IsColumnEncryptionKeyPresentInDatabase(this ColumnEncryptionKey columnEncryptionKey, SqlConnection sqlConnection)
{
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string cekName = columnEncryptionKey.Name;
string sql = "SELECT column_encryption_key_id from sys.column_encryption_keys where name = @cekName";
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add(new SqlParameter("cekName", cekName));
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
return(reader.HasRows);
}
}
}
public void TestRoundTripWithAKVAndCertStoreProvider()
{
using (SQLSetupStrategyCertStoreProvider certStoreFixture = new SQLSetupStrategyCertStoreProvider())
{
byte[] plainTextColumnEncryptionKey = ColumnEncryptionKey.GenerateRandomBytes(ColumnEncryptionKey.KeySizeInBytes);
byte[] encryptedColumnEncryptionKeyUsingAKV = fixture.AkvStoreProvider.EncryptColumnEncryptionKey(DataTestUtility.AKVUrl, @"RSA_OAEP", plainTextColumnEncryptionKey);
byte[] columnEncryptionKeyReturnedAKV2Cert = certStoreFixture.CertStoreProvider.DecryptColumnEncryptionKey(certStoreFixture.CspColumnMasterKey.KeyPath, @"RSA_OAEP", encryptedColumnEncryptionKeyUsingAKV);
Assert.True(plainTextColumnEncryptionKey.SequenceEqual(columnEncryptionKeyReturnedAKV2Cert), @"Roundtrip failed");
// Try the opposite.
byte[] encryptedColumnEncryptionKeyUsingCert = certStoreFixture.CertStoreProvider.EncryptColumnEncryptionKey(certStoreFixture.CspColumnMasterKey.KeyPath, @"RSA_OAEP", plainTextColumnEncryptionKey);
byte[] columnEncryptionKeyReturnedCert2AKV = fixture.AkvStoreProvider.DecryptColumnEncryptionKey(DataTestUtility.AKVUrl, @"RSA_OAEP", encryptedColumnEncryptionKeyUsingCert);
Assert.True(plainTextColumnEncryptionKey.SequenceEqual(columnEncryptionKeyReturnedCert2AKV), @"Roundtrip failed");
}
}
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 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 BulkCopyAETestTable(string tableName, ColumnEncryptionKey columnEncryptionKey1, ColumnEncryptionKey columnEncryptionKey2) : base(tableName)
{
this.columnEncryptionKey1 = columnEncryptionKey1;
this.columnEncryptionKey2 = columnEncryptionKey2;
}
public ColumnEncryption(ColumnEncryptionKey columnEncryptionKey, ColumnEncryptionType encryptionType)
: this(columnEncryptionKey.Name, encryptionType)
{
columnEncryptionKey.ThrowIfNull(nameof(columnEncryptionKey));
}
public SqlNullValuesTable(string tableName, ColumnEncryptionKey columnEncryptionKey) : base(tableName)
{
this.columnEncryptionKey = columnEncryptionKey;
}
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);
}
}
public ApiTestTable(string tableName, ColumnEncryptionKey columnEncryptionKey1, ColumnEncryptionKey columnEncryptionKey2, bool useDeterministicEncryption = false) : base(tableName)
{
this.columnEncryptionKey1 = columnEncryptionKey1;
this.columnEncryptionKey2 = columnEncryptionKey2;
this.useDeterministicEncryption = useDeterministicEncryption;
}
private Table CreateTable(string testRunId, ColumnEncryptionKey columnEncryptionKey)
{
ColumnEncryption columnEncryption = new ColumnEncryption(columnEncryptionKey, ColumnEncryptionType.Deterministic);
Table table = new Table($"MicrosoftDataEncryptionTestTable_{testRunId}");
table.Columns.AddAll
(
new Column("bigint", BigInt())
{
ColumnEncryption = columnEncryption
},
new Column("binary", Binary(10))
{
ColumnEncryption = columnEncryption
},
new Column("bit", Bit())
{
ColumnEncryption = columnEncryption
},
new Column("char", Char(10))
{
ColumnEncryption = columnEncryption, Collation = "Latin1_General_BIN2"
},
new Column("date", Date())
{
ColumnEncryption = columnEncryption
},
new Column("datetime", DateTime())
{
ColumnEncryption = columnEncryption
},
new Column("datetime2", DateTime2(7))
{
ColumnEncryption = columnEncryption
},
new Column("datetimeoffset", DateTimeOffset(7))
{
ColumnEncryption = columnEncryption
},
new Column("decimal", Decimal(18, 0))
{
ColumnEncryption = columnEncryption
},
new Column("float", Float())
{
ColumnEncryption = columnEncryption
},
new Column("int", Int())
{
ColumnEncryption = columnEncryption
},
new Column("money", Money())
{
ColumnEncryption = columnEncryption
},
new Column("nchar", NChar(10))
{
ColumnEncryption = columnEncryption, Collation = "Latin1_General_BIN2"
},
new Column("numeric", Numeric(18, 0))
{
ColumnEncryption = columnEncryption
},
new Column("nvarchar", NVarChar(50))
{
ColumnEncryption = columnEncryption, Collation = "Latin1_General_BIN2"
},
new Column("nvarcharMAX", NVarChar(MAX))
{
ColumnEncryption = columnEncryption, Collation = "Latin1_General_BIN2"
},
new Column("real", Real())
{
ColumnEncryption = columnEncryption
},
new Column("smalldatetime", SmallDateTime())
{
ColumnEncryption = columnEncryption
},
new Column("smallint", SmallInt())
{
ColumnEncryption = columnEncryption
},
new Column("smallmoney", SmallMoney())
{
ColumnEncryption = columnEncryption
},
new Column("time", Time(7))
{
ColumnEncryption = columnEncryption
},
new Column("tinyint", TinyInt())
{
ColumnEncryption = columnEncryption
},
new Column("uniqueidentifier", UniqueIdentifier())
{
ColumnEncryption = columnEncryption
},
new Column("varbinary", VarBinary(10))
{
ColumnEncryption = columnEncryption
},
new Column("varbinaryMAX", VarBinary(MAX))
{
ColumnEncryption = columnEncryption
},
new Column("varchar", VarChar(50))
{
ColumnEncryption = columnEncryption, Collation = "Latin1_General_BIN2"
},
new Column("varcharMAX", VarChar(MAX))
{
ColumnEncryption = columnEncryption, Collation = "Latin1_General_BIN2"
}
);
table.Create(SqlConnectionAE);
DatabaseObjects.Add(table);
return(table);
}
public BulkCopyTruncationTables(string tableName, ColumnEncryptionKey columnEncryptionKey1) : base(tableName)
{
this.columnEncryptionKey = columnEncryptionKey1;
}
