3

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?

user7792598
  • 177
  • 1
  • 6
  • 17
  • Try casting the value as varchar(8000) when you insert. – Jacob H May 24 '17 at 15:59
  • It did not work, I had already tried – user7792598 May 24 '17 at 16:34
  • If this worked, you would have effectively *circumvented* the Always Encrypted feature, given that the strings in your .sql files are *not encrypted*. The `-g` option allows `sqlcmd` to retrieve data from AE columns (if it has access to the keys), but it still cannot run arbitrary T-SQL using unencrypted values, no more than other clients could. The values must be properly parameterized from a client that supports it. That means plaintext T-SQL scripts are out. SSMS has some hacky support for that now since it *is* a pain in the ass, but `sqlcmd` is not sophisticated enough. – Jeroen Mostert May 26 '17 at 13:45

2 Answers2

2

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

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
1

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.

Windows10
  • 51
  • 9
  • Your code creates a table and creates a stored procedure, but doesn't _call_ the stored procedure. Using it on my end, I get `Operand type clash: varchar is incompatible with nvarchar(11) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'tkcMuckAround')` – TomK Dec 30 '20 at 23:38