Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 33 of 212

23 C H A P T E R 2 | Better security 2. Write a .NET application using ADO.NET to process the encryption of both existing and updated rows. 3. Run the .NET application built in the prior step. 4. Drop the existing table and rename the new table to use the old table name. 5. Change the application's connection string to include Column Encryption Setting=enabled. Note For nonproduction environments, you can use the Always Encrypted wizard or the Import/Export wizard in SSMS, which follow a process similar to the one we outline in this section. Step 1: Build a new staging table Because Always Encrypted does not support the conversion of an existing table into an Always Encrypted table, you must build a new table. The new table should have the same schema as the existing table. When you build the new table, the only changes you need to make are enabling the columns to be encrypted and specifying the collation as described in Example 2-3. A large application is likely to require a large amount of time to encrypt and move the data, and it might not complete this process during a single maintenance window. In that case, it is helpful to make two additional schema changes. The first change is to add a column on the production table to track when a row is updated (if the table does not already have such a column). The second change is to add a trigger to the production table that fires on delete and removes any rows from the new table when the row is deleted from the production table. To reduce downtime when you move the table with the encrypted data into production, you should create any indexes existing on the production table on the new table before loading it with data. Steps 2 and 3: Write a .NET application to encrypt the data and move it to the new table Because of the design of Always Encrypted, data is encrypted only by applications using the ADO.NET driver with parameterized queries. This design prevents you from using SSMS to move data into the new table. Similarly, you cannot use an application to perform a simple query such as this: INSERT INTO NewTable SELECT * FROM OldTable; The rows must be brought from the database into a .NET application and then written back to the database using a parameterized query, one row at a time, for the data to be properly inserted as encrypted values in the database. For small applications, this process can be completed quickly, within a single maintenance window. For larger applications, this processes may take several nights, which requires the application to be aware of data changes during the business day. After the application has processed the initial push of data from the source table to the new table, the application must run periodically to move over any changed rows to the new table until the cutover has been completed. Step 4: Rename the table Once all the data has been migrated, the existing table can be dropped or renamed so that it can be saved until testing has been completed. Then the new table can be renamed so that it now has the production table's name. Any indexes existing on the production table that do not exist on the new table should be created at this time, as well as any foreign keys that exist on the old table. Once testing is completed, if the old table is not deleted, any foreign keys using that table as a parent should be removed to prevent issues when rows are deleted.

Articles in this issue

Archives of this issue

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