I have an address table with human-readable columns like street, city, state, zip - and also columns with spatial data (the SQL Server geography type). Technically, this violates 3rd Normal form (i.e. since you can derive street, city, state from Geo) but we know the convenience reasons for doing this. I have custom encryption for the human-readable forms but I want to do math operations in the database against the geography columns - so this is not currently encrypted.

The problem: I effectively have plain-text and the corresponding cipher-text side-by-side. (i.e. I can go find out the street that a plain-text Geo coordinate corresponds to and now I know plain-text street and cipher-text street, somewhat compromising my encryption scheme).

I don't mind having the Geo exposed, but how can I hide/encrypt the link between Geo and human readable? I see that SQL Server does NOT support doing Transparent Data Encryption for columns of the geography type (which seems like it's exactly where you'd want it).

  • What is your "custom encryption" and why are you not using something standard like AES? – AndrolGenhald Apr 2 '18 at 18:27
  • Are you worried that the same plaintext always encrypts to the same ciphertext? Is that the problem? – Ben Apr 2 '18 at 18:28
  • @AndrolGenhald. I am using AES with some additional obfuscation. – Michael Arner Apr 2 '18 at 18:28
  • @Ben I got the impression that the worry was known plaintext. Michael, AES is safe against known plaintext attacks, but your obfuscation is pointless. – AndrolGenhald Apr 2 '18 at 18:29
  • @Ben. No I am worried that a Geo quantity that can be turned into plaintext is next to the actual plaintext. i.e. From Geographical Coordinates, I can get street address. So effectively, I have the encrypted street address and the plaintext street address side by side. – Michael Arner Apr 2 '18 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.