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
Image


This should allow reading and writing to an SQL table with encrypted columns with the following considerations:

  1. 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.
     
  2. 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.