Ejemplo n.º 1
0
        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);
                }
            }
        }
Ejemplo n.º 2
0
 public SQLSetupStrategy()
 {
     if (certificate == null)
     {
         certificate = CertificateUtility.CreateCertificate();
     }
 }
Ejemplo n.º 3
0
 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();
             }
         }
     }
 }
Ejemplo n.º 4
0
 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.");
        }
Ejemplo n.º 7
0
        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();
        }
Ejemplo n.º 8
0
        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);
                }
            }
        }
Ejemplo n.º 9
0
        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);
                }
            }
        }
Ejemplo n.º 10
0
        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
        }
Ejemplo n.º 11
0
        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);
                        }
            }
        }
Ejemplo n.º 12
0
        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).");
                }
            }
        }
Ejemplo n.º 13
0
        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();
        }
Ejemplo n.º 14
0
        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;
 }
Ejemplo n.º 16
0
 public SQLSetupStrategy()
 {
     certificate = CertificateUtility.CreateCertificate();
     SetupDatabase();
 }