Protect and Mask Sensitive SQL Server Data for Development and Test Environments

By:   |   Updated: 2016-11-04   |   Comments   |   Related: > Security


Our organization has sensitive SQL Server data we need to protect as we migrate the production database to lower life cycle environments.  We need to move our production databases on a regular basis to testing, development and training environments to conduct user testing, performance testing and training of new employees and partners.  Simply deleting data, changing all of the values, etc. is not realistic for teaching nor is it realistic for our development and testing teams when working in testing environments.  We need to automate the protection of our sensitive data in our SQL Server databases as we move the databases to new environments.  How can we do this?


Making sure your SQL Server database in production is secure and sensitive data is protected is absolutely critical.  Implementing SQL Server security best practices in production is generally well understood and supported by both technical and line of business management. Unfortunately, those security safe guards are generally not available for lower life cycle environments.  When data is moved to test, development or training environments, there is a need to balance data protection with flexibility for technical team members to get their job done.  For example, realistic data is needed for training classes to have new team members learn from real world examples without jeopardizing sensitive data.  The same is true for Developers and Testers in order to validate application functionality and performance.  If all of the data (i.e. Social Security Numbers) are changed to a single value it is very difficult to conduct any performance testing because the data distribution and statistics are not representative of the production values.  So validating query plans and having assurance that the coding decisions you are making will perform well in production are challenging.


Options for Moving Production Databases to Lower Life Cycle Environments

The path of least resistance is generally to issue a production database backup, restore to a lower life cycle environment, restrict access to the data, cleanse the data, finalize database permissions then release the database to the team.  This process can generally be automated with file copy operations, scripts for the tasks and validation once the process is complete.  This process addresses the bulk of the needs, but the data cleansing process is critical to securing sensitive data.

Here are some of the common options:

  • Maintain Production Safe Guards - Although possible, this is very costly with additional licensing for auditing products, data storage, management and data access analysis.  In many instances, data theft by internal personnel is a major threat.
  • Generate Test Data - Once again possible, but generally not representative of the production environment in terms of data anomalies, size and scale.  With statistics that are not representative of production data it is hard to conduct SQL Server performance tuning and load testing.
  • Data Deletion - Simply deleting the sensitive data makes training and testing very challenging.  Although the data is not available to be compromised, training and testing is very challenging and all business processes cannot be conducted.
  • Mass Updates - Updating all the values to a specific value such as changing all of the Social Security Numbers to 111-11-1111 or XXX-XX-XXXX, is possible, but with Personally Identifiable Data the usability of the data changes dramatically.

With this being said, balancing the development, testing and training needs vs. protecting sensitive data can become time consuming, covered with red tape yielding compromises that leaves no one happy. But does that really need to be the case?

A better option I would like to introduce is DataVeil which is a fully featured data masking solution for SQL Server and Oracle that addresses the needs outlined above and more. Let's dive into the solution to see how DataVeil can deliver a flexible and secure solution to meet your data security and compliance needs.


Masking Data with DataVeil

The easiest way to get started with DataVeil is via the fully featured user interface.  With this interface you can setup a project to connect to a database, review the tables and columns, configure data masking, generate reports and more.  Let's see how that is done.

Once the project is created and a database connection has been made, tables, columns and the associated data can be reviewed to begin the masking process.  In this first screen shot, the dbo.customer table is moved into the project and is being reviewed for potential columns to mask.

New Project in DataVeil

Figure 1 - Browse tables and columns in DataVeil to begin data masking


