The Microsoft SQL Server 2017 is geared towards making SQL Server a platform that will enable users to make their choice of development languages, on-premises or cloud, data types, and operating systems.

With the constant reports of security vulnerabilities and breaches witnessed regularly, protecting data with the latest technology should always be on everybody’s mind.

Microsoft added a ton of new features in SQL Server 2017 that supports creating secure database applications. This article will expose the latest security abilities in SQL Server 2017 such as dynamic data masking, Common Language Runtime, row level security, always encrypted and identity caching.

Dynamic data masking is one special security feature that enables you to encrypt sensitive data on certain columns. However, it is still possible for users to access some information by filtering these encrypted rows to find matching data based on predicates. To prevent this scenario, it is important for you to disable ad hoc querying for these users thereby restricting their access to apply where clauses to the queries.

Unlike other versions of Microsoft SQL Servers, in SQL 2017, all assemblies that were previously considered as ‘safe’ or possessing an ‘external access’ as permission set, are currently treated as unsafe assemblies. Microsoft had to upgrade the security features of SQL 2017 to ensure that all assemblies need to be signed with either an asymmetric key or a certificate.

With the aid of Row Level Security (RLS), the coding and design of the security apparatus in your application is simplified as well as handling application security. Asides that, RLS also makes it possible for you to install restrictions on how data row can be accessed by anyone.

For instance, you can create a restriction to ensure that employees are only able to access those data rows that belong to their department. If this is implemented, an employee will only be able to view his own records his and that of his team members while still being able to access the same table. In an organization, access to a customer’s data can also be restricted to the row (data) relevant to their company.

Although this feature was present in SQL Server 2016, “Always Encrypted” still maintained its position in SQL Server 2017. This feature will be very useful if you have high-sensitive data that is not being ad-hoc queried.

Sensitive data like credit card information should be used with always encrypted, as it enables the customer to store his credit card information through the application in a trusted key store. The SQL admins will not even be able to access this information because the SQL Server cannot access the keys in the trusted key store.

In the older versions of SQL Server, the identity cache would be cleared by default if the SQL Server wasn’t shut down properly thereby creating gaps in your identity values. However, Microsoft SQL 2017 can be able to resolve this issue by allowing you to turn off identity caching by the database. By turning off your identity caching, your identity values will no longer have gaps if the SQL Server restarts unexpectedly or fails.