How to remove sensitive data from database

(0) Share

Report

Posted on by Michael L. Richards

We have a similar request and are trying to understand the options. In our specific example, using AX 2012 R3 CU9, when developers get a copy of production OLTP restored to their dev box to work on a particular issue, they have access to all information since they are System Admins on their own dev boxes and need to be for development purposes. Are there best practices for such instances or a standard way to 'scrub' certain data (Bank Accts, SSN, Salaries, etc). Using SQL Server TDE doesn't solve this situation. Any suggestions would be greatly appreciated.

Thanks,

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

Hi Michael, I moved your question from the thread about encryption because I think this can't be reasonable solved by encryption.

So far, I've seen only custom-built solutions to clean up the data, i.e. people manually identified which data should be removed and ran scripts to remove or modify it. You can deal with it in Test Data Transfer Tool (skipping some tables and fields, running additional SQL scripts), have a piece of code in AX (e.g. for one customer I've built a script that finds all telephone numbers across AX and replace them with random numbers, so they don't send SMS to clients during testing) and so on.

It's a common problem, therefore it would be nice if there was a standard solution.

In many cases, the idea of using production data without heavy clean up is questionable and potentially violating rules for protection of customers' personal data.

Was this reply helpful? Yes No
Michael L. Richards on at

Like (0)

Report

Hi Martin, thanks for the reply. That pretty much matches what I've been reading. I have reached out to Microsoft as well for their recommendation if they have one for best approach. With the last statement of your reply, are you finding a lot of companies are performing that heavy cleanup or are you saying that doing the heavy cleanup causes its own issues and it's not generally done?

Thanks again for the responses.

Michael

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

I mean that many teams underestimate this issue and work on production data with very limited changes - with real customer addresses etc. It's convenient to work on real data, but risky.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Michael,

At a customer we once created a script to scramble data. The organization and person names (global address book records) e.g. were replaced with the RecId values of the records. In that way the customer, vendor and employee names were secured. Also more data was scrambled. You could create a similar script. I have searched my historical files, but I cannot find this script.

Disadvantage: RecId values were not very friendly. Possibly Use a dictionary with words to replace the names.

Was this reply helpful? Yes No