Parameters for encrypted columns

Asked

Viewed 69 times

4

I am trying to create a database to save a user’s data to the SQL SERVER database, but the table has columns encrypted with Always Encrypted, resulting in a conflict error.

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

Script of table creation Usuario down 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 while creating file:

Msg 206, Level 16, Status 2, Procedure usp_usuario_save, Line 11 [Lot Start Line 7]
Conflict in operand type: 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'

Código usado para criação da procedure

sql-server cryptography

  • (1) Could add in the question text how are declared table columns Usuario? (2) Could transcribe the complete error message?

  • Good evening Jose, I put the creation script and the complete error.

  • Have you tried to leave the Procedure variables with the same size in the table? varchar(20), varchar(50) and so on?

  • @Gabrielbastos At first it seems to me a grouping conflict (COLLATE). But it is also worth the suggestion of Gabriel Machado (gmsantos), on adjusting the size of the parameters. // As soon as possible I will simulate the scenario on a stool, to assess possible causes.

  • 1

    @gmsantos was just that, thank you all very much...

  • @Gabrielbastos moved the content of the comment to a reply

Show 1 more comment

1 answer

3


For some reason when you have encrypted columns, when using them inside a Procedure the type and size of the variable must be equal to that defined in the table.

In this case change the parameters of your Procedure 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)

Browser other questions tagged sql-server cryptography

You are not signed in. Login or sign up in order to post.