Parameters for encrypted columns

Question

Parameters for encrypted columns

Navigation

#1 by (3 votes)

4

I'm trying to create a procedure to save a user's data to the SQL SERVER database, however the table has columns encrypted with always encrypted, resulting in a conflict error.

I have tried to change the type of parameters for varbinary etc, but the error persists.

Creation script for table Usuario below:

CREATE TABLE Usuario(
    idUsuario int IDENTITY(1,1) NOT NULL,
    idCurso int NULL,
    nomeUsuario varchar(30) NULL,
    loginAcademico varchar(30) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [senhaAcademico] [varchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [emailAcessoSistema] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [senhaSistema] [varchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [isAceitouTermos] [bit] NULL,
    [ano] [varchar](4) NULL,
PRIMARY KEY CLUSTERED 
(
    [idUsuario] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Usuario]  WITH CHECK ADD FOREIGN KEY([idCurso])
REFERENCES [dbo].[Curso] ([idCurso])
ON DELETE SET NULL
GO

Complete error creating procedure:

Msg 206, Level 16, State 2, usp_user_save Procedure, Line 11 [Batch Start Line 7]
Varchar (30) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'HubbleDB') collation_name = 'Latin1_General_CI_AS' is incompatible with varchar (20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'HubbleDB') collation_name = 'Latin1_General_BIN2'

sql-server criptografia

asked by anonymous 18.11.2018 / 21:25

1 answer

SHA512 return in hexadecimal How to leave the background of a text the size of the text and not of the paragraph?

score 3 · Accepted Answer

For some reason when you have encrypted columns, when using them in a procedure the type and size of the variable must be the same as defined in the table.

In this case, change your procedure parameters to match your table:

ALTER PROCEDURE [dbo].[usp_usuario_salvar]
    @loginAcademico varchar(30),
    @senhaAcademico varchar(20),
    @emailAcessoSistema varchar(50),
    @senhaSistema varchar(20),
AS
    INSERT INTO Usuarios(loginAcademico, senhaAcademico, emailAcessoSistema, senhaSistema)
    VALUES(@loginAcademico, @senhaAcademico, @emailAcessoSistema, @senhaSistema)