Always Encrypted (AE) is exclusively for users of SQL Server and Azure SQL Database. AE is a must for anyone using these DBs who has a need to protect confidential information such as credit card or social security numbers. No other relational database system has a similar capability. However, it is certainly not for those using other databases. And SQL Server consultant Brent Ozar said to skip it if you need to do extensive searches and queries on your databases. AE makes such searches difficult, if not impossible. His advice is to only use Always Encrypted on limited amounts of data that you don’t search on or display to end users.
Always Encrypted is a feature designed to ensure?that sensitive data and its corresponding encryption keys are never revealed in plaintext to the database system. With Always Encrypted enabled, a SQL client driver encrypts and decrypts data?inside client applications or application servers by?using keys stored in a trusted key store, such as Azure Key Vault or Windows Certificate Store on a client machine. As a result, even database administrators, other high privilege users, or attackers gaining illegal access to Azure SQL Database?cannot access the data.
Database Security Features Rated
Databases Served: Good
Always Encrypted is applicable to any user databases containing sensitive data in SQL Server 2016+ and Azure SQL Database. It is not available in Azure SQL Data Warehouse. Always Encrypted with secure enclaves, an evolution of the current AE functionality, will be available in CTP 2.0 of SQL Server vNext.
Problem Solved: Best In Class
A common side effect of client-side encryption is that the server cannot perform any computations on encrypted data. As a result, users need to re-engineer their apps to load all data and perform required operations outside of the database. This is not always feasible due to the volume of data. AE addresses this problem by using deterministic encryption, which allows SQL Server/Azure SQL Database to support equality comparison (point lookup searches, equality joins) on encrypted database columns. Always Encrypted with secure enclaves will substantially expand the list of operations available on encrypted columns on the server side to include pattern matching, range comparisons and cryptographic operations (e.g. initial data encryption or key rotation). Such operations will be performed inside secure enclaves, which are trusted execution environments, on the server side.
AE uses a SQL client driver that encrypts and decrypts sensitive data?inside client applications or application servers by?using keys stored in a trusted key store, such as Azure Key Vault or Windows Certificate Store. AE can guarantee full isolation of data in the Azure SQL Database?from the cloud provider when?client applications or?middle tier services are hosted?on premises. For all-Azure apps, AE substantially reduces the attack surface area by removing the database from it. As DBAs do not have access to the keys,?they can administer the database without having access to sensitive data in plaintext. If a malicious user or malware attempts to scan the memory of the database system or generate a memory dump, they will not be able to get plaintext data.
There are no limits in terms of the number of users or database size. However, encryption has its overheads (for AE, mainly on the client side). Therefore, it is recommended that users pick sensitive data columns for encryption and not attempt to encrypt all database columns. Instead, the recommendation is to use TDE (encryption at rest) for the database as a whole, and apply AE for sensitive data.
Value: Very Good
AE is free, but Microsoft cautions users to limit Always Encrypted “to protect highly sensitive data from high-privilege users and malware in the database environment.”
Other tools should otherwise be used, such as Transparent Data Encryption (TDE) as the first line of defense (and to meet common compliance requirements) to encrypt the entire database at rest, and Transport Layer Security (TLS) to protect all traffic to the database.
Always Encrypted is a feature included in Microsoft SQL Server and in the Microsoft Azure SQL Database. To set it up for an Azure SQL database, a wizard helps the user to create a column master key and a column encryption key, then an application inserts, selects, and displays data from the encrypted columns. After you configure the database to use AE, you create a client application in C# with Visual Studio to work with the encrypted data. A database client driver library on the application side automatically detects data that targets encrypted database columns and transparently encrypts the data with the right keys. Similarly, the client driver library transparently decrypts any data retrieved from encrypted database columns. Customers do not have to explicitly call crypto routines to encrypt or decrypt the data.
Management: Very Good
AE is deeply integrated with both database system and client-driver libraries. Database information is automatically encrypted and decrypted without the need for the user to initiate these actions.
Support: Very Good
Built-in integration with popular key stores/key management solutions (Windows Certificate Store, Azure Key Vault, Hardware secure modules). Broad client driver support (ODBC, JDBC, .NET, PHP).
AE is available for Azure SQL Database but not Azure SQL Data Warehouse. AE with secure enclaves will not support Azure SQL Database, although those additional features are on the development roadmap.
SQL Server can perform computations on sensitive data without exposing the data to DBAs, cloud admins or system admins. In the current version, the supported computations are limited to equality comparison, but AE with secure enclaves (coming soon) will include pattern matching, range comparisons and crypto operations.