Is it possible to define a User-Defined Table Type with an `Always Encrypted` column in it?

101 Views Asked by At

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.

0

There are 0 best solutions below