6

I'm trying to determine if I can use Always Encrypted on a column in a temporary table (SQL Server 2016 SP1 Enterprise on Windows 2016). I've tried as follows:

CREATE TABLE #listofdates (compare_dt datetime2(7) encrypted with (
    COLUMN_ENCRYPTION_KEY = [ColEnc1]
    , ENCRYPTION_TYPE = Deterministic
    , ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NOT NULL
)

However, I get the error:

Msg 33293, Level 16, State 8, Line 3
Cannot find the column encryption key "ColEnc1" because it does not exist or you do not have permissions.

I think this error is misleading, because the statement works if I simply replace table name #listofdates with another name without the leading # (therefore switching from a temporary table to an ordinary table).

The section on the MS site that lists limits/restrictions on Always Encrypted doesn't mention temporary tables.

5

It appears that you have to create a CMK and CEK in tempdb.

This command Works On My Machine®, and is largely copy/pasted from BOL articles.

USE tempdb

CREATE COLUMN MASTER KEY cmk_whatever
    WITH ( KEY_STORE_PROVIDER_NAME = N'cmk_whatever',
           KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420' );

CREATE COLUMN ENCRYPTION KEY cek_whatever
    WITH VALUES
    (
        COLUMN_MASTER_KEY = cmk_whatever,
        ALGORITHM = 'RSA_OAEP',
        ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86
    );


CREATE TABLE #listofdates
(
    compare_dt DATETIME2(7)
        ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = cek_whatever, ENCRYPTION_TYPE = DETERMINISTIC,
                         ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256' ) NOT NULL
);
2

I am guessing that the CEK [ColEnc1] resides in the same database that the table listofdates resides in. Try creating the CMK and CEK in tempdb table and the error should be gone.

  • This seems to be correct; I accepted @sp_BlitzErik's answer as more detailed. Thanks! – M Herbener Oct 4 '17 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.