What is the best way to anonymize ID values in sql server 2008

Question

I got 2 tables in sql 2008

Table1
Id    Name Surname City   
1000  Alex White   London
1001  John Brown   Brussels
..

Table2
Id  Surgeon  Room  aId
1   Mike J.  A104  1000
2   Jack S.  C144  1001
...

And I have a query like:

Select a.Id,b.Id,
       a.Name,a.Surname,a.City,b.Surgeon,b.Room
into #results
from Table1 a
inner join Table2 b on a.Id = b.aId

What I want to do is to anonymize the a.Id and b.Id values for privacy, by using dummy ones instead of the real ones. I added a random mathematical operations before, like:

Select aId = a.Id * 22 / 5 + 14 * 2
      ,bId = b.Id * 12 / 4 + 7 * 3
       ...

but honestly I am not really happy what I am doing here and I am looking for a more professinal way to provide this. Any advice would be appreciated.

What problem are you trying to solve? Calculating an arbitrary number doesn't provide any more anonymity than 1001. — Filburt, Mar 14 '17 at 17:16
I just need dummy but logical ID values instead of the real ones for a.Id and b.Id — Eray Balkanli, Mar 14 '17 at 17:18
Maybe [Dynamic Data Masking](https://msdn.microsoft.com/en-us/library/mt130841.aspx) will fit your needs? It's only available with SQL 2016, but it's MS's out-of-the-box masking functionality. You could try proprietary solutions as well. Most offer a free trial. — John Eisbrener, Mar 14 '17 at 17:56
Maybe this can be of help: http://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select — Ralph, Mar 14 '17 at 18:37

score 0 · Answer 1 · answered Mar 14 '17 at 22:37

If you don't need to be sure the anonymized IDs are unique and you don't need to find a real ID based on an anonymized ID, you could use the CheckSum() or HashBytes() function with the strings from your Table1 and Table2:

Select aId = CheckSum(a.Name + a.Surname) % 10000
      ,bId = HashBytes('SHA1', b.Surgeon) % 10000
      ,a.Name,a.Surname,a.City,b.Surgeon,b.Room
into #results
from Table1 a
inner join Table2 b on a.Id = b.aId

If you need to be sure you have a unique value for each of the Id values in your table and you also need to find a real ID based on an anonymized ID, you can construct a lookup table as follows:

CREATE TABLE Anon
    (
    ID        INTEGER NOT NULL PRIMARY KEY,
    AnonID    UNIQUEIDENTIFIER DEFAULT NewID()
    );

this can then be used in queries where the actual ID should not be returned:

Select aID = Anona.AnonID,
       bID = Anonb.AnonID,
       a.Name,a.Surname,a.City,b.Surgeon,b.Room
   into #results
   from Table1 a inner join Table2 b on a.Id = b.aId
   inner join Anon Anona on a.Id = Anona.Id
   inner join Anon Anonb on b.Id = Anonb.Id

The Anon table would need to be maintained to ensure it contains all IDs from your Table1 and Table2.

What is the best way to anonymize ID values in sql server 2008

1 Answers1