bcp transfers encrypted data in and out of databases in either plaintext or ciphertext form. By default, bcp copies plaintext data. bcp processes plaintext data files 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 plaintext data in native machine format:
bcp uksales.dbo.customer out uk_customers -n -Uroy -Proy123
Use the -C option for bcp to copy the data as ciphertext. When copying ciphertext, you may copy data out and in across different operating systems. If you are copying character data as ciphertext, 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 ciphertext format during execution of bcp in, and Adaptive Server performs no encryption. Use the -C option with bcp in only if the file being copied into Adaptive Server was created using the -C option on bcp out. The ciphertext 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.
Data is copied out of Adaptive Server without decryption on bcp out. The ciphertext data is in hexadecimal format. The user must have select permission on all columns. For copying ciphertext, 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 ciphertext 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 ciphertext. 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
