Insert data into Always Encrypted column using stored procedure in MSSQL
0
votes
0
answers
343
views
I am facing an issue while trying to insert data into SQL table which is having encrypted columns in it.
Following is my approach.
I have created a table with three columns: **[ID], [Name], [Password]**.
**[Name]** and **[Password]** columns are encrypted.
with **COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [Test_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')**
- I have set "**Column Encryption Setting = Enabled**"
- I have set "**Parameterization for Always Encrypted**"
And I have created a procedure to insert records into Test table as follows:
USE [TestDB]
GO
CREATE PROCEDURE InsertTest
@name varchar(50),
@pwd varchar(max)
AS
BEGIN
INSERT INTO [dbo].[Test] ([Name] ,[Password]) VALUES (@name, @pwd)
END
GO
When I try to insert record using the above procedure I am getting error as shown below.
>Msg 206, Level 16, State 2, Procedure InsertTest, Line 0 [Batch Start Line 0]
Operand type clash: varchar is incompatible with varchar(1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Test_CEK', column_encryption_key_database_name = 'TestDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'
I want to insert data into SQL table through stored procedure.
Do not want to use ADO.Net for SQL connection.
Want to implement the same using EntityFramework.
Asked by Prathap r
(1 rep)
Jan 22, 2024, 09:34 AM
Last activity: Jan 22, 2024, 11:49 AM
Last activity: Jan 22, 2024, 11:49 AM