Developers like to forget about security and simply leave security issues to database administrators (DBAs). However, it is much harder for a DBA to tighten security for a database where developers did not plan and design for security. To secure your data, you must understand the potential threats as well as the security mechanisms provided by SQL Server and the other components your application is using, including the operating system and programming language.

When talking about securing SQL Server, we are actually talking about defending data access to the database platform and guaranteeing the integrity of that access. In addition, you have to protect all SQL Server components included in your solution. Remember that your system is only as secure as the least secure component. As a defender, you have to close all holes, while an attacker only has to find a single hole. However, dealing with all aspects of security would be beyond the scope of this chapter. Therefore, this chapter will cover only the most important security features of the SQL Server Database Engine, and introduces three new SQL Server 2016 and 2017 security features.

With Always Encrypted, SQL Server 2016 and 2017 finally enable full data encryption, so that no tools or people, regardless of their database and server permissions, can read encrypted data except the client application with an appropriate key. Row-Level Security (RLS), on the other hand, restricts which data in a table can be seen by a specific user. This is very useful in multi-tenant environments where you usually want to avoid a 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.

This chapter will cover the following points:

• SQL Server security basics
• Data encryption
• Row-Level Security
• Dynamic data masking