mORMot Open Source

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

ORM limitations in MS SQL Virtual Tables with Always Encrypted Columns

I'm posting this message to share with you my experience with ORM limitations in inserting or updating objects which are stored as virtual tables in Microsoft SQL Server and have one or more string properties encrypted using the Always Encrypted feature. Typical IRestORM Add or Update operations fail, because the corresponding parametrized queries send by SQLLite to the SQL server via ODBC, don't also set the parameters' column length, resulting in errors such as:

EOdbcException {Message:"TSqlDBOdbcStatement - TOdbcLib error: [22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]

Operand type clash: varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'InsPumps') collation_name = 'Greek_CI_AS' is incompatible with 
nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'InsPumps') (206)

[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 

This is a known MS SQL Server limitation with a workaround given in the following stack overflow post:

https://stackoverflow.com/questions/510 … ted-column

So in my case, I defined service methods to update or add such an object, which used ADO parametrized queries with size set for all the corresponding string properties.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: ORM limitations in MS SQL Virtual Tables with Always Encrypted Columns

Thanks for the feedback and providing a workaround.