SQL Server Data Masking: Static vs Dynamic

What is SQL Server Data Masking?

Data masking is a data security technique that allows you to obfuscate sensitive data, so it isn’t useful if compromised by an attacker. The masked copy is used to replace real data for testing, demos, or other use cases that don’t require the data itself.

Microsoft SQL Server’s built-in security features enable two types of data masking:

• Static data masking—creates high quality data for application development and testing, without revealing sensitive information. Realism is important for development and testing teams to more effectively identify defects early in the development cycle. SQL Server can create a sanitized copy of a database with all sensitive information altered.
• Dynamic data masking (DDM)—used to apply role-based (object level) security to databases/applications, masking data as it is retrieved by a user. This is mainly applicable to read only scenarios. SQL Server can configure DDM on specific database fields, hiding sensitive data in query results. This is easy to use with existing applications, as it does not require changes to queries.

Read our complete guide to dynamic data masking (across all data stores).

What is SQL Server Static Data Masking?

In SQL Server, the Static Data Masking feature lets you create clean copies of your databases, modified to obfuscate all sensitive information, allowing them to be shared with non-production users.

Static Data Masking can be used for development, testing, analysis and reports, technical support, and sharing databases with third parties or contractors. For organizations subject to GDPR, it can be used to eliminate personal information from databases, while preserving the database structure.

Static data masking lets you configure masking behavior for each column in the database. For each selected column, it replaces the original data with new, masked data, created according to your configuration.

Note: Static Data Masking is irreversible—you cannot retrieve the original data after it has been masked.

What is SQL Server Dynamic Data Masking?

Dynamic data masking (DDM) enables you to control which users view masked data and which roles can view sensitive data. You can use DDM to prevent unauthorized access to certain pieces of data by limiting the amount of sensitive data revealed.

The main goal of DDM is to limit the amount of exposure to sensitive data. Use it as an additional tool to support your overall security efforts. DDM is available in Azure SQL Database and SQL Server 2016 (13.x) and later.

DDM lets you hide the sensitive data pooled together when users run queries. You can hide data by configuring DDM on specific database fields. You can implement masking rules within query results. This process does not modify the masked data and can be applied on existing apps.

SQL Server Dynamic Data Masking Types

Here are four data masking types you can use on SQL Server:

• Default—this function lets you create a rule that masks the entire value when users with read-only privileges query the data.
• Email—this function lets you create a rule that masks the first letter and the domain.
• Random—this function lets you create a rule that replaces all numerical values with a random value according to a specified range.
• Partial—this function lets you partially mask data, specifically for string type columns.

Static Data Masking vs. Dynamic Data Masking: Pros and Cons

Advantages of Static Data Masking:

• Permanent deletion—sensitive data is permanently deleted when the data transformation is applied to the database.
• No effect on performance—all data transformations are applied in advance, so there is no performance impact per transaction.

Disadvantages of Static Data Masking:

• Batch processing—masking is applied to the data store via batch processing, not in real time, which can take significant time.

Advantages of Dynamic Data Masking:

• Prevents unauthorized access—makes it easy to prevent unauthorized disclosure of sensitive data.
• Easy to configure—can be easily set up with Transact-SQL commands.

Disadvantages of Dynamic Data Masking:

• Not a complete solution—you must use additional measures to achieve database security.
• Does not encrypt the data—only masks it when responding to a query.

SQL Server Data Masking with Satori

Satori Universal Masking is using data classification in conjunction with dynamic masking. Users can define which transformations they wish to apply on any data type instead of being applied to specific columns. It works across SQL server and other data stores.