Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk insert with data field encryption Expand / Collapse
Author
Message
Posted Sunday, June 30, 2013 10:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 9:25 AM
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.
Post #1468870
Posted Sunday, June 30, 2013 1:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
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
Post #1468888
Posted Monday, July 1, 2013 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
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.
Post #1469051
Posted Monday, July 1, 2013 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 9:25 AM
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.

Post #1469057
Posted Monday, July 1, 2013 7:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
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.
Post #1469065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse