private void LoadingAdapter()
{
ColumnEncryption encryptor = new ColumnEncryption();
IProcessing adapter = new Adapter(encryptor);
serializationPlugins.Add(adapter);
listBoxSerPlugins.Items.Add(adapter.Name);
}
public Adapter(ColumnEncryption encryptor)
{
_encryptor = encryptor;
Name = _encryptor.Name;
}
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);
}
}
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);
}