Tuesday, 28 November 2017

Always Encrypted VS Transparent Data Encryption (TDE)

What are the differences between Transparent Data Encryption (TDE) and Always Encrypted?
[TDE VS Always Encrypted]  

What are the differences between Always Encrypted and Transparent Data Encryption (TDE)?
[Always Encrypted VS TDE] 
Transparent Data Encryption (TDE)
Always Encrypted
The normal Column encryption which was introduced from SQL Server 2005 uses function EncryptByCert to encrypt the columns.
Always Encrypted is a new feature in SQL Server 2016.
TDE is database-level encryption.

Encryption happening during transport.

With always encrypted the encryption is done at clients app by API, like ADO.net, JDBC, ODBC. Drivers are installed at clients end to do this encryption.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data into the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
No driver is needed since it is database level. It works for all applications.
Driver specific (we see examples that support for .NET and Java). And not sure about when the application needs to change/upgrade the driver when the data encryption will work or not for existing data.
It is accessible to the database itself and to anyone who can own it and obtain the key/certificate/password.
Always Encrypted allows clients to encrypt sensitive data inside client applications, and never reveal the encryption keys to the Database Engine. However the certificate should be installed in Client machine for encrypting the data, in this case DBA/User can see the encrypted data and  by setting "Column Encryption Setting = Enabled" in the SQL Server Management Studio connection string from Client Machine.
Data is submitted as clear text in SQL Server and this data can be seen from traces. We can't make it as fully secured since DBA can access the data by providing the key/certificate.
Data can be protected and fully secured when the application is consuming the data through only Web services, hence the encryption is installed in only Web server for encrypting the data, However DBA who has admin access to SQL Server, can see the data by setting
 "Column Encryption Setting = Enabled"
 in the SQL Server Management Studio connection string from Client Machine as the Certificate is installed in the database server.
If DBA does not have access to the web server.
Encrypted data must be stored in Varbinary data type.  It is very easy to implement in existing database by adding an additional column with data type varbinary for storing the encrypt data.
It can be any data type except IDENTITY, ROWGUIDCOL, FILESTREAM (text/ntext/image/XML/), hierarchyid, geography, geometry alias types/user-defined data types
SQL_VARIANT rowversion (timestamp)) columns. It might be easy for new databases but  implementing the existing database we need to have a detailed study on the following limitations and it may have difficulties on implementing it in existing databases.
Displaying/Filtering the decrypted data can be achieved by creating View or Stored procedure.
Displaying/Filtering the decrypted data can be achieved by only when client application and if certificate is installed.
SQL greater/less than, pattern matching using the LIKE  operators can be used for filtering the encrypted data.
SQL greater/less than, pattern matching using the LIKE operators can't be used for filtering the encrypted data
It is proven since 10 years that there are no much limitations for this approach.
Since it is a new feature, Microsoft has mentioned lot of limitations as following. 

Always Encrypted is not supported for the columns with the below characteristics (e.g. the Encrypted WITH clause cannot be used in CREATE TABLE/ALTER TABLE for a column, if any of the following conditions apply to the column): 
·      Columns using one of the following data types: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types. FILESTREAM columns

·       Columns with the IDENTITY property

·       Columns with ROWGUIDCOL property
String (varchar, char, etc.) columns with non-bin2 collations (Randomized Encryption)

·       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).

·       Table-valued parameters targeting encrypted columns are not supported.

The following clauses cannot be used for encrypted columns:

·       FOR XML

The following features do not work on encrypted columns:

·       Transactional or merge replication
·       Distributed queries (linked servers)

Tool Requirements :

·       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. Requires at least SQL Server Management Studio version 17 to insert, update, or filter encrypted columns.

·       Encrypted connections from sqlcmd require at least version 13.1, which is available from the Download Center.

Key management:

(creating/changing/reviewing key metadata in the database) is not possible.

·       The permissions apply to actions using Transact-SQL, Management Studio (dialog boxes and wizard), or PowerShell.
·       The two VIEW permissions are required when selecting encrypted columns, even if the user does not have permission to decrypt the columns.
·       In SQL Server, both VIEW permissions are granted by default to the public fixed database role. A database administrator may choose to revoke (or deny) the VIEW permissions to the public role and grant them to specific roles or users to implement more restricted control.
·       In SQL Database, the VIEW permissions are not granted by default to the public fixed database role. This enables certain existing, legacy tools (using older versions of DacFx) to work properly. Consequently, to work with encrypted columns (even if not decrypting them) a database administrator must explicitly grant the two VIEW permissions.