5 SQL Server 2014 Security Enhancements

SQL Server 2014 continues the Microsoft commitment to excellence in security. According to the National Institute of Standards and Technology (NIST) public security board, SQL Server reportedly has the lowest number of security vulnerabilities across major database vendors. In addition, SQL Server has been deemed "the most secure database" by the Information Technology Intelligence Consulting (see, "SQL Server Delivers Industry-Leading Security," 2010 and "Stratus ftServer and SQL Server Deliver Continuous Availability," 2012).

SQL Server 2014 offers Common Criteria (CC) Certification at the High Assurance Level, as well as "enhanced separation of duty" for greater security. Additional new features include Transparent Data Encryption for better data protection and Encryption Key Management, allowing the storage of keys on a separate server.

1. Transparent Data Encryption (TDE)

In SQL Server 2014, TDE functionality takes the non-encrypted backup data and encrypts the data before writing it to disk. In order to preserve a major compression factor of SQL Server backup compression in conjunction with the newly introduced encryption, the compression is performed on the backup data first, before the encryption is applied to the compressed data. With this order of actions, a major compression factor can be preserved while the backup as it rests on the backup vehicle is encrypted.

Related: Transparent Data Encryption FAQs

2. Encryption Key Management

As discussed above, to secure your backup data in SQL Server 2014, you can now select to encrypt when you create a backup. The encryption options include an encryption algorithm and a certificate or asymmetric key to be used for the encryption. Only asymmetric keys residing in the Extended Key Management is supported.

Multiple encryption algorithms up to AES 256-bit are supported giving you a variety of algorithms to choose from. Adding backup encryption to a TDE encrypted database, gives additional protection for your data.

You can control the process using PowerShell, SQL Server Management Objects (SMO), or SQL Server Management Studio (SSMS), or T-SQL. Encryption options can also be used when configuring SQL Server Managed Backup to Windows Azure, therefore providing additional security for your off-site data.

It is very important that the certificate or key used for encrypting must be backed up to a different location than the backup that the certificate or key is used to encrypt.  Without this certificate or key the backup file cannot be restored. For more information, see: Backup and Restore Enhancements in SQL Server 2014 CTP2.

New Server-Level Permissions

SQL Server 2014 also has 3 new server-level permissions.

3. CONNECT ANY DATABASE Permission

Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. This permission does not grant any permission in any database beyond connect. You may combine CONNECT ANY DATABASE permission with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.

4. IMPERSONATE ANY LOGIN Permission

When granted, IMPERSONATE ANY LOGIN allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases. When denied, a high privileged login can be blocked from impersonating other logins. For example, a login with CONTROL SERVER permission can be blocked from impersonating other logins.

5. SELECT ALL USER SECURABLES Permission

When SELECT ALL USER SECURABLES permission is granted, a login such as an auditor can view data in all databases that the user can connect to. For additional information, see What's New (Database Engine)

Related: Important New Features in SQL Server 2014