BULK INSERT into a table using encryption

Question

BULK INSERT into a table using encryption

jhom

SSC-Addicted

Points: 443
More actions
November 13, 2013 at 10:41 am

#280194

Hi all,
I don't know if this can be done, but here goes:
1. I have a flat file containing employee numbers and their associated social security numbers
1,123456789
2,234567890
3,345678901
4,456789012
5,567890123
2. I want to insert them into a table
create table employee_ssn
(
employee_number int not null,
ssn_encrypted varbinary(256) null,
primary key (employee_number)
)
with a plain employee number and an encrypted ssn.
The way I'm doing it now is to use a programming language to loop through the data file and for each row call the stored procedure with the row fields as parameters
foreach (string row in dataFile)
{
call_stored_procedure(insert_employee_ssn, row.employee_number, row.employee_ssn)
}
create procedure insert_employee_ssn(@employee_number varchar(10),
@employee_ssn varchar(10))
as
begin
open symmetric key SOCIAL_SECURITY_NUMBER_KEY
decryption by certificate SOCIAL_SECURITY_NUMBER_CERTIFICATE
insert into employee_ssn(employee_number,
ssn_encrypted)
values (ltrim(rtrim(@employee_number)),
encryptByKey(key_guid('SOCIAL_SECURITY_NUMBER_KEY'),
cast(@employee_ssn as char(10))))
close symmetric key SOCIAL_SECURITY_NUMBER_KEY
end
Can this be done using a BULK INSERT?
Thanks!
John

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

yes. modify the stored procedure to do the bulk insert into your stage table first, then encrypt the column in place or encrypt by inserting into your final table with a single update statement:

Bulk Insert .....

update employee_ssn

set ssn_encrypted = encryptByKey(key_guid('SOCIAL_SECURITY_NUMBER_KEY'),

cast(employee_ssn as char(10))))

or ::

insert Table (employee_num, employee_ssn)

select employee_num, encryptByKey(key_guid('SOCIAL_SECURITY_NUMBER_KEY'),

cast(employee_ssn as char(10))))

from employee_ssn_stage

The probability of survival is inversely proportional to the angle of arrival.

jhom SSC-Addicted Points: 443 More actions · Answer 2

Thanks for the quick reply!

Do I need the open symmetric key and decrypt by certificate statements, like:

Bulk Insert ...

open symmetric key SOCIAL_SECURITY_NUMBER_KEY

decryption by certificate SOCIAL_SECURITY_NUMBER_CERTIFICATE

update employee_ssn

set ssn_encrypted = encryptByKey(...)

close symmetric key SOCIAL_SECURITY_NUMBER_KEY

Thanks, again!

John