Wrapping SQL with Always Encrypted columns in a stored procedure-CodePudding

Using the AdventureWorksDW2017 database, I encrypted the dbo.DimCustomer.CustomerAlternateKey using deterministic encryption. I also created a target table called dbo.TargetTable where I also encrypted the dbo.TargetTable.CustomerAlternateKey column with deterministic encryption using the same key.

create table TargetTable
(
    [CustomerKey] int,
    [CustomerAlternateKey] nvarchar(15)
)

I have a connection setup in SSMS with column encryption setting = "enabled" so I can see the plaintext value of the encrypted column in dbo.DimCustomer and dbo.TargetTable.

When I write the following bits of code, they work just fine:

declare @keyvalue nvarchar(15) = 'AW00011000'

select * 
from [dbo].[DimCustomer] 
where CustomerAlternateKey = @keyvalue -- works

declare @keyvalue nvarchar(15) = 'AW00011000'

insert into TargetTable (CustomerKey, CustomerAlternateKey)
    select
        CustomerKey,
        CustomerAlternateKey
    from 
        [dbo].[DimCustomer]
    where 
        CustomerAlternateKey = @keyvalue -- works

If I wrap the code in a stored procedure, it does not work and throws out the standard encryption error.

create or alter procedure uspMoveToTarget 
    @keyvalue nvarchar(15)
as
begin
    insert into TargetTable (CustomerKey, CustomerAlternateKey)
        select
            CustomerKey,
            CustomerAlternateKey
        from [dbo].[DimCustomer]
        where CustomerAlternateKey = @keyvalue
end 

exec uspMoveToTarget 'AW00011000' -- does not work

declare @variable varchar(15) = 'AW00011000'
exec uspMoveToTarget @variable  -- does not work

This is the error I get:

Msg 206, Level 16, State 2, Procedure uspMoveToTarget, Line 0 [Batch Start Line 38]
Operand type clash: varchar is incompatible with nvarchar(15) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto2', column_encryption_key_database_name = 'AdventureWorksDW2017')*

Using deterministic encryption looked like a better bet than randomised because is seems like you can actually work with the data but if we can't wrap the code in stored procedures, the refactoring of our ETL process is going to be a larger effort than I initially thought.

Thanks in advance

CodePudding user response：

Operand type clash: varchar is incompatible with nvarchar(15)

There is no implicit conversion of encrypted values.

Try

declare @variable nvarchar(15) = N'AW00011000'
exec uspMoveToTarget @variable  -- works

The parameter is always required, but in some cases SSMS will parameterize the batch for you. But this doesn't work for stored procedure calls. You can see that the stored procedure parameter requires encryption like this:

EXEC sp_describe_parameter_encryption N'exec uspMoveToTarget @keyvalue',  N'@keyvalue nvarchar(15)';