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? Commented Apr 2, 2018 at 18:27
  • Are you worried that the same plaintext always encrypts to the same ciphertext? Is that the problem?
    – Ben
    Commented Apr 2, 2018 at 18:28
  • @AndrolGenhald. I am using AES with some additional obfuscation. Commented Apr 2, 2018 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. Commented Apr 2, 2018 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. Commented Apr 2, 2018 at 18:30


You must log in to answer this question.

Browse other questions tagged .