Using Table Valued Parameters with Always Encrypted in SQL Server 2016 and Azure SQL Database

[アーティクル]
08/09/2016

Reviewed by Panagiotis Antonopoulos, Jakub Szymaszek, Raghav Kaushik Always Encrypted is one of the compelling features in SQL Server 2016 and in Azure SQL DB which provides a unique guarantee that data in the database cannot be viewed, accidentally or intentionally by users who do not have the ‘master key’ required to decrypt that data. If you want to know more about this feature, please review the product documentation at the previous link or watch the Channel 9 video on this topic.

Customer Scenario

In a recent case, we were working with a customer who was trying to use Table Valued Parameters (TVPs) to do a ‘batch import’ of data into the database. The TVP was a parameter into a stored procedure, and the stored procedure was in turn joining the values from the TVP ‘table’ with some other tables and then performing the final insert into a table which had some columns encrypted with Always Encrypted.

Now, most of the ‘magic’ behind Always Encrypted is actually embedded in the client library which is used. Unfortunately, none of the client libraries (.NET, JDBC or ODBC) support encrypted columns passed within TVPs. So, we needed a viable workaround in this case to unblock the customer. In this blog post, we explain this workaround by using a simple example.

Walkthrough: Working with Bulk data in Always Encrypted

We first proceed to create Column Master Key (CMK) and a Column Encryption Key (CEK). For the CMK, we used a certificate from the Current User store for simplicity. For more information on key management in Always Encrypted, please refer to this link.

Create the CMK

Here’s how we created the CMK. You can either use the GUI:

Or you can use T-SQL syntax:

 USE [TVPAE]
GO
CREATE COLUMN MASTER KEY [TestCMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/C17D4826FA1B6B68808951BF81734283388937EF'
)

Create the CEK

Here’s how we created the CEK using the GUI:

Alternatively you can use T-SQL to do this:

 CREATE COLUMN ENCRYPTION KEY [TestCEK]
WITH VALUES
(
COLUMN_MASTER_KEY = [TestCMK],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0063003100370064003...5E48531480
)

Create the table

Then we create the final table, with the encrypted column defined. Note that in the real application, this table already exists, with data in it. We’ve obviously simplified the scenario here!

 CREATE TABLE FinalTable
(
idCol INT,
somePII VARCHAR(500) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = TestCEK,
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
)

Reworking the application to use SqlBulkCopy instead of TVPs

With this setup on the database side of things, we proceed to develop our client application to work around the TVP limitation. The key to doing this is to use the SqlBulkCopy class in .NET Framework 4.6 or above. This class ‘understands’ Always Encrypted and should need minimal rework on the developer front. The reason for the minimal rework is that this class actually accepts a DataTable as parameter, which is previously what the TVP was passed as. This is an important point, because it will help minimize the changes to the application.

Let’s get this working! The high-level steps are outlined below; there is a full code listing at the end of this blog post as well.

Populate the DataTable as before with the bulk data

As mentioned before the creation and population of the DataTable does not change. In the sample below, this is done in the MakeTable() method.

Using client side ad-hoc SQL, create a staging table on the server side.

This could also be done using T-SQL inside a stored procedure, but we had to uniquely name the staging table per-session so we chose to create the table from ad-hoc T-SQL in the application. We did this using a SELECT … INTO with a dummy WHERE clause (in the code listing, please refer to the condition ‘1=2’ which allows us to efficiently clone the table definition without having to hard-code the same), so that the column encryption setting is retained on the staging table as well. In the sample below, this step is done in the first part of the DoBulkInsert method.

Use the SqlBulkCopy API to ‘bulk insert’ into staging table

This is the core of the process. The important things to note here are the connection string (in the top of the class in the code listing) has the Column Encryption Setting attribute set to Enabled. When this attribute is set to Enabled, the SqlBulkCopy class interrogates the destination table and determines that a set of columns (in our sample case, it is just one column) needs to be encrypted before passing to server. This step is in the second part of the DoBulkInsert method.

Move data from staging table into final table

In the sample application, this is done by using an ad-hoc T-SQL statement to simple append the new data from staging table into final table. In the real application, this would typically be done through some T-SQL logic within a stored procedure or such.

There is an important consideration here: encrypted column data cannot be transformed on the server side. This means that no expressions (columns being concatenated, calculated or transformed in any other way) are permitted on the encrypted columns on server side. This limitation is true regardless of whether you use TVPs or not, but might become even more important in the case where TVPs are used.

