public SQLSetupStrategy()
{
if (certificate == null)
{
certificate = CertificateUtility.CreateCertificate();
}
}
public SQLSetupStrategy()
{
if (certificate == null)
{
certificate = CertificateUtility.CreateCertificate();
}
keyPath = string.Concat(StoreLocation.CurrentUser.ToString(), "/", StoreName.My.ToString(), "/", certificate.Thumbprint);
}
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 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();
}