varchar is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC'

Question

I'm trying to pass in a parameter from my application to do a search inside a stored procedure. I pass in the parameter like this:

SqlParameter param1 = new SqlParameter(@"@FilterCustomerPO", "G06756");
param1.DbType = DbType.AnsiString;
param1.Direction = ParameterDirection.Input;
param1.Size = 50;

sqlCmd.Parameters.Add(param1);

In the stored procedure, it is defined like this:

ALTER PROCEDURE [dbo].[usp_POListing]
    @FilterCustomerPO VARCHAR (50)
AS
BEGIN
    SELECT * 
    FROM [Order]
    WHERE PONumber = @FilterCustomerPO
END

The PONumber column is encrypted using DETERMINISTIC encryption type.

When I pass in a value, I get an error:

Operand type clash: varchar is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnEncryptionKey', column_encryption_key_database_name = 'DataPortal') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

I've seen a lot of other people reporting on this, but none of those solutions worked for me. I'm using parameterized queries, as you can see, so not sure what I could be missing.

EDIT:

I've also tried passing in the parameter this way - same error:

sqlCmd.Parameters.Add("FilterCustomerPO", SqlDbType.VarChar, 50);
sqlCmd.Parameters["FilterCustomerPO"].Value = "G06756"

The definition of the Order Table is as follows:

CREATE TABLE [dbo].[Order]
(
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [CustomerID] [INT] NOT NULL,
    [OrderNumber] [INT] NOT NULL,
    [DBCOrderNumber] [VARCHAR](25) NOT NULL,
    [PONumber] [VARCHAR](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [BillingName] [VARCHAR](255) NOT NULL,

    CONSTRAINT [PK_Order] 
        PRIMARY KEY CLUSTERED ([OrderID] ASC)
                    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

My client connection string also includes the Column Encryption Setting=Enabled

Please note: I am able to retrieve data just fine. It's when I send parameters into the query, and try to do a comparison/filter when I get the error.

Answer 1

This is AlwaysEncrypted, which only uses client-side encryption keys. It's designed to prevent SQL Server (or its administrators) from being able to decrypt the data. If you want column encryption with server-managed keys, SQL Server has that too, but it's a different feature. See Encrypt a Column of Data.

In AlwaysEncrypted data type of Order.PONumber is not really just varchar(50). It's

[varchar](50) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (
      COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey], 
      ENCRYPTION_TYPE = Deterministic, 
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')

That's all part of the column type, and so you can't use a parameter of type varchar(50). To search on this column the client has to encrypt the parameter value with the specified key and algorithm, so SQL Server can match the deterministically-encrypted column value with the encrypted parameter value. SQL Server does not have the column encryption keys, so it can't decrypt the column value, or encrypt the parameter values.

SQL Server has some ability to propagate the encrypted table parameters to a stored procedure or function. This one appears to work fine. To check the stored procedure parameter has picked up the column encryption, check sys.parameters. EG

select name, encryption_type_desc, encryption_algorithm_name
from sys.parameters
where object_id = object_id('usp_POListing')

I created a simple repro and was able to call the stored procedure like this:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp27
{
    class Program
    {
        static void Main(string[] args)
        {

            using (var con = new SqlConnection("server=localhost;database=testdb;integrated security=true;Column Encryption Setting=enabled"))
            {
                con.Open();

                var sqlCmd = new SqlCommand("usp_POListing", con);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                var param1 = new SqlParameter("@FilterCustomerPO", "G06756");
                param1.SqlDbType = SqlDbType.VarChar;
                param1.Size = 50;
                param1.Direction = ParameterDirection.Input;

                sqlCmd.Parameters.Add(param1);

                var dt = new DataTable();
                using (var rdr = sqlCmd.ExecuteReader())
                {
                    dt.Load(rdr);
                }
            }
        }
    }
}

See generally Develop using Always Encrypted with .NET Framework Data Provider