Security

The last few years have provided frequent demonstrations of the importance of security in IT. Whether we consider the repercussions of recent, high profile data leaks, or the multiple cases of data theft by hacking. While no system is completely impenetrable, we should always consider how we can improve security in the systems we build. These considerations are wide-ranging and sometimes even dictated by rules, regulations, and laws. Microsoft has responded to the increased focus on security by delivering new features to assist developers and DBAs in their search for more secure systems. The security features in SQL Server 2016 have been designed to make improving the security of SQL Server based solutions even easier to implement.

Row Level Security

The first technology that has been introduced in SQL Server 2016 to address the need for increased and improved security is Row Level Security (RLS). RLS provides the ability to control access to the rows in a table based on the user executing a query. With RLS it is possible to implement a filtering mechanism on any table in a database completely transparently to any external application or direct T-SQL access. The ability to implement such filtering without having to redesign a data access layer allows system administrators to control access to data at an even more granular level than before.

The fact that this control can be achieved without any application logic redesign makes this feature potentially even more attractive to certain use cases. RLS also makes it possible, in conjunction with the necessary auditing features, to lock down a SQL Server database so that even the traditional "god-mode" sysadmin cannot access the underlying data.

Note

Further details for Row Level Security can be found in Chapter 8, Tightening the Security.

Dynamic Data Masking

The second security feature that we will be covering is Dynamic Data Masking (DDM). DDM allows the system administrator to define column level data masking algorithms that prevent users from reading the sensitive content of columns, while still being able to query the rows themselves. This feature seems to have been initially aimed at allowing developers to work with a copy of production data without having the ability to actually see the underlying data. This can be particularly useful in environments where data protection laws are enforced (for example, credit card processing systems, medical record storage). The data masking occurs for unauthorized users at query runtime and does not affect the stored data of a table. This means that it is possible to mask a multi-terabyte database through a simple DDL statement, rather than resorting to the previous solution of physically masking the underlying data in the table we want to mask. The current implementation of DDM provides the ability to define a fixed set of functions to columns of a table, which will mask data when a masked table is queried. If a user has permission to view the masked data, then the masking function(s) are not run, whereas a user without those permissions will be provided with the data as seen through the defined masking functions.

Note

Further details for Dynamic Data Masking can be found in Chapter 8, Tightening the Security.

Always Encrypted

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.

Note

Further details for Always Encrypted can be found in Chapter 8, Tightening the Security.

This concludes the overview of the three main security enhancements inside SQL Server 2016. Microsoft has made some positive progress in this area. While no system is completely safe, and no single feature can provide an all-encompassing solution, each of these three features provide a further option in building up, or improving upon, any system's current security level. As mentioned for each feature, consult the dedicated chapter to explore how each feature functions and how they may be used in your environments.