SQL Server 2016 Security Features to Look Forward To

Author by Amy Cousland

Psychiatric Hospital Loses Backup Data”…”Cardinals Sack Employee After Hack”…”LastPass Takes Steps to Protect Enterprise Partners in Wake of Data Breach

Headlines such as these are all too common. A company’s data can be at risk both externally and internally. With this in mind, Microsoft has added many features to make SQL Server more secure. With SQL Server 2016 on the horizon, let’s look at the main security features you can use to help protect your data.

Transparent Data Encryption

TDE, which has been in SQL Server since 2008, is designed to protect the data in the physical data and log files at all times. Data is encrypted as it’s written to disk, and decrypted as it’s read into memory for use.

Is it applied at the database or object level? TDE is enabled at the database level.

Where are keys stored? The database encryption key (DEK) is stored on the database server. To restore to another server, or use an HA/DR technology like Availability Groups or log shipping, you must restore the key to the second server as well.

Pros:

  • When enabled, the entire database is protected.
  • The data in the backup files is encrypted, as well.
  • No application changes are required.

Cons:

  • It’s an Enterprise Edition only feature.
  • If someone manages to get into the server, with high enough permissions, they can view the data in the database.
  • The resource cost to do the encryption and decryption is CPU, so adding this to a resource-constrained system is a bad idea.
  • The data is not encrypted after it leaves the database server, on its way to the client.

Backup Encryption

Backup Encryption, introduced in SQL Server 2014, is designed to ensure the data in backup files is protected by encryption.

Is it applied at the database or object level? The database level. The WITH ENCRYPTION clause can be used with full, differential, and log backups.

Where are keys stored? When taking the backup, a certificate or asymmetric key must be specified. When restoring the backup, the certificate or key must be present as well.

Pros:

  • It’s available in Standard Edition.
  • In addition to on-premises databases, it can also be used with SQL Server Managed Backup to Azure, which means offsite backups are more secure.

Cons:

  • You can’t restore an encrypted backup to an earlier version of SQL Server.
  • Because this encrypts the backup only, it won’t protect the data at rest on your SQL Server.

AlwaysEncrypted

AlwaysEncrypted is a new feature in SQL Server 2016. It’s designed to protect the data at all times by encrypting and decrypting within the client – the actual, unencrypted data is never known to SQL Server.

Is it applied at the database or object level? Encryption happens at the column level, so it’s a very targeted process.  Because the data is encrypted and decrypted at the client, the more data that is encrypted will mean more work and slower performance. Don’t try to encrypt the entire database!

Where are keys stored? The encryption keys are generated on the database server. A driver that encrypts and decrypts the data as it is sent to the database server is installed on the client.

Pros: Since this is a two-step encryption process, no one can get a copy of the database files or backup files and automatically have access to the encrypted data.

Cons:

  • The application connection string must be changed, which may not be possible with all applications.
  • It’s a first-version feature right now, which means there are limitations. For example, only equality comparisons (=) are allowed on the encrypted columns when using deterministic encryption, and no comparisons are allowed when using randomized encryption.

Row-Level Security

Row-level security first emerged in Azure SQL Database, and is part of the on-premises product in SQL Server 2016. With this feature, the data is not encrypted, but based on a user’s security level, they may not be able to see sensitive information. There are many articles online about how to implement this in SQL Server prior to it being a feature; now, it’s a recognized and supported need from Microsoft.

Is it applied at the database or object level? The data that is sensitive is marked at the row level. Individual users or groups are given rights to see the data. Note, this only applies to SELECT, UPDATE, and DELETE operations. Any user can INSERT data into the table.

Where are keys stored? Row-level security is based on a table-valued function which users are evaluated against, and a security policy that is applied to the table. No master keys or certificates are needed.

Pros: You can now control access to certain data within the database itself, rather than at the client level.

Cons:

  • RLS requires a table-valued function, and functions are terrible for performance. If you use this feature, write your functions well and test them thoroughly.
  • Also, if a user has direct access to the server, they will be able to see the data.

Secure Your Data

With the many security features available in SQL Server, it’s a good time to review your security needs. If there is room for improvement, consider implementing one or more of these features!