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:
Data is automatically encrypted by Adaptive Server before insertion when executing bcp in. Slow bcp is used. The user must have insert and select permission on all columns.
Data is automatically decrypted by Adaptive Server when executing bcp out. select permission is required on all columns; in addition, decrypt permission is required on the encrypted columns.
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:
Data is assumed to be in cipher text format during execution of bcp in, and Adaptive Server performs no encryption. Use the -C option only if the file being copied into Adaptive Server was created using the -C option on bcp out. The cipher text must have been copied from a column with exactly the same column attributes and encrypted by the same key as the column into which the data is being copied. Fast bcp is used. The user must have insert and select permission on the table.
bcp in -C bypasses the domain rule and check constraint for encrypted columns if either exist on an encrypted column because, in this situation, Adaptive Server uses fast bcp. Domain rules and check constraints do not affect bcp out -C.
If an access rule exists on an encrypted column, using bcp out -C results in a 2929 error. Access rules do not affect bcp in -C.
Data is copied out of Adaptive Server without decryption on bcp out. The cipher text data is in hexadecimal format. The user must have select permission on all columns. For copying cipher text, decrypt is not required on the encrypted columns.
Encrypted char or varchar data retains the character set used by Adaptive Server at the time of encryption. If the data is copied in cipher text format to another server, the character set used on the target server must match that of the encrypted data copied from the source. The character set associated with the data on the source server when it was encrypted is not stored with the encrypted data and is not known or converted on the target server.
You can also perform bcp without the -C option to avoid the character set issue.
You cannot use the -J option (for character set conversion) with the -C option.
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.