After the tables and columns have been reviewed, the next step is to begin masking the data.  The easiest option to begin masking a column is by right clicking on it.  First you need to decide which data mask to select based on the data.  There are 20+ options available (Social Security Numbers, critical dates, company and people's names, account numbers, phone numbers, addresses, etc.) for data masking in DataVeil and the options in the interface below are context sensitive based on the column's data type.

Data masks in DataVeil

Figure 2 - DataVeil Data Masking Selection


The data mask that is selected dictates the next step.  Below are a few examples for configuring the data masks for Social Security Numbers, Dates (i.e. birth date, credit card expiration date, etc.) and a User SQL Value (i.e. custom email address).  With all the DataVeil masks you have full control to preserve a portion of the data, randomize portions of the data, extend the values for custom columns with a User SQL Value including columns from DataVeil in curly brackets (i.e. { and }) and more.

Configure data masking in DataVeil

Figure 3 - DataVeil Data Masking Configuration


With data masks in place, the next step is to preview the data to get a sense of the new values and to validate that the data is accurate across a row.  For example, the FirstName and FamilyName values need to be consistent with the FullName values.  In the Masking View interface, each of the columns can be compared between the original value and a preview of the masked value (i.e. Preview in heading).

Preview masked data in DataVeil

Figure 4 - Preview masked data in DataVeil


With all of the data masks in place, review them one final time on the Masks Summary tab before running the data masks logic.

DataVeil Data Masking Summary

Figure 5 - Masks Summary tab in DataVeil


When the data masking configuration is satisfactory and the has been previewed, it is time to execute the configuration and review the results.

Execute the data masks in DataVeil

Figure 6 - Execute the Data Masks in DataVeil


After the data masking is completed in DataVeil, the data can be reviewed once again to see the final results (i.e. Masked) as compared to the original values.

Final data review in DataVeil

Figure 7 - Final data review in DataVeil


DataVeil Benefits

As SQL Server Professionals, protecting our organizational data is a top priority whether we are in an regulate industry or not.  We cannot have gaps in our database security, but we have to provide secure product-like environments for development, testing, training and outsourcing needs.  DataVeil recognizes these needs and delivers the following value:

  • Data Security - Data breaches are all too common, sacrificing the organization's reputation and jeopardizing customer data.  DataVeil recognizes that you cannot let your guard down in any environment and enables you to secure your data while providing a useful database environment to meet business needs.
  • Legal Compliance - Beyond protecting your organization and customers, DataVeil enables SQL Server Professionals to comply with data security regulations such as PCI DSS, HIPAA, GLBA and the EU Data Protection Directive.
  • Integration - DataVeil provides the ability to seamlessly integrate with your existing processes with a command line interface to properly secure data in lower life cycle environments and enable your entire team to properly test and educate your team via existing SQL Server Agent Jobs and scripts.
  • Automation - DataVeil knows that masking sensitive data is one part of the equation.  As part of the data masking process the tool will automatically take care of triggers, constraints, conditional masking, correct masking execution order and more with a multi-threaded application to ensure your database is just as you expect it when the process completes.
  • Data Discovery - DataVeil has the ability to scan your database for sensitive data then recommend data masks based on the data in the column and the associated data type, so you can rest assured that all of your sensitive data is found and protected.
  • Fully Featured User Interface - Using a project based paradigm, DataVeil allows you to manage the data masking process per database, clone the masks to apply to another similar database, perform data discovery to find out if sensitive data is being missed in your database and migrate the data masking rules from SQL Server to Oracle (and vice versa).  In the user interface you can save the configuration per database as a *.dvp file which is XML based.
  • Scalability - With the same GUI and command interface line interface, you can scale DataVeil from a free version to enterprise databases with the most demanding data protection needs.
  • Extensible - Beyond the out of the box data masks, you have the ability to build your own data masks with any inline T-SQL code to return a value or call a SQL Server User Defined Function to return the masked value.  In addition, DataVeil includes data masks for consistent or random data masking values including your own substitution data.
  • Data Integrity - Data is rarely formatted perfectly across all columns in a database.  For example, telephone numbers can be in a variety of formats (i.e. 123-123-1234, (123)-123-1234, 123.123.1234, 1231231234, etc.) depending on the data type.  DataVeil maintains the integrity of the data by preserving the original format of the data during the masking process, so that the final masked data is representative for that row. 
  • Realistic Data - DataVeil's algorithm's use realistic mapping.  For example, change the name value from "Ted" to "Roman" or "123 Main Street" to "57698 South Cove Ave".  This enables the technology teams to perform realistic functional and performance testing as well as data analysis without sacrificing data security.
  • Checks and balances - DataVeil provides the ability to validate the data prior to the data mask being implemented in order to fine tune the configuration as well as review the data after masking has been completed.  In addition, reports can be generated after each execution of the data masking process for validation purposes.

All of these items yield a significant time savings, secure sensitive data and provide your DBA team with a flexible tool to meet the business needs for training, development and testing.


Data Masking options in DataVeil

DataVeil offers a free version of their data masking product in addition to a commercially licensed version to meet the highest enterprise demands.  Here are the current pricing details:

Data Masking options in DataVeil

Figure 8 - DataVeil Licensing Options


In terms of the commercially licensed product, there is also an optional maintenance fee of 25% for software updates, upgrades and priority support.


Get Started with DataVeil

  1. Go check out all of the resources available for data masking from DataVeil.
  2. Check out the online demo to see DataVeil in action for yourself.
  3. If the online demo is interesting for you download the Free DataVeil Platform Edition to see how the product can help in your environment.
  4. Contact the DataVeil Team for assistance - their full knowledge of the product will save you a lot of time during your evaluation and make sure you cover everything there is to see!
  5. Think about all of the SQL Server data security challenges you face and how DataVeil can save you time.
    1. Data Security
    2. Compliance
    3. Automation
    4. Data Integrity
    5. Data Discovery
  6. After you have checked out the Free Platform Edition of DataVeil, gather all of your findings then communicate with your team and management about how you think DataVeil will save your team time on a regular basis.
  7. Determine the next steps for your team.
  8. Think about all that you can accomplish with your new found free time and being confident your data is secure in development, test and training environments.


Next Steps Product Editorial sponsored by DataVeil Technologies, makers of DataVeil.

Last Updated: 2016-11-04

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources

Comments For This Article


Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

SQL Server Permissions List for Read and Write Access for all Databases

List SQL Server Login and User Permissions with fn_my_permissions

get free sql tips
agree to terms