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.
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)
CREATE PROCEDURE [SomeStoredProcedure]
WITH EXECUTE AS OWNER
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @EncryptedValue VARBINARY(4096);
SELECT TOP 1
@EncryptedValue = [someencryptedcolumn]
CREATE TABLE [sometable]
COLUMN_ENCRYPTION_KEY = [CEK1],
ENCRYPTION_TYPE = Randomized,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
INSERT INTO [sometable] ([someencryptedcolumn])
Call the stored procedure:
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.
Lorraine Bigmore commented
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