SQL 2016 Always Encrypted columns and SQL temporary tables (#temp)

Question

We are looking for a solution to implement "always encrypted" columns in a database, where we are using at the same time many SQL temporary tables (#tmp).

We explored the alternate path - stop using #temp tables, but this would mean a high impact on our app in terms of time/cost.

Did anyone find a way to write queries like "insert into #tmp select from my_table", where my_table contains AE columns?

I tried applying the same CMK and CEK to the tempdb database, so that I can create the same structure for the #tmp table, as the structure of my_table. This doesn't solve the problem though - having the tables in 2 different databases seems to prevent the data transfer.

I'm looking for an SQL solution, and not for a solution which involves a client app (C#, vb, etc.) which has access to all the encryption keys.

Answer 1

Insert operations in the manner you are describing are not supported for encrypted columns.

"insert into #tmp select from my_table"

You will have to write a client app to achieve a similar result. If you want to explore that path, please leave a comment and I can guide you.

---

You should be able to achieve something similar in C# as follows.

• Do select * from encryptedTable to load the data in a SqlDataReader then use SqlBulkCopy to load it to the temp table using SqlBulkCopy.WriteToServer(IDataReader) Method

If you have the encrypted table and the plaintext table on the same SQL Server instance, then be aware that you might to leaking information to SQL Server admin, because they can examine the plaintext data and corresponding ciphertext