Azure SQL Database
An Azure relational database service.
5,688 questions
What for kind of "magic" do you expect?
Head out for commercial software for anonymization data.
Anonymize sensitive fields in sql server table
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 36%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER4%3C/text%3E%3C/svg%3E)
rajanisqldev-42
216
Reputation points
Hello,
I need to copy Production sql database tables to Dev database tables. The tables have sensitive data. I need to anonymize during the copy(Fabric pipelines/ADF/SSIS).
How can I do this? Dynamic masking doesn't help as it just hides data instead of showing full value.
I need something like sample names, Data of births, addresses, bla bla
Thanks in advance
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 60%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Mahesh Kurva 245 Reputation points Microsoft Vendor2024-09-13T01:37:18.1433333+00:00 Hi @rajanisqldev-42,
Welcome to the Microsoft Q&A and thank you for posting your questions here.
- Identify the columns containing sensitive data (e.g., names, dates of birth, addresses).
- Develop a logic to replace sensitive data with anonymized values using SQL scripts or custom code.
- Use Azure Data Factory (ADF) or SQL Server Integration Services (SSIS) to copy data from Production SQL database tables to Dev database tables.
- In ADF, create linked services for both your Production and Dev databases, define datasets for the tables you want to copy, and use a Data Flow activity to read data from the Production database, apply transformations to anonymize the sensitive data, and write the transformed data to the Dev database.
- In SSIS, use an OLE DB Source to read data from the Production database, a Data Flow task to apply transformations for anonymization, and an OLE DB Destination to write the anonymized data to the Dev database.
- Schedule the ADF pipeline or SSIS package to run at regular intervals to keep the Dev database updated with anonymized data from Production.
I hope this information helps. Please do let us know if you have any further queries.
-
Mahesh Kurva 245 Reputation points Microsoft Vendor
2024-09-16T10:34:49.89+00:00 Hi @rajanisqldev-42,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Mahesh Kurva 245 Reputation points Microsoft Vendor
2024-09-17T12:05:10.9833333+00:00 Hi @rajanisqldev-42,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
2 answers
Sort by: Most helpful
-
-
rajanisqldev-42 216 Reputation points
2024-09-11T07:36:21.0233333+00:00 Hello,
I mean is to generate sample text value in case of varchar fields and random number instead of numeric fields.
I need just random data without masking for all sensitive fields.
Regards