Why SQL Server stored procedure selecting encrypted data into variable fails

Question

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.

I found no references on how XACT_ABORT and Always Encrypted related.

I also checked for SQL Server logs but found no additional information on the issue.

Update:

Link to the registered SQL Server bug

Answer 1

This is a bug in the product. You should first try installing the latest SP/CU to see if it has already been fixed and if not report it to Microsoft.

I can also reproduce this on SQL Server 2017 RTM. I haven't tried installing the latest CU to see if that improves things.

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 follows.