Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 32 of 212

22 C H A P T E R 2 | Better security Another change required to make Always Encrypted work is the addition of a connection string attribute to the connection string of the application connecting to the database: Column Encryption Setting=enabled With this setting added to the connection string, the ADO.NET driver asks the SQL Server if the executing command includes any encrypted columns, and if so, which columns are encrypted. For high-load applications, the use of this setting may not be the best practice, especially if a large percentage of executing commands do not include encrypted values. Consequently, the .NET Framework provides a new method on the SqlConnection object called SqlCommandColumnEncryptionSetting, which has three possible values as shown in the following table. Method value Effective change Disabled There are no Always Encrypted columns or parameters to use for the queries that are executed by using this connection object. Enabled There are Always Encrypted columns and/or parameters in use for the queries that are executed by using this connection object. ResultSet There are no Always Encrypted parameters. However, executing queries using this connection object return columns encrypted by using Always Encrypted. Note Be aware that the use of this method can potentially require a significant amount of change to your application code. An alternative approach is to refactor your application to use different connections. For the best performance of SQL Server, it is wise to request only the metadata about Always Encrypted for those queries that use Always Encrypted. This means that in applications for which a large percentage of queries use Always Encrypted, the connection string should be enabled and the specific queries within the application should specify SqlCommandColumnEncryptionSetting as Disabled. For applications for which most queries are not using Always Encrypted values, the connection string should not be enabled, and SqlCommandColumnEncryptionSetting should be set for Enabled or ResultSet as needed for those queries that are using Always Encrypted columns. In most cases, applications are able to simply enable the connection string attribute, and application performance will remain unchanged while using the encrypted data. Note While enabling the Always Encrypted setting has been designed to be an easy-to-implement solution for application data encryption, it is a very major change to application functionality. Like all major changes to application functionality, there should be rigorous testing of this feature in a testing environment, including load testing, before making this change in a production environment. Migrating existing tables to Always Encrypted In a production environment, there is no direct path to migrate an unencrypted table to a table that is protected by Always Encrypted. A multiphased approach to data migration is required to move data from the current table into the new table. The basic approach to move data from an existing table into an Always Encrypted table includes the following steps: 1. Build a new staging table.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016