CODEWITHSUNDEEP
sql-serverencryptionsql-server-2016azure-sql-databasealways-encrypted
Asdfg
Asdfg

Reputation: 12233

Retrieving encrypted column 'xxx' with CommandBehavior=SequentialAccess is not supported

I have setup a Azure SQL Database and enabled Always Encrypted.

One of the column (column3) is varchar(Max).

When I query the database from SSMS without using "column encryption setting=enabled", I can see that all the columns have binary data.

When I query the database from SSMS using "column encryption setting=enabled", I am getting an error as below:

An error occurred while executing batch. Error message is: Retrieving encrypted column 'column3' with CommandBehavior=SequentialAccess is not supported.

This is what my table definition looks like:

CREATE TABLE [dbo].[mytable](
    [column1] [varchar](2000) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [column2] [datetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [column3] [varchar](max) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)

If I remove the encryption on column3, everything works fine and I can see decrypted values.

Am I missing something here?

enter image description here

Upvotes: 4

Views: 909

Answers (2)

Jakub Szymaszek - Microsoft
Jakub Szymaszek - Microsoft

Reputation: 596

This issue has been fixed in the SSMS 16.3, released in August 2016. You can now query and decrypt varchar(max) and other LOB columns.

Upvotes: 0

Nitish Upreti
Nitish Upreti

Reputation: 6550

Developer from the Always Encrypted team here.

SQL Server and Azure SQL DB do not allow querying VARCHAR(MAX) column with "column encryption setting=enabled" from SSMS as SSMS employs Sequential Access for streaming potentially large varchar(MAX) values. SSMS enforces this choice by design as it provides a mechanism to query partial byte locations in large objects ( thus avoiding storage of entire large blob in memory). However, in contrast, Always Encrypted does not allow partial decryption for encrypted columns. This limitation stems from the fact that we use block ciphers and also store HMAC in the encrypted cell, without the full cell value we cannot validate the HMAC and decrypt the data.

As an alternative, you can query a varchar(max) with "column encryption setting=enabled" from a ADO.Net client (without using SequentialAccess).

Upvotes: 2

Related Questions