SQL Server始终加密的操作数类型冲突：运行EXEC sproc时，varchar与varchar（60）不兼容 [英] SQL Server Always Encrypted Operand type clash: varchar is incompatible with varchar(60) when running EXEC sproc

问题描述

我无法执行一个存储过程，该存储过程使用Always Encrypted向上插入具有加密列的表。但是，我能够从sproc复制SQL并使用设置了参数的常规SQL进行运行，在通过SSMS中的EXEC函数执行sproc时，无法使sproc触发，这也会导致应用程序

该表具有触发器，该触发器将插入另一个结构相似的审计表中，该审计表也使用相同的加密方法进行了加密。我已经完成了通常的事情：

检查查询选项中始终加密的启用参数设置

在连接设置上设置列加密设置=启用。

刷新存储过程的加密元数据：

  EXEC sp_refresh_parameter_encryption'organization.uspOrganizationAddressUpsert'
  

表格：

 创建表[organization]。[OrganizationAddress]（
 [OrganizationAddressId] [int] IDENTITY（1,1）NOT NULL，
 [CreatedUser] [int] NOT NULL，
 [CreatedDate] [datetime2]（7）非空，
 [LastUpdateUser] [int]非空，
 [LastUpdateDate] [datetime2]（7）非空，
 [RemovedDate] [datetime2]（7 ）NULL，
 [Address1] [varchar]（60）NOT NULL，
 [Address2] [varchar]（60）NULL，
 [City] [varchar]（60）收集Latin1_General_BIN2加密WITH（COLUMN_ENCRYPTION_KEY = [CEK_Auto1]，ENCRYPTION_TYPE =确定性，ALGORITHM ='AEAD_AES_256_CBC_HMAC_SHA_256'）NOT NULL，
 [State] [varchar]（60）NOT NULL，
 [ZipCode] [varchar]（60）非空，
 [ClientNumberId] [int]非空
 
创建表[审核]。[OrganizationAddressA udit]（
 [OrganizationAddressId] [int]非空，
 [CreatedUser] [int]非空，
 [CreatedDate] [datetime2]（7）非空，
 [ LastUpdateUser] [int] NOT NULL，
 [LastUpdateDate] [datetime2]（7）NOT NULL，
 [RemovedDate] [datetime2]（7）NULL，
 [Address1] [varchar]（ 60）NOT NULL，
 [Address2] [varchar]（60）NULL，
 [City] [varchar]（60）收集Latin1_General_BIN2 ENCRYPTED WITH（COLUMN_ENCRYPTION_KEY = [CEK_Auto1]，ENCRYPTION_TYPE =确定性，ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'）非空，
 [状态] [varchar]（60）非空，
 [邮政编码] [varchar]（60）非空，
 [ClientNumberId] [int]非NULL，
 [OperationDate] [datetime] NOT NULL，
 [Operation] [varchar]（50）NOT NULL，
 [OperationBy] [varchar]（100）NOT NULL 
  

存储过程：

 更改过程[组织]。[uspOrganizationAddressUpsert] 
 @OrganizationId INT，
 @ExecutingUserId INT，
 @ Address1 VARCHAR（ 60），
 @ Address2 VARCHAR（60），
 @City VARCHAR（60），
 @State VARCHAR（60），
 @ZipCode VARCHAR（60），
 @ClientNumberId INT 
 AS 
开始
在
上设置NOCOUNT声明@RightNow AS DATETIME2 = SYSDATETIME（）
如果存在（从[organization]中选择1。[OrganizationAddress ]其中ClientNumberId = @ClientNumberId）
开始
更新[组织]。[OrganizationAddress] SET 
 LastUpdateUser = @ ExecutingUserId，
 LastUpdateDate = @ RightNow，
 Address1 = @地址1，
地址2 = @地址2，
城市= @城市，
 [州] = @州，
邮政编码= @邮政编码，
 RemovedDate =空
其中ClientNumberId = @ClientNumberId 
 END 
 ELSE 
 BEGIN-插入UPSERT 