In our sample application we just inserted the data from the staging table into the final table, and then drop the staging table. This code is in the InsertStagingDataIntoMainTable method in the listing below.

Conclusion

While Always Encrypted offers a compelling use case to protect sensitive data on the database side, there are some restrictions it poses to the application. In this blog post we show you how you can work around the restriction with TVPs and bulk data. We hope this helps you move forward with adopting Always Encrypted! Please leave your comments and questions below, we are eager to hear from you!

Appendix: Client Application Code

Here is the client application code used.

 namespace TVPAE
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    class Program
    {
        static private string TVPAEConnectionString = "Server=.;Initial Catalog=TVPAE;Integrated Security=true;Column Encryption Setting=enabled;";

        static void Main(string[] args)
        {
            var stgTableName = DoBulkInsert(MakeTable());
            InsertStagingDataIntoMainTable(stgTableName);
        }

        private static DataTable MakeTable()
        {
            DataTable newData = new DataTable();

            // create columns in the DataTable
            var idCol = new DataColumn()
            {
                DataType = System.Type.GetType("System.Int32"),
                ColumnName = "idCol",
                AutoIncrement = true
            };

            newData.Columns.Add(idCol);

            var somePII = new DataColumn()
            {
                DataType = System.Type.GetType("System.String"),
                ColumnName = "somePII"
            };

            newData.Columns.Add(somePII);

            // create and add some test data
            var rand = new Random();
            for (var loopCount = 0; loopCount < 10000; loopCount++)
            {
                var datarowSample = newData.NewRow();

                datarowSample["somePII"] = DateTime.Now.ToLongDateString();
                newData.Rows.Add(datarowSample);
            }

            newData.AcceptChanges();

            return newData;
        }

        private static void InsertStagingDataIntoMainTable(string stgTableName)
        {
            using (var conn = new SqlConnection(TVPAEConnectionString))
            {
                conn.Open();
                using (var cmd = new SqlCommand("BEGIN TRAN; INSERT FinalTable SELECT * FROM [" + stgTableName + "]; DROP TABLE [" + stgTableName + "]; COMMIT", conn))
                {
                    Console.WriteLine("Inserted rowcount: " + cmd.ExecuteNonQuery().ToString());
                }
            }
        }

        private static string DoBulkInsert(DataTable stagingData)
        {
            string stagingTableName = "StagingTable_" + Guid.NewGuid().ToString();

            using (var conn = new SqlConnection(TVPAEConnectionString))
            {
                conn.Open();

                // create the staging table - note the use of the dummy WHERE 1 = 2 predicate
                using (var cmd = new SqlCommand("SELECT * INTO [" + stagingTableName + "] FROM FinalTable WHERE 1 = 2;", conn))
                {
                    cmd.ExecuteNonQuery();
                }

                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.DestinationTableName = "[" + stagingTableName + "]";
                    bulkCopy.WriteToServer(stagingData);
                }
            }

            return stagingTableName;
        }
    }
}

Comments

Anonymous
August 12, 2016
Is there a plan in the future to support user defined table types. We have a significant number of stored procedures that take user defined tables types as a parameter, so many of those procedures would need to be refactored.
- Anonymous
  August 17, 2016
  Hi Alan - we do not have this lined up anytime soon. Can you please contact me offline at arvindsh AT microsoft DOT com and share more use case details, and your contact information if you don't mind? We are very interested to talk to more customers with this issue so your details would be very useful.- Arvind.
Anonymous
December 13, 2016
Is it possible to send an encrypted parameter in a stored procedure to the database to query a table with an encrypted column? More specifically, if I have a table with an encrypted column and I wanted to query it based on a user input, will I be able to write a stored procedure and pass the user input to the database and query the encrypted column inside the stored procedure with my user input in the where clause (deterministic encryption)
Anonymous
February 03, 2017
The same case with us. We have a significant number of stored procedures that take user defined tables types as a parameter.Is there any update if this will be back supported or not?
Anonymous
December 04, 2017
We're in the process of implementing Always Encrypted in a new project and have run into this issue. Surely this must affect a large number of customers - particularly with GDPR just around the corner?What is Microsoft's plan and timeline for addressing this issue?
- Anonymous
  April 10, 2018
  hi Nigel, could you drop me a note about your use case at arvindsh at microsoft dot com? It would help us understand options.

次の方法で共有