CODEWITHSUNDEEP
c#asp.netsql-serverlinqalways-encrypted
AranDG
AranDG

Reputation: 406

Cannot Insert into SQL Server AlwaysEncrypted Table with Linq

Absolutely going out of my mind with this, so please, any suggestions would be welcome.

I am running SQL Server 2016 with two encrypted tables:

hr_client

[id] [int] IDENTITY(1,1) NOT NULL,
[employee_id] [nvarchar](20) NULL,
[honorific] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[first_name] [nvarchar](60) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[last_name] [nvarchar](60) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[gender] [char](1) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[date_of_birth] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[national_insurance] [nvarchar](9) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[start_date] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[end_date] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[position] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[department] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[manager] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[grade] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[salary] [decimal](10, 2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[status] [int] NULL,
[modify_date] [datetime] NULL,
[user_modify] [nvarchar](20) NULL,
[active] [bit] NULL,
[type] [nvarchar](20) NULL

hr_grade

[id] [int] IDENTITY(1,1) NOT NULL,
[grade_id] [nvarchar](20) NULL,
[description] [nvarchar](30) NULL,
[min_rate] [decimal](10, 2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[max_rate] [decimal](10, 2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[overtime_1] [decimal](5, 2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[overtime_2] [decimal](5, 2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [EXAMPLE_KEY_NAME], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[modify_date] [datetime] NULL,
[user_modify] [varchar](20) NULL,
[comments] [varchar](max) NULL,
[status] [int] NULL,
[active] [bit] NULL,
[type] [nvarchar](20) NULL,
[position] [int] NULL,

I have setup the Column Master and Column Encryption Keys (currently stored on SQL Server). I have also setup Stored Procedures to Insert and Select data from these two tables. The data is encrypted as expected, and data can be inserted and returned using the stored procedures (all working fine direct in SSMS).

To access the data, I have created an ASP MVC application running on our internal IIS web server. The Application is targeting 4.6.1, and I use LinqToSql dbml for my SQL connection & commands. Connection String is as follows:

Data Source=EXAMPLE_IP;Column Encryption Setting=enabled;Initial Catalog=hr;Persist Security Info=True;User ID=EXAMPLE_USER;

Data can be inserted into, and selected from, the hr_grade table without issue.

However I can only SELECT from the hr_client table, and the INSERT (both direct or via Stored Procedure) fail on the following error:

Operand type clash: 
nvarchar(4000) encrypted with (
    encryption_type = 'RANDOMIZED', 
    encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
    column_encryption_key_name = 'EXAMPLE_KEY_NAME', 
    column_encryption_key_database_name = 'hr'
) is incompatible with nvarchar(20) encrypted with (
    encryption_type = 'RANDOMIZED', 
    encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
    column_encryption_key_name = 'EXAMPLE_KEY_NAME', 
    column_encryption_key_database_name = 'hr')

Now the only obvious difference I can see is that the hr_client table has NVARCHAR columns, which my application doesn't seem to like.

Any ideas / suggestions?

Upvotes: 1

Views: 1570

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

You should use profiler to verify, but I suspect Linq2SQL is declaring a parameter of type nvarchar(4000) to insert into a nvarchar(20) column. With client encryption the SqlParameter type and length must match the target column, as the SQL Server can't perform any server-side conversion on the value.

You'll probably have to use EF or ADO.NET directly.

Upvotes: 3

Related Questions