的一部分插入[organization]。[OrganizationAddress] 
（CreatedUser 
 ，创建日期
，最后更新用户
，最后更新日期
，地址1 
，地址2 
，城市
，[州] 
，邮政编码
 ，ClientNumberId）
值
（@ExecutingUserId 
，@ RightNow 
，@ ExecutingUserId 
，@ RightNow 
，@ Address1 
，@ Address2 
，@城市
，@ State 
，@ ZipCode 
，@ ClientNumberId）
 END 
 END 
  

运行带有参数设置的存储过程代码很好，但是我无法执行该存储过程：

 声明@orgId INT = 1; 
声明@client int = 888; 
声明@user int = 1; 
声明@ Add1 varchar（60）=‘测试地址1’； 
声明@ Add2 varchar（60）=测试地址2； 
声明@city varchar（60）=城市； 
声明@state varchar（60）=‘St’; 
声明@zip varchar（60）=‘12345’; 
 
 EXEC Organization.uspOrganizationAddressUpsert 
 @ OrganizationID = @ orgID，
 @ExecutingUserId = @user，-int 
 @ Address1 = @ Add1，-varchar（60 ）
 @ Address2 = @ Add2，-varchar（60）
 @City = @city，-varchar（60）
 @State = @state，-varchar（60）
 @ZipCode = @zip，-varchar（60）
 @ClientNumberId = @client; -int 
 
消息206，级别16，状态2，过程uspOrganizationAddressUpsert，第0行[Batch Start Line 1] 
操作数类型冲突：varchar与varchar（60）不兼容，并用（ encoding_type ='DETERMINISTIC'，encryption_algorithm_name ='AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name ='CEK_Auto1'，column_encryption_key_database_name ='BankruptcyApp'）collat​​ion_name ='SQL_Latin1_General _ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$来得来么？ b 
 
我在测试数据库中使用VARCHAR（60）为加密的列，触发器和审计表创建了一个相似的表，并且在那里工作良好，我无法在表/存储过程中找到任何差异/ trigger，它将允许它在那里而不是这里工作。我已经穷尽了我可以找到的其他帖子和博客。 
解决方案
已修复！需要更新应用程序代码以指定数据类型和长度：
  parameters.Add（ @ City，organizationAddress.City ，System.Data.DbType.AnsiString，System.Data.ParameterDirection.Input，60）； 
  
先前的位置：
  parameters.Add（ @ City，OrganizationAddress.City）
  
这至少可以使应用程序运行sproc，但仍然无法通过EXEC从SSMS运行它。
 
I am unable to EXEC a stored procedure that upserts a table that has an encrypted column using Always Encrypted. However, I am able to copy the SQL from the sproc and run that as regular SQL with the parameters set, Just cannot get the sproc to fire when executing the sproc via the EXEC function in SSMS which is also causing problems in the application

The table has a trigger on it that inserts into another audit table of similar structure that is also encrypted using the same encryption. I have done the usual thing: 
Checking the Enable Parameterizaion for Always Encrypted in Query Options 
Setting column encryption setting=enabled on the Connection Settings.
 Refreshing the encyrption metadata for the sproc:
    EXEC sp_refresh_parameter_encryption 'organization.uspOrganizationAddressUpsert'
Tables:
    CREATE TABLE [organization].[OrganizationAddress](
[OrganizationAddressId] [int] IDENTITY(1,1) NOT NULL,
[CreatedUser] [int] NOT NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[LastUpdateUser] [int] NOT NULL,
[LastUpdateDate] [datetime2](7) NOT NULL,
[RemovedDate] [datetime2](7) NULL,
[Address1] [varchar](60) NOT NULL,
[Address2] [varchar](60) NULL,
[City] [varchar](60) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[State] [varchar](60) NOT NULL,
[ZipCode] [varchar](60) NOT NULL,
[ClientNumberId] [int] NOT NULL

    CREATE TABLE [audit].[OrganizationAddressAudit](
[OrganizationAddressId] [int] NOT NULL,
[CreatedUser] [int] NOT NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[LastUpdateUser] [int] NOT NULL,
[LastUpdateDate] [datetime2](7) NOT NULL,
[RemovedDate] [datetime2](7) NULL,
[Address1] [varchar](60) NOT NULL,
[Address2] [varchar](60) NULL,
[City] [varchar](60) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[State] [varchar](60) NOT NULL,
[ZipCode] [varchar](60) NOT NULL,
[ClientNumberId] [int] NOT NULL,
[OperationDate] [datetime] NOT NULL,
[Operation] [varchar](50) NOT NULL,
[OperationBy] [varchar](100) NOT NULL
Stored Procedure:
    ALTER PROCEDURE [organization].[uspOrganizationAddressUpsert]
    @OrganizationId INT,
    @ExecutingUserId INT,
    @Address1 VARCHAR(60),
    @Address2 VARCHAR(60),
    @City VARCHAR(60),
    @State VARCHAR(60),
    @ZipCode VARCHAR(60),
    @ClientNumberId INT
AS
BEGIN
SET NOCOUNT ON
    DECLARE @RightNow AS DATETIME2 = SYSDATETIME()
    If EXISTS (Select 1 From [organization].[OrganizationAddress] Where ClientNumberId = @ClientNumberId)
        BEGIN
            UPDATE [organization].[OrganizationAddress] SET
            LastUpdateUser = @ExecutingUserId,
            LastUpdateDate = @RightNow,
            Address1 = @Address1,
            Address2 = @Address2,
            City = @City,
            [State] = @State,
            ZipCode = @ZipCode,
            RemovedDate = Null
            Where ClientNumberId = @ClientNumberId
            END
    ELSE
        BEGIN -- INSERT part of the UPSERT
            INSERT INTO [organization].[OrganizationAddress]
            (CreatedUser
            ,CreatedDate
            ,LastUpdateUser
            ,LastUpdateDate
            ,Address1
            ,Address2
            ,City
            ,[State]
            ,ZipCode
            ,ClientNumberId)
            VALUES
            (@ExecutingUserId
            ,@RightNow
            ,@ExecutingUserId
            ,@RightNow
            ,@Address1
            ,@Address2
            ,@City
            ,@State
            ,@ZipCode
            ,@ClientNumberId)   
        END
END
Running the stored procedure code with the paramteers set is fine, but I am unable to EXEC the sproc:
        declare @orgId INT = 1;
declare @client int = 888;
declare @user int = 1;
declare @Add1 varchar(60)= 'Test Address1';
declare @Add2 varchar(60)= 'Test Address2';
declare @city varchar(60) = 'City';
declare @state varchar(60) = 'St';
declare @zip varchar(60) = '12345';

EXEC organization.uspOrganizationAddressUpsert 
    @OrganizationID=@orgID,
    @ExecutingUserId = @user, -- int
    @Address1 = @Add1, -- varchar(60)
    @Address2 = @Add2, -- varchar(60)
    @City = @city, -- varchar(60)
    @State = @state, -- varchar(60)
    @ZipCode = @zip, -- varchar(60)
    @ClientNumberId = @client; -- int

Msg 206, Level 16, State 2, Procedure uspOrganizationAddressUpsert, Line 0 [Batch Start Line 1]
Operand type clash: varchar is incompatible with varchar(60) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'BankruptcyApp') collation_name = 'SQL_Latin1_General_CP1_CI_AS'
I created a similar table in a test DB with VARCHAR(60) for the enrcrypted columns, a trigger, and an audit table and its working fine there and I cant find any differences in the table/sproc/trigger that would allow it to work there and not here. I've pretty much exhausted the other posts and blogs I can find. 
解决方案
Fixed! Needed to update the app code to specify the data type and length:
parameters.Add("@City", organizationAddress.City, System.Data.DbType.AnsiString, System.Data.ParameterDirection.Input, 60);
where it was previously just:
parameters.Add("@City", organizationAddress.City)
This at least get the app to run the sproc but still cant run it from SSMS via EXEC

                        
这篇关于SQL Server始终加密的操作数类型冲突：运行EXEC sproc时，varchar与varchar（60）不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！