SQLSERVER: BCP import and encrypt columns using Database Key

user_6234075 0
Viewed 283

I have a large file which needs to be imported to SQL Server. File contains columns of personal information (like first_name, phone_number). Currently I'm importing the large file onto SQL Server using BCP tool. And as a next step, I'm encrypting the columns using Database Key as shown below.

CREATE TABLE users (
    first_name VARCHAR(4000)
)

CREATE CERTIFICATE db_cert1
WITH SUBJECT = 'Encrypt PII data'; 
GO 

CREATE SYMMETRIC KEY db_symkey1
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE db_cert1;
GO

BEGIN TRY
    UPDATE users
    SET first_name = CAST(EncryptByKey(KEY_GUID('db_symkey1'),[first_name]) AS VARCHAR(MAX))
END TRY
BEGIN CATCH
    DELETE FROM users;
END CATCH

There are 100s of columns in my table and 10s of such sensitive columns which needs encryption and millions of rows. Currently it is slow (due to number of rows and VARCHAR(MAX/4000))

Is there a better way to achieve this? Does BCP offer any out of the box solution?

bcp import encryption sql-server database
2 Answers

I guess you are preforming cast to nvarchar(max) because of your fields type. It will be better, to use varbinary instead.

The function EncryptByKey returns:

varbinary with a maximum size of 8,000 bytes.

So, storing your data in this format will remove the need of cast. Also, it will be better to use precise value for the varbinary length.

You can use the formula bellow, to check what's the maximum varbinary's length that EncryptByKey will return for specific text column:

60 + max_length - ((max_length + 8) % 16)

I often use the following script:

SELECT name,  60 + max_length - ((max_length + 8) % 16) 
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.securityUsers')
    AND name in ('FirstName', 'LastName', 'Gender', 'Address1', 'Address2', 'City', 'Province', 'Country')

For example, for nvarchar(128), you will have varbinary(308). You just need to have some way to know that when you are decrypting, tho cast to nvarchar(128) again.

Generally, try to use types with smallest possible precision and to cast to smallest possible precision, too.

You can for example to insert these data in a buffer table and then to just encrypt it and record it in the target table (without casting).

user_10803540

Below are the steps followed to improve performance.

  1. Created two columns for each sensitive data
    • first_name_plaintext VARCHAR(256)
    • first_name VARBINARY(308)
    • Thanks @gotqn for this
  2. Added an auto incrementing id column, added a clustered index(this makes sure its already sorted) on the table and did updates in batches (like WHERE [id] BETWEEN 1 AND 100000).
  3. Commit after each iteration (to reduce the transaction logs usage)
  4. Changed the DB Recovery model to Simple (IMPORTANT)
  5. Increased the DB file size
  6. If there are no restrictions, you can use AES_128 encryption for key creation instead of AES_256, but our security advisor didn't allow this.

This improved the time from 3 minutes to 1:17 minutes for 1 million records.

user_62340750
Related