In the last blog post, we understood the basic concept of Always Encrypted, different keys and the available Encryption types. In this blog post we’ll see different Limitations of Always Encrypted feature, in-case you want to move to actual Implementation steps click here to know how to configure Always Encrypted on existing table with data.
SQL Server Management Studio can decrypt the results retrieved from encrypted columns if you connect with the column encryption setting=enabled in the Additional Properties tab of the Connect to Server dialog, except encrypted varbinary(max), nvarchar(max) andvarchar(max) values. SQL Server Management Studio does not support inserting, updating, or filtering encrypted columns.
- Encrypted connections from sqlcmd require at least version 13.1, which is available from the Download Center. <https://www.microsoft.com/en-us/download/details.aspx?id=53591>
Before we look into the actual implementation & testing let’s see the list of columns types which are not supported by Always Encrypted means you can’t use encrypted with clause with any of these columns:
Limitations of Always Encrypted:
- Columns using one of the following datatypes: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types.
- FILESTREAM columns
- Columns with ROWGUIDCOL property
- String (varchar, char, etc.) columns with non-bin2 collations
- Columns that are keys for nonclustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
- Columns that are keys for clustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
- Columns that are keys for fulltext indices containing encrypted columns both randomized and deterministic
- Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted)
- Sparse column set
- Columns that are referenced by statistics
- Columns using alias type
- Partitioning columns
- Columns with default constraints
- Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported)
- Primary key columns when using randomized encryption (deterministic encryption is supported)
- Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms
- Columns referenced by check constraints
- Columns in tables that use change data capture
- Primary key columns on tables that have change tracking
- Columns that are masked (using Dynamic Data Masking)
- Columns in Stretch Database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)
- Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported)
- Columns in table variables
The following clauses cannot be used for encrypted columns:
- FOR XML
FOR JSON PATH
The following features do not work on encrypted columns:
- Transactional or merge replication
- Distributed queries (linked servers)
So these are the limitations of Always Encrypted which are not supported as of now in SQL Azure or SQL Server 2016.
Hope you got answer to your questions! Happy Learning!
feel free to leave a comment. 🙂