Import Data from Excel as Encrypted Data in SQL
-
I couldn't find anything on this so I don't even know if I'm using the correct search terms, I've tried a few variations, so please be gentle with me if it's already been answered.
I have a SQL table with encrypted fields for First and Last Name as well as email. When new information is added from a web interface front end, the data automatically gets encrypted (those fields only) in the database. I need to be able to gather past information into an Excel spreadsheet and import plain text names and email addresses into those same encrypted fields in that table. I can write an Insert query in SQL with the given passphrase declared and have it enter names one at a time, but I need to be able to do hundreds or even thousands at a time so I wanted to use the Import Wizard. Here's a sanitized sample of the query I'm using:
DECLARE @passphrase varchar(32) = 'I'm not going to give this! :-)'
INSERT INTO [dbo].[tbl_mytable]
([carrier_id]
,[member_id]
,[first_name]
,[middle_initial]
,[last_name]
,[birth_date]
,[address_1]
,[city]
,[state]
,[zip]
,[phone]
,
,[gender])
VALUES
('3'
,'587469552'
,EncryptByPassPhrase(@passphrase,'FirstName',1,NULL)
,''
,EncryptByPassPhrase(@passphrase,'Testing',1,NULL)
,'2000-01-01'
,'123 Some Ave.'
,'City Name'
,'ST'
,'55555'
,'5551234567'
,EncryptByPassPhrase(@passphrase,'myemail@myisp.com',1,NULL)
,'M')
GOSo, the full question would be something to the effect of:
Is there a way to use the Import Wizard to import many rows of data from Excel in plain text, into SQL where some of the fields need to be encrypted? -
In the import wizard, you cna write a query for the values, and you could probably use a function in there. Structuring this for the Excel can be cumbersome. You might be better building the SSIS package and then adding something that takes the Excel output and applies a transform to call the encryption function.
-
Actually, the source has to support encryption. Excel won't.
My advice, load the hundreds into a staging table that has very limited read writes. Then query this table with an insert..select to encrypt the data and put it in the other table.
Then truncate the staging table
- Steve Jones - SSC Editor - Thursday, February 28, 2019 3:50 PM
That's actually the conclusion I had reached. I don't understand the "very limited read writes" or why I would need to truncate something at the end, but I'm thinking I can just import from a .csv file into a temp table in SQL and once I have it straight SQL to SQL, I know how to add the encryption with the INSERT. Thank you to everyone who looked this over.
-
Limited security. Sorry, was typing too fast. We don't want unsecured sensitive data in the system too long. I'd limit permissions on the staging table and truncate it to prevent anyone from accessing the data that isn't authorized.
Temp tables work, but I always worry about cleanup here, or a potential issue if your session crashes and the data remains. It's probably overly worrisome, but some auditors get upset over having data in a shared temp space, which is why TDE encrypts not only the db, but the tempdb as well.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply