Always Encrypted and sys.dm_exec_describe_first_result_set

Suppose we have the following table created in SQL Server 2016 CTP 3.2 (presuming that the encryption keys have already been created):

create table dbo.Customer
(
	CustomerID int NULL,
	FirstName varchar(50) collate Latin1_General_BIN2
		ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Auto1,
		ENCRYPTION_TYPE = Deterministic,
		ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	LastName varchar(50) collate Latin1_General_BIN2
		ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Auto1,
		ENCRYPTION_TYPE = Deterministic,
		ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	Address varchar(50) NULL,
	City varchar(50) NULL,
	State varchar(50) NULL
);

Now suppose we connect to the database using a connection where Column Encryption Setting=Disabled and execute:

select column_ordinal, name, system_type_name
from sys.dm_exec_describe_first_result_set(N'select * from dbo.Customer', null, null);

Then we get the following results:

DescribeFirstResultSet_EncryptionDisabled

The first and last name columns, rather than coming back as varchar(50) are instead shown as varbinary(113). This isn’t too surprising, given that we are on an unencrypted connection, and if we actually execute the select against the Customer table we will, in fact, get back binary data.

So surely if we use a connection where Column Encryption Setting=Enabled, we will get the correct data type back, right?

Nope.

DescribeFirstResultSet_EncryptionEnabled

We still get back varbinary(113).

I’m not sure how I feel about these results. On the one hand, the results are consistent regardless of the column encryption setting, and they do reflect the true data type at the storage engine level. However, the real usefulness of sys.dm_exec_describe_first_result_set (and its companion stored procedure sys.sp_describe_first_result_set, which gives identical results) is the ability to discover the data types coming back from a particular T-SQL statement. If used as intended (that is, on a connection with the column encryption setting enabled), the metadata coming back doesn’t match what will be returned by the actual query, significantly reducing the value of this DMF.

The official documentation for the DMF doesn’t address the impact of Always Encrypted, and the wording is sufficiently vague (“describes the metadata”) that it’s hard to say what the “correct” results should be in this case.

Of course, it could very well be something that Microsoft is already planning to address before RTM.

Leave a Reply

Your email address will not be published. Required fields are marked *