Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SQL Server stored procedure selecting encrypted data into variable fails

Stored procedure that selects data from encrypted column (Always Encrypted) into variable fails with an error

Cannot continue the execution because the session is in the kill state

if XACT_ABORT is set to on.

Removing SET XACT_ABORT ON; line makes the stored procedure work perfectly, but it is unclear how it is related.

Completely removing variable also fixes an error.

Environment:

Microsoft SQL Server 2016 Enterprise (64-bit) Service Pack 2 with CU2 (13.0.5153.0): latest build at the moment.
Microsoft Windows NT 6.3 (15063)
Stored procedure:

CREATE PROCEDURE [SomeStoredProcedure]
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @EncryptedValue VARBINARY(4096);

SELECT TOP 1
@EncryptedValue = [someencryptedcolumn]
FROM
[sometable];

SELECT @EncryptedValue

RETURN 0;
END;
Table declaration:

CREATE TABLE [sometable]
(
[someencryptedcolumn] [varbinary](4096)
ENCRYPTED WITH
(
COLUMN_ENCRYPTION_KEY = [CEK1],
ENCRYPTION_TYPE = Randomized,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
)
NULL
)
Sample data:

INSERT INTO [sometable] ([someencryptedcolumn])
VALUES (NULL)
Call the stored procedure:

EXEC [SomeStoredProcedure];
Error:

Msg 596, Level 21, State 1, Line 29
Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 29
A severe error occurred on the current command. The results, if any, should be discarded.

Seems it's also reproducible on SQL Server 2017 RTM.

It isn't specific to XACT_ABORT. You also see the same with other set options. Such as

SET DATEFIRST 5
SET ANSI_NULLS OFF.
When these are present it ends up calling sqllang.dll!CEnvColEncryptionKey::XretSchemaChanged twice and the second time around it ends up trying to dereference a null pointer and fails with an Access violation reading location 0x0000000000000000.

The call stack when the error is thrown (by SELECT @EncryptedValue) is as attached.

5 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Maxim Sidorenko shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Lorraine Bigmore commented  ·   ·  Flag as inappropriate

    This issue was raised through Microsoft CSS, reproduced and a SQL defect raised with the title:
    Access violation in CEnvColEncryptionKey::XretSchemaChanged at inline function CExecutionCollection::PiMedAccessGet when using a stored procedure containing a SET statement to select from an encrypted column

Feedback and Knowledge Base