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,
open symmetric key ... decryption by certificate ...
insert into secret(id,ssn)
values (@id,EncryptByKey(Key_GUID(...), @ssn))
close symmetric key ...
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(...)),
or a way to have bcp XML format file to do this
<FIELD ID="ID" xsi:type="CharFixed" LENGTH="9" />
<FIELD ID="SSN" xsi:type="CharFixed" LENGTH="9" />
<COLUMN SOURCE="ID" NAME="id" xsi:type="SQLINT"/>
<COLUMN SOURCE="SSN" NAME="ssn" xsi:type="SQLVARBIN" ENCRYPTION="..."/>
or a way to have BULK INSERT
, or OPENROWSET(BULK...)
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.