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
|
|
|
1
|
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.
|
|
2
|
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. |
|
3
|
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.
|
|
4
|
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.
|
|
5
|
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. |
|
6
|
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. |
|
7
|
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.
|
|
8
|
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
|
|
9
|
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
· FOR
JSON PATH
|
||
|
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.
|
||
|
IMPORTATNT NOTES:
· 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.
Reference:
|
