0

I have a SP that accepts a user-defined table type table and does inserts and updates to a database table.

Example:

CREATE PROC [dbo].[MySP1] (@InsertData dbo.MyDataType READONLY)
AS
BEGIN
    INSERT INTO dbo.MyTable (A, B, C)
    SELECT  ia.A, ia.B, ia.C
    FROM    @InsertData AS ia
END

Recently, I have decided to encrypt one column of that database table (dbo.MyTable) using Always Encrypted.

Now, in order to keep the SP operational, I (guess I) will have to update the user table type (dbo.MyDataType) to contain an Always Encrypted column.

But this doesn't seem possible to me, neither from the SSMS GUI, nor via T-SQL.

E.g., this fails:

CREATE TYPE [dbo].[MyDataType] AS TABLE(
    [A] [INT] NULL,
    [B] [VARCHAR](MAX) NULL,
    [C] [VARCHAR](MAX) COLLATE Danish_Norwegian_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)

Is this possible? If not, are they any alternatives? Passing the the table values as scalar values to the SP is not very possible, since I will have to call that SP as many times as the number of the rows in the original user defined table.

user2173353
  • 4,316
  • 4
  • 47
  • 79
  • 2
    This is explicitly [documented as unsupported](https://learn.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-database-engine#limitations). If your stored procedure does nothing very interesting, an alternative would be to eliminate it in favor of directly inserting into the table from the client (round-trip overhead can be minimized by doing things in a transaction). Alternatively, handle encryption client-side entirely so the limitations of Always Encrypted don't apply (though you lose the transparency this way, of course). – Jeroen Mostert Jun 20 '23 at 11:05
  • Thanks @JeroenMostert. My SP also does some security checks, so the 1st option will probably not be ideal. Any hints that help on figuring out the C# code used to encrypt the data programmatically would be welcome. – user2173353 Jun 20 '23 at 11:44
  • 1
    The basic approach would be the same as the one AE is using: generate a strong, long key used to asymmetrically encrypt a key which is in turn used in symmetric encryption like AES for the actual data (since using asymmetric encryption for everything would be too slow). The process AE uses is [documented](https://learn.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-cryptography); you would not necessarily have to follow the same steps, but something similar. – Jeroen Mostert Jun 20 '23 at 11:57
  • I see that, if I try to update the encrypted column with a `VARBINARY(MAX)` value inside the SP, it fails with this error while updating the SP: `Operand type clash: varbinary(max) is incompatible with varchar(max) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', ...`. Is there a way to cast this to an encrypted `VARCHAR(MAX)`? – user2173353 Jun 20 '23 at 14:33
  • 1
    Nope. That, too, is mentioned as unsupported on the same page I linked earlier. If you were to move encryption to the client side, then the field would have to change to an (unencrypted!) `VARBINARY(MAX)`, and you would only get it as a string after decryption. The only loophole there is when [bulk loading encrypted data](https://learn.microsoft.com/sql/relational-databases/security/encryption/migrate-sensitive-data-protected-by-always-encrypted), but this is intended for migration scenarios. – Jeroen Mostert Jun 20 '23 at 14:39
  • Thanks a lot. This was all SO helpful! It appears I have problems locating the information I need in the documentation :( . You could post an answer with all this info so that I can accept it, if you want. Or I could add an answer myself, if you don't want to spend extra time. – user2173353 Jun 20 '23 at 15:04
  • 1
    I dont really get what microsoft was thinking with AE. It has so many limitations, especially the INSERT/UPDATE ones that it becomes unuseful in my humble opinion. – siggemannen Jun 21 '23 at 18:53

0 Answers0