Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk insert with data field encryption


Bulk insert with data field encryption

Author
Message
jhom
jhom
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 27
We get a daily file of employee ids and their associated social security numbers.

Right now, I have a stored procedure


create procedure put_secret(@id int,
@ssn int)
as
begin
open symmetric key ... decryption by certificate ...

insert into secret(id,ssn)
values (@id,EncryptByKey(Key_GUID(...), @ssn))

close symmetric key ...
end



and a program that reads one record from the file, calls the stored procedure put_secret passing the record, and I do this in a while (not EOF). Everything is working well. But it is s-l-o-w.

Is there a way to either create the table so that it knows that a column is, by default, encrypted,


create table secret
(
id int not null,
ssn varbinary(MAX) default encryptByKey(Key_GUID(...)),

primary key(employee_number)
)



or a way to have bcp XML format file to do this


<RECORD>
<FIELD ID="ID" xsi:type="CharFixed" LENGTH="9" />
<FIELD ID="SSN" xsi:type="CharFixed" LENGTH="9" />
</RECORD>
<ROW>
<COLUMN SOURCE="ID" NAME="id" xsi:type="SQLINT"/>
<COLUMN SOURCE="SSN" NAME="ssn" xsi:type="SQLVARBIN" ENCRYPTION="..."/>
</ROW>



or a way to have BULK INSERT, or OPENROWSET(BULK...) do this?

Again, the while loop works and no one is complaining (so I guess it's academic), but I can foresee some shop somewhere in the world with a huge number of records where this might be useful.
Vedran Kesegic
Vedran Kesegic
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1255
Of course it is slow. For each row you open and close a certificate, insert just one row and overhead of calling procedure adds-up to that.
Use table parameter to pass all data at once to procedure, open certificate just once and insert all in one big insert command, no loops are needed at all. You will be amazed.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

sturner
sturner
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 3254
bulk insert into a staging table, then do a single insert from the stage table into destination table like this:


open symmetric key ... decryption by certificate ...

insert into secret(id,ssn)
select id, EncryptByKey(Key_GUID(...), ssn) from stage table

close symmetric key ...

The probability of survival is inversely proportional to the angle of arrival.
jhom
jhom
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 27
These are wonderful recommendations!

It always amazes me how the thinking processes of the various specialties differ: as a "regular" programmer, my thinking is nitty-gritty cursor based I/O (you know, open file, read one record at a time until EOF, close file). The database person probably looks at cursors with a horrified expression.

:-)
sturner
sturner
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 3254
jhom (7/1/2013)
The database person probably looks at cursors with a horrified expression.

:-)


You would be amazed at some the things we see from programmers when left to their own devices.;-)

They usually see a relational database as merely a place to store un-related pieces of data that they will eventually fetch back out (one thing at a time) and beat into submission with VB code. LOL

Sad but true. lol You display early signs of becoming a good database guy. Keep it up.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search