Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted February 1, 2016

Exploring SQL Server 2016 Always Encrypted – Part 4 – Encrypting Existing Data - Page 2

By Greg Larsen

Encrypting Existing SQL Server Data using Import/Export Wizard

For the next example I’m going to use the Import/Export Wizard to encrypt my unencrypted confidential data.   In order to have the Import/Export wizard encrypt the data you have a CMK and CEK setup in the target database.  Since I’m going to use the same database as I did in the prior example these keys are already set up.  If I was using a new database then I could follow the same procedures I did in the prior section to set up the CMK and CEK.  Once those key are set up it is as simple as going through the wizard to encrypt the data. 

Since I’m going to use the same database as I used in the prior section my unencrypted confidential data now resides in a table name MyConfidentialDataOriginal.  I’m going to move the data from this table to a table named MyConfidentialDataTemp2 using the wizard.  Since my CMK and CEK are already set up in my database the only other prior encryption step I need to take is to create the MyConfidentialDataTemp2 target table for the import/export process using the following script:

USE [Demo_Encrypt_Existing]
GO
CREATE TABLE [dbo].[MyConfidentialDataTemp2](
       [ConfidentialId] [int] ,
       [FirstName] [nvarchar](45),
       [LastName] [nvarchar](45),
       [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = Deterministic, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = Part4_Demo1
    ),
       [DriverLicenseNumber] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = RANDOMIZED, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = Part4_Demo1
    )
) ON [PRIMARY]

Now that my target table is set up, it is just a matter of running through the Import/Export wizard to migrate the data.

To get started in migrating my data using the wizard, I bring up SSMS on my CLIENT1 machine.  When I start SSMS I connect to SERVER1.  Once connected I right click on the Demo_Encrypt_Existing database, hover over the Tasks Item and then select the “Import data” item as shown below:

Import Data
Import Data

When I do this the following window is displayed:

Welcome to SQL Server Import and Export Wizard
Welcome to SQL Server Import and Export Wizard

On this window I just click on the “Next>” button.  When I do this the following “Choose a Data Source” window is displayed.  On this window I pick the following options to define my data source:

Choose a Data Source
Choose a Data Source

On this page I identified where the source data is that needs to be imported.  Note how I’m using the AppAdmin user to connect to the source data in my Demo_Encrypt_Existing database.  The data will be encrypted using the rights associated with the AppAdmin user.   When I click the “Next>” button the following “Choose a Destination” Window is displayed:

Choose a Destination
Choose a Destination

On this window I select the “.Net Framework Data Provider for SQLServer” for the destination, make sure the “Column Encryption Setting” is set to “Enabled”, and then set the UserID to AppAdmin, and enter the AppAdmin password in the Password item as shown below.

Select a Destination
Select a Destination

Once I’ve completed this window as shown above I click on the “Next>” button.  This brings up the following window:

Specify Table Copy of Query
Specify Table Copy of Query

On this window, I just take the defaults and click on the “Next>” button.  Doing this brings up the following window, where I select my source and destination table:

Select Source Tables and Views
Select Source Tables and Views

Once I’ve selected my source and destination tables I click on the “Next>” button, which brings up the following window:

Save and Run Package
Save and Run Package

On this window I just click on the “Finish>>|” button.  When I do that the following window is displayed:

Complete the Wizard
Complete the Wizard

Here I review the details of my migration and then click on the “Finish” button.  Which brings up this window:

The Execution was Successful
The Execution was Successful

As you can see my migration was successful and it migrated five rows.  I verified my data was encrypted by running a SELECT statement against the table MyConfidentialDataTemp2 and then checking to make sure the SSN and DriverLicenseNumber columns are encrypted.

Now that I have verified my data is encrypted it is a simple matter of executing a couple of RENAME statements like I did in the prior section to finish the migration.

For more information about using the Input/Export wizard you can refer to the following article:

http://blogs.msdn.com/b/sqlsecurity/archive/2015/07/28/encrypting-existing-data-with-always-encrypted.aspx



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM