SQL Server Data Masking with DbDefence

By: Jeremy Kadlec   |   Updated: 2023-10-07   |   Comments   |   Related: > Security

Problem

Data breaches are becoming too common place with some accounts reporting more than 1000 breaches per year with close to 50 million records exposed yearly in the financial, business, education, government and healthcare industries.  As Microsoft SQL Server Professionals, we are concerned about properly protecting our production databases, not just with our production data at rest and offline backups, but also preventing access to the data without using any of our approved business applications.  This is difficult with SQL Server databases in our local and remote data centers as well as with SQL Server instances at remote client locations.  To further complicate the situation, we have numerous older versions of SQL Server (SQL Server 2008 R2 is still widely used), to support core business applications.  The level of effort to upgrade immediately to take advantage of some of enterprise features would be expensive on top of the costly licensing for SQL Server Enterprise Edition.  Another reality is auditing SELECT statements and fine-tuning permissions down to a column level is almost impossible across all of our applications. Are there any other data security options?

Solution

The market offers a number of solutions to improve SQL Server's security.  All of them differ in value, functionality and pricing.  DbDefence's solution received validation on for their database encryption algorithm on October 15, 2020 from the National Institute of Standards and Technology (NIST). Review the validation.

With tens of millions of records exposed per year, data breaches are serious and costly to organizations and citizens around the globe.  As SQL Server professionals we need to be vigilant to protect our sensitive information from unauthorized users.  This is easier said than done.  Today’s data is housed in various locations, numerous editions of SQL Server, varying application requirements, internal access to sensitive data and more.  As a best practice, we need to lock down permissions at the server and database level; but this is not enough.  Unfortunately, SQL Server Enterprise Edition licensing is expensive and may require costly upgrades to be performed prior to moving to this licensing model to use Transparent Data Encryption (to protect data at rest).  Alternatively, Column Level Encryption (encrypt singular columns) was introduced in SQL Server 2008, but is not automatic whereas Data Masking (hide and obfuscate data) and Always Encrypted (protect data at rest and in motion) are other options available with Standard and Express Editions starting with the more recent versions (i.e. SQL Server 2016, 2017 and 2019).  With these options there could be a significant investment in terms of upgrades, licensing and code changes.  We still need to protect our data, restrict access and maintain compliance.

One real-time data protection solution that I would like to recommend is DbDefence, which offers a one of kind three-prong approach to protect SQL Server databases like no other single product in the market:

1. Transparent Data Encryption to protect data at rest and when backed up.
2. DbDefence can configure your database to be a 100% Blackbox.  This functionality in DbDefence completely locks down the database from being able to browse the database objects (tables, stored procedures, functions, etc.) and not access the data set outside of approved applications.
3. Data Masking, is a middle ground option between the first two offerings where you still enable Transparent Data Encryption to protect the data at rest online and in backups, but also mask data in sensitive columns to hide the data from administrators, analysts and Power Users, whereas authorized users or applications access the original data.

New Data Masking functionality from DbDefence includes the following security features:

• Dynamic Data Masking (DDM) - The Dynamic Data Masking feature is for particular fields to protect sensitive data outside of approved business applications
• No Code Changes - No code changes or new application development in your apps or stored procedures for the masked columns.  There is no ALTER TABLE or ALTER COLUMN code that needs to be executed in your production environments.
• Low Performance Impact - Minimal SQL Server and application performance impact
• Simple Configuration - Configuration of unmasked data access based on a SQL Server Login(s) or Application(s)
• Universal Protection - Centralized installation and configuration with universal protection above and beyond transparent data encryption to protect data at rest and in backups including SELECT permissions to data
• Low Learning Curve - Fully programmable Data Masking syntax on a per column basis with T-SQL like commands
• Integration - Ability to import and export data without impacting the data masking logic
• End to End Support - Support for SQL Server 2008R2 64 bit to SQL Server 2019 for Express, LocalDB, Web, Standard and Enterprise editions
• Redistribution Licenses - Ability for software development companies to re-distribute the DbDefence functionality into their custom applications
• Compliance - Meet regulatory compliance requirements
• Universal Support - DbDefence supports SQL Server on Windows and Linux. All versions and all editions.

SQL Server Data Masking with DbDefence

In this example implementation, we are demonstrating DbDefence with a widely used CRM application called Goldmine.  In the first two screen shots from Goldmine and SQL Server Management Studio it shows the data prior to configuring DbDefence to encrypt the database and mask particular fields related to Contact data.

Our next steps are to install and launch DbDefence then connect to your SQL Server instance.  Check out these resources for a step by step explanation of the installation and configuration.  Once installed, launch DbDefence and navigate to the Encryption tab.  Select the “Only Encryption, Maximum Transparency” radio button to enable DbDefence’s Transparent Database Encryption. 

The next screen is where all of the Data Masking magic happens.  In the DbDefence interface, Navigate to the Data Masking Tab.  With 3 lines of code, you are able to begin masking data in sensitive columns.  Here are some example masking rules:

• Table Name
• Column Name
• Syntax to Data Mask

The syntax to mask data is simple T-SQL commands such as LEFT, RIGHT, SUBSTRING, etc. functions as well as the corresponding replacement value.

Let’s walk through an example:

• Table Name - contact1
• Column Name - phone1
• Syntax to Data Mask - LEFT(phone1,5)+N'***-*******'
  • LEFT function
  • Phone1 column
  • Display the first 5 characters
  • Mask the remaining characters with *

Additional options include making the value an empty string “” or a constant value such as “111”.

Also keep in mind, in the examples below an “*” was used as a replacement value to display, but you are free to use any letter (i.e. xxxx), number (i.e. 9999) or symbol (i.e. ****) in the result set.

One key step prior to proceeding is clicking on the Validate button to ensure the data masks are correct prior to proceeding with the encryption process.

Once satisfied with Data Masking configurations, press the OK button to proceed back to the main DbDefence interface.  At this point, enter the DbDefence encryption password and press the Encrypt button to implement the Encryption and Data Masking configurations.  The time needed to complete this operation depends on the database size and server resources.  Once completed, a screen will provide the final status (“Database Successfully Encrypted”).

It is now necessary to restart the applications for the DbDefence configurations to display the data as configured.  As you can see in both Goldmine and SQL Server Management Studio, the contact, last name and phone number data has been masked with asterisks for the query results.

Now that we have walked through a simple configuration to encrypt the database at rest and mask particular columns, we need to configure DbDefence to enable access to the data.  One of the most efficient ways to give access to the data is to grant access by login. There are two other options available: grant access by application or the using DbDefence API.

To unlock the masked data for a particular login, reconnect to the DbDefence application and navigate to the Allowed Logins tab.  On this interface, we are able to select the logins that should access the original data directly.

If granting access by the login is not suitable in your case you can configure permissions to the data at the application level, which is configured with the DbDefence Configuration tool.  With this application we are able to browse to select Application then either grant access to the executable to access the unmasked data or provide access to particular users.

The final data access alternative is with the DbDefence API to fine tune access to the data. This is accomplished by using the OPEN SYMMETRIC KEY and CLOSE SYMMETRIC KEY commands shown in SQL Server Management Studio.

How do I get started with DbDefence?

1. Check out the free resources available for DbDefence for small databases.
2. Check out the DbDefence customer testimonials.
3. Schedule your personal demo.
4. Download DbDefence to see how it can help you.
5. Think about all of the challenges you face with protecting your client data then communicate with your team and management about how you think DbDefence will help:
   1. Multi-level approach to securing SQL Server data online and offline with the ability to protect sensitive data from prying eyes with SELECT permissions
   2. No code changes to implement SQL Server database encryption and masking across every version (2008 R2 to 2019) and edition (Express, LocalDB, Web, Standard and Enterprise)
   3. Central installation and configuration with universal protection for your data
   4. Protection of online and offline data with the ability to restrict logins, applications and IIS Pools
   5. Peace of mind that you are protecting your client data and meeting regulatory compliance and data privacy standards (GDPR, HIPAA, PCI-DSS, CCPA, Social Security Numbers, Credit Card Numbers, Personally Identifiable Information (PII), personal data, etc.)
   6. Affordable with prices starting at $698 per server with volume discounts available
6. Put DbDefence through its paces in your environment with your use cases, share the results with your team and determine your next steps.

Next Steps

• Learn more about DbDefence and get your free download.
• Schedule Your Personal Demo
• Additional resources:
  • Check out this introductory video
  • Free Evaluation Copy
  • Home Page
  • Affordable Transparent Data Encryption for all SQL Server Editions

MSSQLTips.com Product Editorial sponsored by Activecrypt, makers of DbDefence.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips