public void Dispose()
{
// Do NOT remove certificate for concurrent consistency. Certificates are used for other test cases as well.
foreach (string connectionStr in DataTestUtility.AEConnStringsSetup)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionStr);
using (SqlConnection conn = CertificateUtility.GetOpenConnection(false, sb))
{
using (SqlCommand cmd = new SqlCommand($"drop table {encryptedTableName}", conn))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.CommandText = $"drop procedure {encryptedProcedureName}";
cmd.ExecuteNonQuery();
}
}
// Only use traceoff for non-sysadmin role accounts, Azure accounts does not have the permission.
if (DataTestUtility.IsNotAzureServer())
{
CertificateUtility.ChangeServerTceSetting(true, sb);
}
}
}
public SQLSetupStrategy()
{
if (certificate == null)
{
certificate = CertificateUtility.CreateCertificate();
}
}
private void CreateAndPopulateSimpleTable()
{
encryptedTableName = DatabaseHelper.GenerateUniqueName("encrypted");
encryptedProcedureName = DatabaseHelper.GenerateUniqueName("encrypted");
foreach (string connectionStr in DataTestUtility.AEConnStringsSetup)
{
using (SqlConnection conn = CertificateUtility.GetOpenConnection(false, new SqlConnectionStringBuilder(connectionStr)))
{
using (SqlCommand cmdCreate = new SqlCommand($"create table {encryptedTableName}(c1 int)", conn))
{
cmdCreate.CommandType = CommandType.Text;
cmdCreate.ExecuteNonQuery();
}
using (SqlCommand cmdInsert = new SqlCommand($"insert into {encryptedTableName} values(1)", conn))
{
cmdInsert.CommandType = CommandType.Text;
cmdInsert.ExecuteNonQuery();
}
using (SqlCommand cmdCreateProc = new SqlCommand($"create procedure {encryptedProcedureName}(@c1 int) as insert into {encryptedTableName} values (@c1)", conn))
{
cmdCreateProc.CommandType = CommandType.Text;
cmdCreateProc.ExecuteNonQuery();
}
}
}
}
public SQLSetupStrategy()
{
if (certificate == null)
{
certificate = CertificateUtility.CreateCertificate();
}
keyPath = string.Concat(StoreLocation.CurrentUser.ToString(), "/", StoreName.My.ToString(), "/", certificate.Thumbprint);
}
/// <summary>
/// Helper function to test the result of encryption using Aead.
/// </summary>
/// <param name="plainText"></param>
/// <param name="rootKey"></param>
/// <param name="encryptionType"></param>
/// <param name="expectedFinalCellValue"></param>
private void TestEncryptionResultUsingAead(byte[] plainText, byte[] rootKey, CertificateUtility.CColumnEncryptionType encryptionType, byte[] expectedFinalCellValue)
{
// Encrypt.
byte[] encryptedCellData = CertificateUtility.EncryptDataUsingAED(plainText, rootKey, encryptionType);
Debug.Assert(encryptedCellData != null && encryptedCellData.Length > 0);
Assert.True(encryptedCellData.SequenceEqual(expectedFinalCellValue), "Final Cell Value does not match with the native code baseline.");
}
/// <summary>
/// Helper function to test the result of decryption using Aead.
/// </summary>
/// <param name="cipherText"></param>
/// <param name="rootKey"></param>
/// <param name="encryptionType"></param>
/// <param name="expectedPlainText"></param>
private void TestDecryptionResultUsingAead(byte[] cipherText, byte[] rootKey, CertificateUtility.CColumnEncryptionType encryptionType, byte[] expectedPlainText)
{
// Decrypt.
byte[] decryptedCellData = CertificateUtility.DecryptDataUsingAED(cipherText, rootKey, encryptionType);
Debug.Assert(decryptedCellData != null);
Assert.True(decryptedCellData.SequenceEqual(expectedPlainText), "Decrypted cell data does not match with the native code baseline.");
}
public void TestTrustedColumnEncryptionMasterKeyPathsWithMultipleServers()
{
SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(defaultConnectionString);
// 3. Test with multiple servers with multiple key paths
//
// Clear existing dictionary.
if (SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Count != 0)
{
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Clear();
}
// Add entries for one server
List <string> server1TrustedKeyPaths = new List <string>();
// Add some random key paths
foreach (char c in new char[] { 'A', 'B' })
{
string tempThumbprint = new string('F', CertificateUtility.CreateCertificate().Thumbprint.Length);
string invalidKeyPath = string.Format(@"{0}/my/{1}", StoreLocation.CurrentUser.ToString(), tempThumbprint);
server1TrustedKeyPaths.Add(invalidKeyPath);
}
// Add the key path used by the test
server1TrustedKeyPaths.Add(columnMasterKeyPath);
// Add it to the dictionary
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Add(connBuilder.DataSource, server1TrustedKeyPaths);
// Add entries for another server
List <string> server2TrustedKeyPaths = new List <string>();
server2TrustedKeyPaths.Add(@"https://balneetestkeyvault.vault.azure.net/keys/CryptoTest4/f4eb1dbbe6a9446599efe3c952614e70");
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Add(@"randomeserver", server2TrustedKeyPaths);
using (SqlConnection sqlConnection = new SqlConnection(string.Concat(defaultConnectionString, @";Column Encryption Setting = Enabled;")))
{
sqlConnection.Open();
// Test INPUT parameter on an encrypted parameter
using (SqlCommand sqlCommand = new SqlCommand(
$@"SELECT CustomerId, FirstName, LastName
FROM [{tableName}]
WHERE FirstName = @firstName",
sqlConnection))
{
SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"firstName", @"Microsoft");
customerFirstParam.Direction = System.Data.ParameterDirection.Input;
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
ValidateResultSet(sqlDataReader);
}
}
}
// Clear out trusted key paths
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Clear();
}
public void TestParamUnexpectedEncryptionMD(string connectionString)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString);
using (SqlConnection conn = CertificateUtility.GetOpenConnection(true, sb))
{
using (SqlCommand cmd = new SqlCommand(ExceptionGenericErrorFixture.encryptedProcedureName, conn))
{
SqlParameter param = cmd.Parameters.AddWithValue("@c1", 2);
param.ForceColumnEncryption = true;
cmd.CommandType = CommandType.StoredProcedure;
string expectedErrorMessage = $"Cannot execute statement or procedure '{ExceptionGenericErrorFixture.encryptedProcedureName}' because ForceColumnEncryption(true) was set for SqlParameter '@c1' and the database expects this parameter to be sent as plaintext. This may be due to a configuration error.";
InvalidOperationException e = Assert.Throws <InvalidOperationException>(() => cmd.ExecuteNonQuery());
Assert.Contains(expectedErrorMessage, e.Message);
}
}
}
public void TestInvalidForceColumnEncryptionSetting(string connectionString)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString);
using (SqlConnection conn = CertificateUtility.GetOpenConnection(false, sb))
{
using (SqlCommand cmd = new SqlCommand(ExceptionGenericErrorFixture.encryptedProcedureName, conn))
{
SqlParameter param = cmd.Parameters.AddWithValue("@c1", 2);
param.ForceColumnEncryption = true;
cmd.CommandType = CommandType.StoredProcedure;
string expectedErrorMessage = $"Cannot set ForceColumnEncryption(true) for SqlParameter '@c1' because encryption is not enabled for the statement or procedure '{ExceptionGenericErrorFixture.encryptedProcedureName}'.";
InvalidOperationException e = Assert.Throws <InvalidOperationException>(() => cmd.ExecuteNonQuery());
Assert.Contains(expectedErrorMessage, e.Message);
}
}
}
public void TestCommandOptionWithNoTceFeature(string connectionString)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString);
CertificateUtility.ChangeServerTceSetting(false, sb); // disable TCE on engine.
using (SqlConnection conn = CertificateUtility.GetOpenConnection(false, sb, fSuppressAttestation: true))
{
using (SqlCommand cmd = new SqlCommand(ExceptionGenericErrorFixture.encryptedProcedureName, conn, null, SqlCommandColumnEncryptionSetting.Enabled))
{
SqlParameter param = cmd.Parameters.AddWithValue("@c1", 2);
cmd.CommandType = CommandType.StoredProcedure;
string expectedErrorMessage = "SQL Server instance in use does not support column encryption.";
InvalidOperationException e = Assert.Throws <InvalidOperationException>(() => cmd.ExecuteNonQuery());
Assert.Contains(expectedErrorMessage, e.Message);
}
}
// Turn on TCE now
CertificateUtility.ChangeServerTceSetting(true, sb); // enable tce
}
public void TestDataAdapterAndEncrytionSetting(string connectionString)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString);
// Create a new SqlCommand for select and delete
using (SqlConnection conn = CertificateUtility.GetOpenConnection(false, sb))
{
using (SqlCommand cmdInsert = new SqlCommand(ExceptionGenericErrorFixture.encryptedProcedureName, conn, null, SqlCommandColumnEncryptionSetting.Enabled))
using (SqlCommand cmdDelete = new SqlCommand($"delete {ExceptionGenericErrorFixture.encryptedTableName} where c1 = @c1", conn, null, SqlCommandColumnEncryptionSetting.Disabled))
using (SqlDataAdapter adapter = new SqlDataAdapter($"select c1 from {ExceptionGenericErrorFixture.encryptedTableName}", conn))
{
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add("@c1", SqlDbType.Int, 4, "c1");
cmdInsert.UpdatedRowSource = UpdateRowSource.None;
cmdDelete.Parameters.Add("@c1", SqlDbType.Int, 4, "c1");
cmdDelete.UpdatedRowSource = UpdateRowSource.None;
adapter.InsertCommand = cmdInsert;
adapter.DeleteCommand = cmdDelete;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables[0];
foreach (DataRow row in table.Rows)
{
row.Delete();
}
DataRow rowInserted = table.NewRow();
rowInserted["c1"] = 5;
table.Rows.Add(rowInserted);
adapter.UpdateBatchSize = 0; // remove batch size limit
// run batch update
string expectedErrorMessage = "SqlCommandColumnEncryptionSetting should be identical on all commands (SelectCommand, InsertCommand, UpdateCommand, DeleteCommand) when doing batch updates.";
InvalidOperationException e = Assert.Throws <InvalidOperationException>(() => adapter.Update(dataset));
Assert.Contains(expectedErrorMessage, e.Message);
}
}
}
public void TestRsaCryptoWithNativeBaseline()
{
// Initialize the reader for resource text file which has the native code generated baseline.
CryptoNativeBaselineReader cryptoNativeBaselineReader = new CryptoNativeBaselineReader();
// Read and initialized the crypto vectors from the resource text file.
cryptoNativeBaselineReader.InitializeCryptoVectors(CryptNativeTestVectorType.Rsa);
IList <CryptoVector> cryptoParametersListForTest = cryptoNativeBaselineReader.CryptoVectors;
Assert.True(cryptoParametersListForTest.Count >= 3, @"Invalid number of RSA test vectors. Expected at least 3 (RSA Keypair + PFX + test vectors).");
Assert.True(cryptoParametersListForTest[0].CryptNativeTestVectorTypeVal == CryptNativeTestVectorType.RsaKeyPair, @"First entry must be an RSA key pair.");
Assert.True(cryptoParametersListForTest[1].CryptNativeTestVectorTypeVal == CryptNativeTestVectorType.RsaPfx, @"2nd entry must be a PFX.");
byte[] rsaKeyPair = cryptoParametersListForTest[0].RsaKeyPair;
byte[] rsaPfx = cryptoParametersListForTest[1].RsaKeyPair;
// For each crypto vector, run the test to compare the output generated through sqlclient's code and the native code.
foreach (CryptoVector cryptoParameter in cryptoParametersListForTest)
{
if (cryptoParameter.CryptNativeTestVectorTypeVal == CryptNativeTestVectorType.Rsa)
{
// Verify that we are using the right padding scheme for RSA encryption
byte[] plaintext = CertificateUtility.DecryptRsaDirectly(rsaPfx, cryptoParameter.CiphertextCek, @"Test");
Assert.True(cryptoParameter.PlaintextCek.SequenceEqual(plaintext), "Plaintext CEK Value does not match with the native code baseline.");
// Verify that the signed blob is conforming to our envelope (SHA-256, PKCS 1 padding)
bool signatureVerified = CertificateUtility.VerifyRsaSignatureDirectly(cryptoParameter.HashedCek, cryptoParameter.SignedCek, rsaPfx);
Assert.True(signatureVerified, "Plaintext CEK signature scheme does not match with the native code baseline.");
//// TODO: Programmatically install the in-memory PFX into the right store (based on path) & use the public API
//plaintext = Utility.VerifyRsaSignature(cryptoParameter.PathCek, cryptoParameter.FinalcellCek, rsaPfx);
//CError.Compare(cryptoParameter.PlaintextCek.SequenceEqual(plaintext), "Plaintext CEK Value does not match with the native code baseline (end to end).");
}
}
}
public void TestTrustedColumnEncryptionMasterKeyPathsWithInvalidInputs()
{
SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(defaultConnectionString);
// 1. Test with null List
//
// Clear existing dictionary.
if (SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Count != 0)
{
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Clear();
}
// Clear any cache
CertificateUtility.CleanSqlClientCache();
// Prepare a dictionary with null list.
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Add(connBuilder.DataSource, (List <string>)null);
using (SqlConnection sqlConnection = new SqlConnection(string.Concat(defaultConnectionString, @";Column Encryption Setting = Enabled;")))
{
sqlConnection.Open();
// Test INPUT parameter on an encrypted parameter
using (SqlCommand sqlCommand = new SqlCommand(
$@"SELECT CustomerId, FirstName, LastName
FROM [{tableName}]
WHERE FirstName = @firstName",
sqlConnection))
{
SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"firstName", @"Microsoft");
customerFirstParam.Direction = System.Data.ParameterDirection.Input;
string expectedErrorMessage = "not a trusted key path";
ArgumentException e = Assert.Throws <ArgumentException>(() => sqlCommand.ExecuteReader());
Assert.Contains(expectedErrorMessage, e.Message);
}
}
// 2. Test with empty List
//
// Clear existing dictionary.
if (SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Count != 0)
{
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Clear();
}
// Prepare dictionary with an empty list
List <string> emptyKeyPathList = new List <string>();
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Add(connBuilder.DataSource, emptyKeyPathList);
using (SqlConnection sqlConnection = new SqlConnection(string.Concat(defaultConnectionString, @";Column Encryption Setting = Enabled;")))
{
sqlConnection.Open();
// Test INPUT parameter on an encrypted parameter
using (SqlCommand sqlCommand = new SqlCommand(
$@"SELECT CustomerId, FirstName, LastName
FROM [{tableName}]
WHERE FirstName = @firstName",
sqlConnection))
{
SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"firstName", @"Microsoft");
customerFirstParam.Direction = System.Data.ParameterDirection.Input;
string expectedErrorMessage = "not a trusted key path";
ArgumentException e = Assert.Throws <ArgumentException>(() => sqlCommand.ExecuteReader());
Assert.Contains(expectedErrorMessage, e.Message);
}
}
// 3. Test with invalid key paths
//
// Clear existing dictionary.
if (SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Count != 0)
{
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Clear();
}
// Prepare dictionary with invalid key path
List <string> invalidKeyPathList = new List <string>();
string tempThumbprint = new string('F', CertificateUtility.CreateCertificate().Thumbprint.Length);
string invalidKeyPath = string.Format(@"{0}/my/{1}", StoreLocation.CurrentUser.ToString(), tempThumbprint);
invalidKeyPathList.Add(invalidKeyPath);
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Add(connBuilder.DataSource, invalidKeyPathList);
using (SqlConnection sqlConnection = new SqlConnection(string.Concat(defaultConnectionString, @";Column Encryption Setting = Enabled;")))
{
sqlConnection.Open();
// Test INPUT parameter on an encrypted parameter
using (SqlCommand sqlCommand = new SqlCommand(
$@"SELECT CustomerId, FirstName, LastName
FROM [{tableName}]
WHERE FirstName = @firstName",
sqlConnection))
{
SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"firstName", @"Microsoft");
customerFirstParam.Direction = System.Data.ParameterDirection.Input;
string expectedErrorMessage = "not a trusted key path";
ArgumentException e = Assert.Throws <ArgumentException>(() => sqlCommand.ExecuteReader());
Assert.Contains(expectedErrorMessage, e.Message);
}
}
// Clear out trusted key paths
SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.Clear();
}
public TestTrustedMasterKeyPaths(SQLSetupStrategy fixture)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr);
builder.ConnectTimeout = 10000;
defaultConnectionString = builder.ToString();
columnMasterKeyPath = string.Format(@"{0}/{1}/{2}", StoreLocation.CurrentUser.ToString(), @"my", CertificateUtility.CreateCertificate().Thumbprint);
this.fixture = fixture;
tableName = fixture.TrustedMasterKeyPathsTestTable.Name;
}
public TestTrustedMasterKeyPaths(SQLSetupStrategyCertStoreProvider fixture)
{
columnMasterKeyPath = string.Format(@"{0}/{1}/{2}", StoreLocation.CurrentUser.ToString(), @"my", CertificateUtility.CreateCertificate().Thumbprint);
this.fixture = fixture;
tableName = fixture.TrustedMasterKeyPathsTestTable.Name;
}
public SQLSetupStrategy()
{
certificate = CertificateUtility.CreateCertificate();
SetupDatabase();
}