Always Encrypted support in version 4.2 with SQL 2017

Posts   
 
    
trancehead
User
Posts: 137
Joined: 03-Dec-2007
# Posted on: 29-May-2020 14:30:09   

Is Always Encrypted supported in LLBLGen version 4.2 connecting to SQL 2017?

I ran a test by adding "Column Encryption Setting=enabled" to my connection string and encrypting a column on the database. The column is decrypted through SSMS but when attempting to access the data I get the following error:


System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Operand type clash: nvarchar(255) 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 = 'xxx') is incompatible with nvarchar
Statement(s) could not be prepared.
  Source=.Net SqlClient Data Provider
  StackTrace:
<Cannot evaluate the exception stack trace>

The post at https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=24102 suggests that there should be compatibility as only the connection string needs to change.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39613
Joined: 17-Aug-2003
# Posted on: 30-May-2020 11:39:51   

You forgot to call the static method on SqlConnection I think. See our docs: https://www.llblgen.com/Documentation/5.7/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/FeaturesPerDB/SqlServer.htm#sql-server-2016-always-encrypted-support

We don't have any extra code for this implemented, as per-column encryption is either done for you (using the connectionstring + static method route) or cumbersome and we didn't go there.

Frans Bouma | Lead developer LLBLGen Pro
trancehead
User
Posts: 137
Joined: 03-Dec-2007
# Posted on: 01-Jun-2020 09:19:18   

I thought the static method was only if I need to use a custom provider. However, adding it and using the Azure Key Vault provider solved the problem.

Thank you!