Wednesday, November 27, 2024
Latest:

SQLChamp

Let's learn together!

Azure Data PlatformSQLAzureSQLServerSQLServer on Azure VM

Limitations of Always Encrypted

Sarabpreet Singh Anand

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.

 

Tool Limitations

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

  1. Columns using one of the following datatypes: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types.
  2. FILESTREAM columns
  3. Columns with ROWGUIDCOL property
  4. String (varchar, char, etc.) columns with non-bin2 collations
  5. Columns that are keys for nonclustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
  6. Columns that are keys for clustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
  7. Columns that are keys for fulltext indices containing encrypted columns both randomized and deterministic
  8. Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted)
  9. Sparse column set
  10. Columns that are referenced by statistics
  11. Columns using alias type
  12. Partitioning columns
  13. Columns with default constraints
  14. Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported)
  15. Primary key columns when using randomized encryption (deterministic encryption is supported)
  16. 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
  17. Columns referenced by check constraints
  18. Columns in tables that use change data capture
  19. Primary key columns on tables that have change tracking
  20. Columns that are masked (using Dynamic Data Masking)
  21. Columns in Stretch Database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)
  22. Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported)
  23. Columns in table variables

 

The following clauses cannot be used for encrypted columns:

  1. FOR XML
  2. FOR JSON PATH

 

The following features do not work on encrypted columns:

  1. Transactional or merge replication
  2. 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. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox