CODEWITHSUNDEEP
sql-server-2008
MohammedT
MohammedT

Reputation: 517

Obfuscating data in SQL Server

How to obfuscate data in SQL Server for development purposes to hide sensitive data without encryption keys because that's crack-able.

Upvotes: 1

Views: 1628

Answers (1)

Peter M
Peter M

Reputation: 7493

OK I am not sure if you require the data to be encrypted for regulatory purposes or just because you don't trust your developers. Given I don't know the laws where your data resides I can't answer the regulatory side of things.

For the trust side the best solution is not to encrypt/decrypt the data (although that may be needed for other reasons), but to partition data sets and only allow defined people to access their required data. You do this by having separate development, staging and production environments:

  1. The developers only work in the development environment which is loaded with enough dummy data for them to do their job. Developers have full access to the data and code here.

  2. QA people test the code in a staging environment which mimics the real system, but again only has enough dummy data loaded for the testing. Developers may or may not have access to this system

  3. The production environment has the tested code and all the real data. Only trusted system admins have access to this system. Developers do not have any access to this system.

The sensitive data is protected by the system admins granting the correct permission to roles that people play in maintaining the overall system.

At some point you need to trust someone with your data, but by partitioning it you can reduce the number of people who have access to it.

Edit

From a comment it seems that you already have this architecture, and that you want to transfer the live data from production server to the development server. In general that is a Bad Idea, and defeats the purpose of having the split environment.

Unless you have some sort of compelling reason to do so, there should be no need to have actual sensitive data in the development environment. If you want to do load testing etc then get some development people to code up data generation routines.

Upvotes: 1

Related Questions