Encryption and bulk insert

Hello,

I need to build a table with encrypted data with the source data coming from a .csv. The account number from the .csv will need to be encrypted in the final table and I will also need to have a hashed or MAC index on the account number. I'm guessing that I can do this with a stored procedure using a temp table to load the unedited data and then load the actual table from there. The table will be built from a download every evening. The key and certificate for the encryption are already set. I have been able to load data directly to the table providing values. But, I have been struggling getting a bulk insert to work.

I'd appreciate any ideas or examples.

Thanks!

Could you please explain what problems you are having with bulk insert? Do you have any error messages that can help us diagnose the problem? How are you doing the bulk insert? Are you using bcp or BULK INSERT or OPENROWSET(BULK)? Please post a simple repro of the problem if possible also.|||

Thanks for your quick reply! I have been working with encryption examples from Raul Garcia's demo for the encryption basics. But I need to be able to bulk load a lot of data and end up with an encrypted account number, and also need to be able to do look ups by the account number which is the reason for the MAC portion. This is what I'm working with. The bulk insert piece works fine. I got past that point when I was trying to use a trigger to do the encryption steps. I was able to get one record inserted when I did it that way. But then received an error about a duplicate key on the second record. A regular insert statement specifying values works fine also. With the code below I'm getting a syntax error on the select statement. Query analyzer doesn't like the two lines following the select. This is the error message:

Incorrect syntax near ','.
and it occurs for either line after the select. I experimented by commenting them out one at a time just to be sure. Any suggestions you have will be appreciated!

create procedure test_sp as
create table #InsertTmp
( acct_no_macHolder varchar(20), -- will build mac later
acct_no nvarchar(60), -- (unencrytped acctNo)
acct_lname char(24) ,
acct_fname char(24) )
BULK INSERT #InsertTmp
FROM 'd:\YukonTSQLLibrary\BulkTest.csv'
with (
DATAFILETYPE = 'char',
FIELDTERMINATOR='<F',
ROWTERMINATOR = '\n' )
GO
Insert into buildfiche (acct_no_index, acct_no_cipher, lname, fname)
select (
( dbo.MAC (acct_no, object_id('buildfiche') ) ), --LINE ERROR OCCURS ON
(encryptbykey(key_guid('key_Encryption'), acct_no)), --OTHER LINE ERROR OCCURS ON
lname,
fname)
from #InsertTmp
GO

|||

I don't know why you have so many paranthesis in the SELECT list. It makes it really hard to read. And looks like the columns in the SELECT list is also wrong. I simplified the SELECT statement to:

select dbo.MAC (acct_no, object_id('buildfiche')), --LINE ERROR OCCURS ON
encryptbykey(key_guid('key_Encryption'), acct_no), --OTHER LINE ERROR OCCURS ON
acct_lname,
acct_fname
from #InsertTmp

Also, I am not sure how you are using the OBJECT_ID value in the dbo.MAC UDF. You need to be aware of the fact that the object identifier is not guaranteed to be same on multiple database that contain the same table or even in the same database if you happen to drop & recreate the table. So the acct_no_index value that you generate on one server will be different from another and so on.

|||

Thanks for your help. Getting much closer . . .

When I get a good working example, I'll post it.