February 12, 2018 at 2:31 pm
I have a table in SQL Server 2014, with varchar(Max) column which holds xml type data, we get data from external source. Data contains some customer name and addresses, so we need to encrypt the data. In past we have used symmetric key to encrypt data in few columns earlier, but those columns where less than 255 Char so never had issue. ENCRYPTBYKEY or ENCRYPTBYCERT returns varbinary(8000), for input of 3000-3500 length string returned encrypted data is approximately 8000.
Please suggest what could be best way to encrypt the data.
February 13, 2018 at 1:23 pm
Avi1 - Monday, February 12, 2018 2:31 PMI have a table in SQL Server 2014, with varchar(Max) column which holds xml type data, we get data from external source. Data contains some customer name and addresses, so we need to encrypt the data. In past we have used symmetric key to encrypt data in few columns earlier, but those columns where less than 255 Char so never had issue. ENCRYPTBYKEY or ENCRYPTBYCERT returns varbinary(8000), for input of 3000-3500 length string returned encrypted data is approximately 8000.
Please suggest what could be best way to encrypt the data.
Quick question, why are you inflating the XML into a varchar(max), the xml data type is much more efficient?
😎
You can encrypt elements and attributes of the XML without encrypting the whole lot, I've posted some examples of this here in the passed, no need to encrypt the entire thing is there?
February 14, 2018 at 9:59 am
Eirikur Eiriksson - Tuesday, February 13, 2018 1:23 PMAvi1 - Monday, February 12, 2018 2:31 PMI have a table in SQL Server 2014, with varchar(Max) column which holds xml type data, we get data from external source. Data contains some customer name and addresses, so we need to encrypt the data. In past we have used symmetric key to encrypt data in few columns earlier, but those columns where less than 255 Char so never had issue. ENCRYPTBYKEY or ENCRYPTBYCERT returns varbinary(8000), for input of 3000-3500 length string returned encrypted data is approximately 8000.
Please suggest what could be best way to encrypt the data.Quick question, why are you inflating the XML into a varchar(max), the xml data type is much more efficient?
😎
You can encrypt elements and attributes of the XML without encrypting the whole lot, I've posted some examples of this here in the passed, no need to encrypt the entire thing is there?
This is just an old system, pushing xml in the text field. I think just created in the way for ease of development, I will work with development team to see if that can be changed to XML.
I'll try finding your post. In case the link to your post on XML encryption is handy, could you please share.
Thanks a lot for the guidance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply