Optimized Memory Tables + Encryption and Identity Column
I was wondering if it is possible to always have encrypted Memory Optimized Table
and also have your primary key auto-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
Quite simple table and initially created fine, however, when I try 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 OFF.
I also tried setting SET IDENTITY_INSERT Foo ON
after generating the table statement but still no luck.
Reading the documentation also doesn't mean it isn't possible, but maybe I missed something? If this is not possible, I will have a different approach that I know will work.
I think my question of a kind is that ... "Support for memory-optimized tables is always encrypted with the identity column."
source to share
You cannot encrypt identity columns using Always Encrypted, whether they are stored in memory-optimized tables or regular tables. In the normal processing flow, encrypted requests inserted into encrypted columns are generated and encrypted on the client side. Note that only the client has encryption keys and can encrypt / decrypt data. SQL Server, on its own, 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 follow the above workflow.
It would be helpful to understand why you are trying to encrypt your id column. Always Encrypted is designed to protect sensitive data. Automatically generated identifiers are generally not considered confidential information.
Thank,
Jakub
source to share
Even after your encryption with the wizard fails, you should have a CMK and CEK created. You can check the CEK name that was generated by looking in YourDb -> Security -> Always Encrypted Keys -> Column Encryption Keys in the SSMS Object Explorer. In my case, the CEK name was CEK_Auto1
If you find that there are no column encryption keys in the above location, you can create a new one by following the steps in Column Primary Keys (New Column Master Key) and Column Encryption Encryption (New Column Encryption Key) in this article
You should be able to create a table like this
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
source to share