Chapter 8: Tightening the SecuritySQL Server 2016 introduces three new security features. With Always Encrypted SQL Server finally enables full data encryption, so that no tools or persons regardless their database and server permissions can read encrypted data except the client application with an appropriate key. Row-level security on the other side restricts which data in a table can be seen by specific user. This is very useful in multi-tenant environments where you usually want to avoid data-reading intersection between different customers. Dynamic data masking is a soft feature that limits sensitive data exposure by masking it to non-privileged users.
SQL Server 2016 Developer's Guide William 11
The third major security feature to be introduced in SQL Server 2016 is Always Encrypted. Encryption with SQL Server was previously a (mainly) server-based solution. Databases were either protected with encryption at the database level (the entire database was encrypted) or at the column level (single columns had an encryption algorithm defined). While this encryption was and is fully functional and safe, crucial portions of the encryption process (for example, encryption certificates) are stored inside SQL Server. This effectively gave the owner of a SQL Server instance the potential ability to gain access to this encrypted data; if not directly, there was at least an increased surface area for a potential malicious access attempt. As more and more companies moved into hosted service and cloud solutions (for example, Microsoft Azure), the old encryption solutions no longer provided the required level of control and security. Always Encrypted was designed to bridge this security gap by removing the ability of an instance owner to gain access to the encryption components. The entirety of the encryption process was moved outside SQL Server and resides on the client-side. Previously, you could achieve a similar effect using a homebrew solution, but Always Encrypted provides a fully integrated encryption suite into both the .NET Framework and SQL Server. Whenever data is defined as requiring encryption, the data is encrypted within the .NET Framework and only sent to SQL Server after encryption has occurred. This means that a malicious user (or even system administrator) will only ever be able to access encrypted information should they attempt to query data stored via Always Encrypted. 2ff7e9595c