CODEWITHSUNDEEP
c#command-linesql-server-2016always-encrypted
user7792598
user7792598

Reputation: 197

SQL Server 2016 How to read/write to an always encrypted column from command line?

I am able to read and write to a column with always encrypted from a C# ASP.NET app. However, I need to do it from sqlcmd.

After some research I found here, that you need the -g parameter to activate the Column Encryption Setting, and to update to sqlcmd version 13.1, which I did and verified with "sqlcmd -?".

So, I made a test table:

create table tmp 
(
tmp_id int identity(1,1) not null,
test varchar(500)
COLLATE Latin1_General_BIN2
ENCRYPTED WITH 
   (ENCRYPTION_TYPE = RANDOMIZED, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
    COLUMN_ENCRYPTION_KEY = MY_Encryption_Key)  
null
);

And made a test.sql with the following:

insert into tmp (test) values ('mytest');

I call sqlcmd like this:

sqlcmd.exe -g -b -S "localhost\SQL2016" -d "my_db_name" -i "D:\test.sql" -U "my_username" -P "my_password"

But I get the following error, whether I use "-g" or not:

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MY_Encryption_Key', column_encryption_key_database_name = 'my_db_name') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

If I change test.sql to:

declare @test varchar(8000) = 'mytest';
insert into tmp (test) values (@test);

Then I get another error (still with or without -g):

Encryption scheme mismatch for columns/variables '@test'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '2' expects it to be (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MY_Encryption_Key', column_encryption_key_database_name = 'my_db_name') (or weaker).

I need this because currently there is a C# app that accepts a flat file, and then it is loaded into the DB through command line by calling ".sql" files. Now some columns need to be encrypted. So, we need sqlcmd to read/write the encrypted columns to avoid rewriting all the logic in C#.NET.

Am I doing something wrong? Is this even possible? Why is the "-g" parameter of sqlcmd not making a difference?

Upvotes: 3

Views: 1728

Answers (2)

Windows10
Windows10

Reputation: 51

create table tmp ( tmp_id int identity(1,1) not null,

test nvarchar(11)

COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK_Auto1)
null );

Create procedure [dbo].[Proc_Test] @test nvarchar(11) AS

insert into tmp (test) values (@test);

above code is worked for me in SSMS.

Upvotes: 1

Nikhil Vithlani - Microsoft
Nikhil Vithlani - Microsoft

Reputation: 837

Currently inserting data into an always encrypted column using the following syntax is only supported in SSMS.

declare @test varchar(8000) = 'mytest';
insert into tmp (test) values (@test);

You can find details regarding this here and here

Upvotes: 2

Related Questions