SQL Server always encrypted with Dynamic SQL

Question

I am using SQL Server always encrypted on feature for SSN column. I have created a stored procedure and calling it from .net code. I am using column encryption setting=true in my connection string. Calling the dynamic stored procedure from the .net code throws this error:

Operand type clash: varchar is incompatible with varchar(4) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'DatabaseName') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

Could someone please provide a sample code which would work to retrieve data with the search on encrypted column using parameterization?

CREATE PROCEDURE [dbo].[testSSN_Dynamic]
    @Last4SSN VARCHAR(50) 
AS
BEGIN
    DECLARE @query NVARCHAR(4000);

    SET @query = 'Select [SSNumber], [last4SSNumber] from table_XXX   WHERE last4SSNumber =  @Last4SSN ;'

    EXEC sp_executesql @query, N'@Last4SSN varchar(4)', @Last4SSN ;
END

In the application SSN number is encrypted using Randomized encryption. Last4SSNumber is encrypted using Deterministic encryption. The search is based on last 4 digit of SSN number and the filtered list will get the SSN number only with the last 4 matching digit, rest all the digits are masked in the application. Since both the encryption Randomized and deterministic do not support like operation the column last4SSNumber was added.

When I call the above Stored procedure without the dynamic SQL from .NET code, it works fine and I am able to retrieve the list of matching last 4 digit of SSN..

In the application there are existing stored procedure with quite a few parameters passed to them. The search criteria is based on if the value is passed to the parameter or not. Last4SSNumber is one of the parameter passed. I have provided a simple sample of the stored procedure and not the entire stored procedure with all of the parameters. The search criteria is build, depending on the value passed to the parameter.

In various post it has been pointed out that the Dynamic SQL has to be converted to Parameterized query in order to use the always encrypted on feature. I applied parameterization to the above stored procedure still I receive the error when calling the stored procedure from the .NET code.

IF you used "deterministic" encryption, (same value is always encrypted the same way), you can encrypt the parameter value and search that way. If you you used random encryption, you're SOL... See the "Selecting Deterministic or Randomized Encryption" section of the MS doc... https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine — Jason A. Long, Oct 01 '17 at 01:00

score 0 · Answer 1 · answered Oct 02 '17 at 21:19

Use Dynamic SQL with parameters that evaluate against encrypted columns is currently not supported.

Unfortunately executing the query in the manner below is not supported by Always Encrypted

EXEC sp_executesql @query, N'@Last4SSN varchar(4)', @Last4SSN ;

However, if you are able to restructure your stored procedure in the following manner, you may have a working solution.

CREATE PROCEDURE [dbo].[testSSN]
    @Last4SSN VARCHAR(50) 
AS
BEGIN    
    Select [SSNumber], [last4SSNumber] from table_XXX   WHERE last4SSNumber =  @Last4SSN ;
END

SQL Server always encrypted with Dynamic SQL

1 Answers1