Worldwide Locations
Abstract
With Microsoft SQL Server 2016, you can distribute your data to the cloud using the Stretch Database feature to improve local access while allowing cloud access to large amounts of historical data. You can also provide transparent end-to-end security using Always Encrypted technology and track changes automatically through temporal data. Download this white paper to learn why these features and more make SQL Server 2016 Microsoft's most important release to date.
Sample
Microsoft SQL Server 2016 Themes
Three underlying themes categorize the SQL Server 2016 release:
- Mission Critical Performance
- Deeper Insights Across Data
- Hyperscale Cloud
Within this white paper, we will be exploring one selected new feature under each theme to get a sense of the capabilities of the new release. Other features, while too numerous to describe here, are documented on the Microsoft website. You can also download an evaluation copy of the software from the same location. Of course, all the functionality of Microsoft SQL Server 2016 will be contained in the Microsoft Official Curriculum (MOC) courses offered by Global Knowledge both in the physical and virtual classroom platforms when available.
Mission Critical Performance
Selected Feature: Always Encrypted
Starting with SQL Server 2005, Microsoft allowed column-level encryption natively within the database engine. Sensitive columns could be encrypted by an application and decrypted as needed providing an “end-to-end” security option. Selected columns would be encrypted on disk, on backup, in memory, and over the network. However, column-level encryption required application code changes to use functions such as EncryptByKey and DecryptByKey. A Database Encryption Key and an appropriate Certificate were needed to be accessible for encryption or decryption to occur successfully. With SQL Server 2008, Microsoft introduced Transparent Data Encryption (TDE) as a feature that would automatically encrypt a whole database without having to change application code even for third-party applications. A major benefit was that the “data at rest” would be encrypted, including database files on disk and backups on tape or disk. This feature provided additional security to counter possible physical security vulnerabilities. However, as soon as data was “in motion,” for instance, into memory or over the network, the information was automatically decrypted and therefore not protected.
Now with SQL Server 2016, Microsoft has implemented an “Always Encrypted” option which includes end-to-end encryption without the need for application code changes therefore providing a best-of-both-worlds solution. A major benefit of this strategy is that Always Encrypted provides a separation between users who own the data and users who manage the data, such as administrators, because the encryption/decryption occurs at the client layer. With the advent of cloud-based data, this separation is especially important. A Column Master Key (CMK) and Column Encryption Key (CEK) are required to be accessible for encryption or decryption to occur successfully using an Always Encrypted-enabled driver installed on the client computer.
Although the application code does not need to change, the data definitions for sensitive columns will need to be redefined to include the ENCRYPTED WITH clause (see Figure 1).
The ENCRYPTION_TYPE clause for a column can be DETERMINISTIC or RANDOMIZED. The DETERMINISTIC setting means that, given a certain input data, the encrypted data will always be the same output. This option allows grouping, filtering, joining, and indexing with encrypted values. However, it may provide an opportunity for unauthorized users to deduce data for columns with few distinct values. The RANDOMIZED setting provides more security by encrypting in a less predictable way; however, it does not provide support for grouping, filtering, joining, and indexing with encrypted values.
Two types of encryption keys are required for encryption: A Column Encryption Key (CEK) and a Column Master Key (CMK). The CEK is used to perform fast, synchronous data encryption while the CMK is used to encrypt the CEK asynchronously for high security. The CMK must be available in a Trusted Key Store, typically using a Certificate, on the client machine. See Figure 2 for an example of Column Encryption Key Properties within SQL Server Management Studio (SSMS).
Once the appropriate keys and certificates are available and authorized to a client, a special connection string option needs to be specified, namely, “Column Encryption Setting=Enabled” (see Figure 3).
If any of the required objects are inaccessible or unauthorized, decryption will not occur and data values will be presented as encrypted, assuming the user has security permissions on the data columns themselves (see Figure 4) for an example where decryption is unauthorized but a user, such as an administrator, has access permissions.
Always Encrypted is therefore a mission-critical feature of Microsoft SQL Server 2016 for both “on-premises” and “cloud-based” data that require an automatic and transparent end-to-end security solution.