Always Encrypted Feature on SQL
kbt145231
PRODUCTK2 Five
K2 blackpearl 4.7
BASED ONK2 blackpearl 4.7
TAGSK2 SmartObject Integration
SmartObjects
SQL
This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice. This article is not considered official documentation for K2 software and is provided "as is" with no warranties.
Objective
How to read and write to a SQL table that has a column encrypted through the Always Encrypted feature in SQL.Before You Begin
The Always Encrypted feature was introduced into SQL Server 2016 and is also available on Azure SQL. Information regarding this feature is documented in this article.How-to Steps
Edit the SQL Service Instance to add this setting: Column Encryption Setting = Enabled
This should allow reading and writing to an SQL table with encrypted columns with the following considerations:
- Encrypted columns will not be surfaced as SmartObject properties. The datatype of an encrypted column is being returned as a varbinary data type. This is one of the unsupported data types in the SQL Server Service as per documentation.
- A stored procedure is required to perform an insert/update of entries inside this table. You may use this article as reference in creating stored procedures specifically for tables with the Always Encrypted feature implemented.