Bulk copying encrypted data

bcp transfers encrypted data in and out of databases in either plain text or cipher text form. By default, bcp copies plain text data, processing them as follows:

This example copies the customer table out as plain text data in native machine format:

bcp uksales.dbo.customer out uk_customers -n -Uroy -Proy123

If the data to be copied out as plain text is encrypted by a key that uses an explicit password, you can supply that password to bcp using the --c password or --colpasswd options.

For example, if the salary column in the employee table is encrypted by a key that is protected by an explicit password, you can only copy out the salary data as plain text by providing bcp with the password, as follows:

bcp hr.dbo.employee out  -c -Upjones -PX15tgol --colpasswd hr.dbo.employee.salary '4mIneIsonly'

Alternatively, if you know the name of the key that encrypts the salary column, you can use:

bcp hr.dbo.employee out  -c -Upjones -PX15tgol --keypasswd keydb.dbo.hr_key '4mIneIsonly'

bcp uses the password to issue a set encryption passwd command before selecting the data. Use the --keypasswd and --colpasswd options in a similar way on the bcp command line when copying the data back in.

Use the -C option for bcp to copy the data as cipher text. When copying cipher text, you may copy data out and in across different operating systems. If you are copying character data as cipher text, both platforms must support the same character set.

The -C option for bcp allows administrators to run bcp when they lack decrypt permission on the data. When the -C option is used, bcp processes data as follows:

The following example copies the customer table. The cc_card column is copied out as human-readable cipher text. Other columns are copied in character format. User “roy” is not required to have decrypt permission on customer cc_card.

 bcp uksales.dbo.customer out uk_customers -C -c -Uroy -Proy123

When copying data as cipher text, ensure that the same keys are available in the database when the data is copied back in. If necessary, use the ddlgen utility to move keys from one database to another.