sql-server - SQL Server Always Encrypted 操作数类型冲突 : varchar is incompatible with varchar(60) when running EXEC sproc

Question

我无法执行一个存储过程，该过程使用 Always Encrypted 更新插入具有加密列的表。但是，我能够从 sproc 复制 SQL 并将其作为带有参数集的常规 SQL 运行，只是无法在通过 SSMS 中的 EXEC 函数执行 sproc 时触发 sproc，这也会导致问题应用

该表上有一个触发器，它插入到另一个结构相似的审计表中，该审计表也使用相同的加密方法进行了加密。我做了通常的事情:

在查询选项中检查启用始终加密的参数化

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

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

    EXEC sp_refresh_parameter_encryption 'organization.uspOrganizationAddressUpsert'

表格:

    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

存储过程:

    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

使用设置的参数运行存储过程代码很好，但我无法执行存储过程:

        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'

我在测试数据库中使用 VARCHAR(60) 为加密列、触发器和审计表创建了一个类似的表，它在那里工作正常，但我在表/存储过程/触发器中找不到任何允许的差异它在那里而不是在这里工作。我几乎已经用尽了我能找到的其他帖子和博客。

Answer 1

已修复!需要更新应用程序代码以指定数据类型和长度:

parameters.Add("@City", organizationAddress.City, System.Data.DbType.AnsiString, System.Data.ParameterDirection.Input, 60);

它之前的位置:

parameters.Add("@City", organizationAddress.City)

这至少让应用程序运行存储过程，但仍然无法通过 EXEC 从 SSMS 运行它