FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL

[Umfriend]

FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL Server

Been wrecking my brains and googling like hell. Sure, reversible and deterministic will mean it'll be crackable. I don't care as long as it is harder then a simple REVERSE and wider than Rot13. I can not believe that others are not looking for such a function.

Blast!

Sry, had to vent somewhere.

[UtwigMU]

What are you trying to achieve?

Not expert on MS SQL, know a bit of MySQL.

[Umfriend]

I need to create a file, basically a table, where certain columns may not be easily readable. For instance, a contract number. It needs to be encoded. However, that contract number may appear more than once in this file and the reader must be aware that certain records relate to the same contract. So, each encryption of contract number needs to be the same. It also needs to be reversible because in certain cases, the client must be provided a key/algorithm/instructions to decode.

Input is varchar so alphabet-encryption is out. Rot13 won't work as it ecrypts to few values. Hashbytes are not reversible. It does not need to be state of the art, as long as regular bankers and lawyers don't easily hack it. I have seen the use of Reverse for similar issues but thta is a running gag by now I think, I won;t do that.

[UtwigMU]

I need to create a file, basically a table, where certain columns may not be easily readable. For instance, a contract number. It needs to be encoded. However, that contract number may appear more than once in this file and the reader must be aware that certain records relate to the same contract. So, each encryption of contract number needs to be the same. It also needs to be reversible because in certain cases, the client must be provided a key/algorithm/instructions to decode.

Input is varchar so alphabet-encryption is out. Rot13 won't work as it ecrypts to few values. Hashbytes are not reversible. It does not need to be state of the art, as long as regular bankers and lawyers don't easily hack it. I have seen the use of Reverse for similar issues but thta is a running gag by now I think, I won;t do that.

i think your problem comes from requirement of encryption to be reversible. Could you store hashes and then another table with id hash data (varchar)? Or in some encrypted file?

I used hashes because I have around 2000 folders with files that I need to make available. So wrote a script that entered hashes of all files in a table. Then did select distinct to create table of unique files and then created table of folders and file ids.

[Umfriend]

Well, reversible encryption with SQL Server is actually rather simple, it has AES_256 out of the box. I could secure the keys with a lawyer and have them released when required. The issue with that is that AES_256 is not deterministic. So if I would run a SELECT and encrypt ContractNumber on the fly, any subsequent instance of that ContractNumber would be different.

I could sorta do what you suggested, with AES_256 encrypted values, but it'd be a pain. Not only would I need an additional table solely for this purpose, I would also need to pre-load it. So the first time I would need to have a distinct list of values to be encrypted, encrypt them once, store encrypted value, join in query. Next query would then first have a distinct list of values not yet in table with encrypted values etc. It is certainly doable but just a lot of hassle. Given that I would effectively be taking out the random seed for encryption, it becomes more vulnerable in any case. It would help if there was a way to fix the Initialization Vector. And there is the hassle of permissions that I'd need to deal with.

If I really need to do this, I would probably use HASHBYTES and secure the encryption table with a lawyer. Still a hassle. And I would have to deal with the, remote, probability of collision... A real PITA this is.

It really does not have to be that strong, just a tad harder than a simple REVERSE or Rot13.

[UtwigMU]

Well, reversible encryption with SQL Server is actually rather simple, it has AES_256 out of the box. I could secure the keys with a lawyer and have them released when required. The issue with that is that AES_256 is not deterministic. So if I would run a SELECT and encrypt ContractNumber on the fly, any subsequent instance of that ContractNumber would be different.

I could sorta do what you suggested, with AES_256 encrypted values, but it'd be a pain. Not only would I need an additional table solely for this purpose, I would also need to pre-load it. So the first time I would need to have a distinct list of values to be encrypted, encrypt them once, store encrypted value, join in query. Next query would then first have a distinct list of values not yet in table with encrypted values etc. It is certainly doable but just a lot of hassle. Given that I would effectively be taking out the random seed for encryption, it becomes more vulnerable in any case. It would help if there was a way to fix the Initialization Vector. And there is the hassle of permissions that I'd need to deal with.

If I really need to do this, I would probably use HASHBYTES and secure the encryption table with a lawyer. Still a hassle. And I would have to deal with the, remote, probability of collision... A real PITA this is.

It really does not have to be that strong, just a tad harder than a simple REVERSE or Rot13.

Well then, I think MURC SQL knowledge has been exhausted. Stack overflow or reddit is the next step though they will probably tell you to securely encrypt everything and don't give decryption data to client.

What's wrong with just plain: select distinct contract number and creating table with id index. Then just using index from table of contract numbers your customers are not supposed to see? I'm just generating ideas.

[Umfriend]

Sorta what I was suggesting with HASHBYTES but simpler because, of course, having such a translation table removes all further ubfuscation requirements.

Still don;t like it but if I have to....

[dZeus]

FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL Server

Been wrecking my brains and googling like hell. Sure, reversible and deterministic will mean it'll be crackable. I don't care as long as it is harder then a simple REVERSE and wider than Rot13. I can not believe that others are not looking for such a function.

Blast!

Sry, had to vent somewhere.

Check for 'tokenization' solutions for obfuscating the PAN (personal account number). I would be very surprised if that doesn't yield results, as this is a common issue in obtaining PCI-DSS certification.

[Umfriend]

Thanks, yes, that is basically what I would be doing. At some stage I'll need to do this with actual name/address data as well. Going to be a long list of tokens as they'll need to be consistent inter temporarily as well.