Memory Optimized Tables + Encryption and Identity Column

Question

I was wondering whether it is possible to have a always encrypted Memory Optimized Table and also have its Primary Key automatically seeded? For example, I want to create the following:

CREATE TABLE Foo
(
    [Id] [int] Identity(1,1) NOT NULL,
    [Bar] NVARCHAR(MAX) NOT NULL,
    CONSTRAINT [PK_Foo] PRIMARY KEY NONCLUSTERED ([Id] ASC)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

Fairly simple table and is initially created fine however, when I attempt to encrypt the column Bar I get the following exception.

Apr 21 2017 09:23:00 [Error] WorkitemExecution: Message:Inner exception: System.Data.SqlClient.SqlException Details: Cannot insert explicit value for identity column in table 'Foo' when IDENTITY_INSERT is set to OFF..

I also tried setting SET IDENTITY_INSERT Foo ON after the create table statement but still no luck.

Reading the documentation as well doesn't seem to suggest this isn't possible but perhaps I have missed something? If this isn't possible I have another approach which I know will work.

I guess my question in summary is.. "do Memory Optimized Tables support always encrypted with an identity column".

score 2 · Answer 1 · answered Apr 21 '17 at 16:59

You cannot encrypt identity columns using Always Encrypted, regardless if they are stored in memory optimized tables or in regular tables. In the normal Always Encrypted query processing flow, values inserted into encrypted columns are created and encrypted on the client-side. Note that only the client has encryption keys and can encrypt/decrypt data. SQL Server, by design, does not have access to encryption keys and cannot perform cryptographic operations.Identity column values are generated on the server side, so they do not fit the above workflow.

It would be useful to understand why you are trying to encrypt your identity column. Always Encrypted is designed to be used for protecting sensitive data. Auto-generated identifiers are typically not considered to be sensitive information.

Thanks,

Jakub

Sorry I didn't mean encrypt identity columns I meant encrypting another field (to be encrypted) but there happens to be an identity column in the table. For example, the field BAR is going to be encrypted ONLY. — Dr Schizo, Apr 21 '17 at 17:02

Nikhil Vithlani - Microsoft · Answer 2 · 2017-04-21T19:22:01.663

Even after your encryption through wizard failed, you should have a CMK and CEK created for you. You can check the name of the CEK that was created by browsing to YourDb -> Security -> Always Encrypted Keys -> Column Encryption Keys in SSMS object explorer. In my case the name of CEK was CEK_Auto1

If you find that there are no Column Encryption Keys in the above location, you can create new one by following the steps outlined in Provisioning Column Master Keys (New Column Master Key) and Provisioning Column Encryption Keys (New Column Encryption Key) in this article

You should be able to create the table as follows

CREATE TABLE Foo
(
    [Id] [int] Identity(1,1) NOT NULL,
    [Bar] NVARCHAR(MAX) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    CONSTRAINT [PK_Foo] PRIMARY KEY NONCLUSTERED ([Id] ASC)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

Memory Optimized Tables + Encryption and Identity Column

2 Answers2