Introduction to SQL Server 2016: new features & improving security

Anton Merinov

Anton Merinov

The author of the article. Interests, skills: Professional administration of Oracle Database, web development, IT-World. More details.

SQL Server 2016 new features and security improvmentSQL Server is the main relational database management product from Microsoft. It has been around in one form or another since the late 1980s (developed in partnership with Sybase), but as a standalone Microsoft product since the early 1990s. During the last 20 years, SQL Server has changed and evolved, gaining newer features and functionality along the way.

The SQL Server we know today is based on what was arguably the most significant (r)evolutionary step in its history, with the release of SQL Server 2005. The changes that were introduced have allowed the versions that followed the 2005 release to take advantage of newer hardware and software improvements such as: 64-bit memory architecture, better multi-CPU and multi-core support, as concludes the overview of programming better alignment with the .NET framework, and many more modernizations in general system architecture.

The incremental changes introduced in each subsequent version of SQL Server have continued to improve upon this solid foundation. Fortunately, Microsoft have changed their release cycle for multiple products, including SQL Server, resulting in shorter timeframes between releases. This has, in part, been due to Microsoft's focus on their much reported "Mobile First, Cloud First" strategy. This strategy, together with the development of the cloud version of SQL Server "Azure SQL Database", has forced Microsoft into a drastically shorter release cycle. The advantage of this strategy is that we are no longer required to wait three to five years for a new release (and new features). There have been releases every two years since SQL Server 2012 was introduced, with multiple releases of Azure SQL Database in between the real versions. While we can be pleased that we no longer need to wait for new releases, we are also at a distinct disadvantage. The rapid release of new versions and features leaves us developers with ever decreasing periods of time to get to grips with the shiny new features. Previously, versions had many years between releases, allowing us to build up a deeper knowledge and understanding of the available features before having to consume new information.

In this article, we will introduce what's new inside SQL Server 2016. We will outline features that are brand new in this release of the product and look at features that have been extended or improved upon.

We will be outlining new features in the following areas:

  • Security
  • Engine features
  • Programming
  • Business intelligence

Let's start with 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.


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.


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.

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.

Вас заинтересует / Intresting for you:

Introduction to the Family of ...
Introduction to the Family of ... 798 views Doctor Thu, 10 Oct 2019, 10:04:22
Automating the SQL Server Inst...
Automating the SQL Server Inst... 1346 views Doctor Sun, 02 Dec 2018, 08:44:21
Installing a MS SQL 2016 failo...
Installing a MS SQL 2016 failo... 1544 views Arrived Topple Thu, 23 Aug 2018, 11:09:22
Automating Microsoft SQL Serve...
Automating Microsoft SQL Serve... 2721 views Doctor Sun, 02 Dec 2018, 11:50:16