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.

datprof privacy logo
  • 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
Trial DATPROF Privacy

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!

Start your
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

1Step 1
2Step 2

Native support for SQL Server

sql server logo

TDM Platform

The right test data in the right place at the right time. Masked, generated, subsetted, virtualized and automated at the push of a button.