What is data masking in SQL Server
Test data management (TDM) in Microsoft SQL Server consists (or should consist) of data masking or data obfuscation. Anonymizing privacy sensitive data is an important aspect of the data protection inside your SQL Server databases. Privacy laws state that you can’t use personally identifiable information (PII) in your CI/CD pipeline, so you need to do something with this. The first step is to detect where sensitive data is stored; which tables, columns or rows. This gives a good idea of which data needs to be secured. It’s undeniable: doing nothing is not an option anymore.
How to anonymize data in SQL Server
There are several ways to manually anonymize data in SQL Server, depending on the specific requirements and the sensitivity of the data. One approach is to use the UPDATE statement to replace sensitive data with fake or randomized values. For example, you could use the RAND() function to generate random numbers for numeric data, and the REPLACE() function to replace sensitive text with fake values.
Another option is to use the TRUNCATE or DELETE statements to remove sensitive data from the table entirely. This approach is useful if you want to keep the overall structure and format of the data, but remove any identifying information.
It’s also important to consider the access controls and permissions for the anonymized data, to ensure that only authorized users can access it. You can use SQL Server’s built-in security features, such as roles and permissions, to control access to the anonymized data.
Overall, anonymizing data in SQL Server requires a combination of techniques and careful planning to ensure that sensitive information is protected while still maintaining the integrity and usefulness of the data.
A more efficient way to mask data within SQL Server is with the use of a data masking tool like DATPROF Privacy. With this tool, you create masking rules on tables and columns that contain Personally Identifiable Information (PII). Additionally, you can use synthetic data to replace privacy-sensitive records. A masking template is built (and can be reused over and over again) quickly with the predefined rules. Within a few mouse clicks, personal information like IBAN, SSN, and other sensitive data is replaced with randomly generated values or any other anonymized data. The whole process can be automated in your SQL Server CI/CD Pipeline with our TDM portal DATPROF Runtime.
- Supporting MS SQL Server 2008 and higher
- High performance on large data sets
- Automate Data Masking in CI/CD pipelines
- Including Synthetic Data Generation
- Preserve data characteristics
No creditcard required
Data masking tools for SQL Server
In some versions of MS SQL Server, dynamic data masking (DDM) features are available. This is nice to have – nothing more, nothing less. It’s a bandage on the wound, but absolutely no suture. Just like it’s stated in Microsoft’s docs “the purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who shouldn’t have access to the data from viewing it.” So the data is not masked physically in the database; it is masked in the query result. The unmasked data will remain visible in the actual database.
To facilitate compliance with privacy rules and regulations like GDPR, PCI and HIPAA you’d need static data masking (SDM). Static masking is replacing sensitive data by altering data at rest. It is used to provide high-quality (realistic) data for the development of applications. Some versions of MS SQL Server come with this feature. If MS SQL Server is the only data store that you use, it will suffice. If you use other data stores as well that need to be masked, like MySQL, Oracle, PostgreSQL, DB2, etc. then you might want to reconsider.
Static data masking can be done using DATPROF Privacy, the reliable MS SQL Server database masking tool. It works across all major relational (SQL) databases, SQL Server included. With DATPROF Privacy you can easily anonymize data, scramble privacy-sensitive data and/or generate synthetic test data for SQL Server instances and it can be used to mask consistently over a chain of databases/applications. We dare to say it might even be the most user-friendly data masking tool there is for SQL Server users. Thanks to this tool you can create a masked environment easily, containing logical, realistic data. Data characteristics, relationships, and formats are preserved while the original data is obfuscated.
Also read: Static vs. Dynamic Data Masking
SQL Server data masking example
Watch our technical product demonstration/SQL Server data masking tutorial to see how it works. Connect DATPROF Privacy to your SQL Server database to mask and generate data. Some commonly used masking and generation techniques in SQL Server are shown in this demo. Download your 14-day free trial to see the data masking result in your own database!
DATPROF Privacy free trial
Enable test teams with high quality masked production data and synthetically generated data for compliance.
Free Trial - Privacy
"*" indicates required fields