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