Parameters defaulting to invalid sizes for Always on Encryption

[ctgbarcalow]

The process reads from encrypted columns without issue, the ODBC 17 driver takes care of the decryption. But I can't insert into the table for the life of me. Below is a simple example that should work but it never does. It always converts my input parameters to an invalid type too long, too short, wrong type, and the encryption fails.

FWIW: Inserting from SSMS and EntityFrameworkCore works just fine.

Table

CREATE TABLE [dbo].[enc_test](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[a] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [???], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[b] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [???], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[c] [datetime2](7) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [???], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
) ON [PRIMARY]
GO

Stored Procedure

-- Columns a, b and c are encrypted using deterministic encryption
create procedure [dbo].[__test_enc]
  @a varchar(50),
  @b nvarchar(50),
  @c datetime2 = null
as
begin
    declare @ae_a varchar(50)  = @a
    declare @ae_b nvarchar(50) = @b
    declare @ae_c datetime2    = @c

    insert into [dbo].[enc_test] (a, b, c)
    output inserted.*
    values (@ae_a, @ae_b, @ae_c)
end

Node.js

const msnodesqlv8 = require('msnodesqlv8');

msnodesqlv8.open(dsnString, (err, conn, output) => {
  if (err) return console.log(err);

  let pm = conn.procedureMgr();
  pm.get('[dbo].[__test_enc]', proc => {
    proc.call(['T', 'T', '2022-01-01'], (err, results, output)=>{
      console.log({err, results, output});
    })
  })
})

Error

 [Error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: nvarchar(2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '???', column_encryption_key_database_name = 'table') is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '???', column_encryption_key_database_name = 'table') collation_name = 'SQL_Latin1_General_CP1_CI_AS'] {
    sqlstate: '22018',
    code: 206,
    severity: 16,
    serverName: 'SERVER\\DEV',
    procName: 'dbo.__test_enc',
    lineNumber: 0
  }

[TimelordUK]

hello,

I looked briefly at this issue and ran into many various errors setting up a test, encrption is not an area I have much expertise.

I was not for example able to generate keys that gave me a similar error that you are seeing but admittedly could also not produce a working test.

i am sorry but as things stand this will pobably require more time to investigate than I have to give - i wonder if we have any experts out there whom can help us out ..

[TimelordUK]

I know it is stating obvious I assume you had

SQLWCHAR *connString = L"Driver={ODBC Driver 18 for SQL Server};Server={myServer};Encrypt=yes;Trusted_Connection=yes;ColumnEncryption=Enabled;";

set? I will try to look at what is required from ODBC perspective to make this work.

[TimelordUK]

https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver?view=sql-server-ver16

not sure anything here helps this does all look quite complex!

for example what is purpose of below in connection string .

"Driver=ODBC Driver 18 for SQL Server;Server=myServer.myDomain;Encrypt=yes;Database=myDataBase;Trusted_Connection=Yes;ColumnEncryption=VBS-HGS,http://myHGSServer.myDomain/Attestation"

[TimelordUK]

this is interesting from page above

If the type of the parameter was set to SQL_WCHAR, which maps to nchar, the query would fail

this may be what is going on - ie. we may not bind column correctly. But without setting up an example i cant look into it - what is easiest sql I need to generate keys for example.

The SQL type of the parameter inserted into the SSN column is set to SQL_CHAR, which maps to the char SQL Server data type (rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, (SQLPOINTER)SSN, 0, &cbSSN);). If the type of the parameter was set to SQL_WCHAR, which maps to nchar, the query would fail, as Always Encrypted doesn't support server-side conversions from encrypted nchar values to encrypted char values. See ODBC Programmer's Reference -- Appendix D: Data Types for information about the data type mappings.

[ctgbarcalow]

Setting up a table with encrypted fields took me some time too. The obvious duh! moment for me was realizing that after you create a certificate on the SQL Server (if it's a different computer) you have to export it from the server and import it on the computer you used to query the database.

For testing purposes, I used and windows ODBC DSN Connection with the ODBC 18 driver. I did this so that I could use the make sure that it wasn't a problem with the connection string. I was able to use same DSN Connection with .Net Core to insert records and call the stored procedure.

I would assume that the fancy ColumnEncryption codes VBS-HGS and SGX-AAS are mostly to do with azure or other cloud hosted solutions where a 3rd computer is required to handle the attestation.

From what I've seen, the ODBC driver requires that the parameter type be the exact same length of that on the server, it cannot be different. nchar(5) must be described as nvhar(5), even if the value is shorter to match the data, the driver can't/won't convert encrypted columns. And the node-sqlserver-v8 seems always to modify the type based on the value being submitted.

For example, I was just able to insert a test record with encryption, but the column needed to be NVarChar and exactly double the length of the value being submitted.

Table

create table [dbo].[enc_test_2] (a nvarchar(6))
insert into [dbo].[enc_test_2] (a) values ('TTT')

[TimelordUK]

i have started an encrypt branch - the cpp needs to be compiled as this is still in development

you are right the mappings were not right for encryption

the below tests now work i.e. these types are fixed

encrypt
✔ encrypted binary via proc (173ms)
✔ encrypted varbinary via proc (173ms)
✔ encrypted decimal via proc (127ms)
✔ encrypted nvarchar via proc (122ms)
✔ encrypted char 10 via proc (104ms)
✔ encrypted bit via proc (118ms)
✔ encrypted big int via proc (109ms)
✔ encrypted tiny int via proc (136ms)
✔ encrypted small int via proc (124ms)
✔ encrypted int via proc (114ms)

[ctgbarcalow]

Did you happen to test any of the date data types? I'm also unable to insert Date, Datetime and Datetime2.

[TimelordUK]

Is that on the encrypt branch. I believe I fixed datetime2 the other day on that branch the other date types will not yet work. Every single type has to be exactly specified else encryption fails.Sent from my iPhoneOn 17 Nov 2022, at 15:55, ctgbarcalow ***@***.***> wrote: Did you happen to test any of the date data types? I'm also unable to insert Date, Datetime and Datetime2. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>

[TimelordUK]

so far these are working on encrypt - nothing on master works properly

encrypt
✔ encrypted float (691ms)
✔ encrypted UTC datetime2 (177ms)
✔ encrypted numeric -12.12345 via proc (107ms)
✔ encrypted numeric 12.12345 via proc (131ms)
✔ encrypted numeric 12.12345678901234 via proc (235ms)
✔ encrypted binary via proc (98ms)
✔ encrypted varbinary via proc (96ms)
✔ encrypted decimal via proc (98ms)
✔ encrypted nvarchar via proc (101ms)
✔ encrypted char 10 via proc (113ms)
✔ encrypted bit via proc (106ms)
✔ encrypted big int via proc (102ms)
✔ encrypted tiny int via proc (110ms)
✔ encrypted small int via proc (102ms)
✔ encrypted int via proc (108ms)

15 passing (3s)

[TimelordUK]

This is merged back to master not released on npm. Many types now work. Money is not supported.

[TimelordUK]

this is now released under v3.0.1