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