Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Data Masking with DbDefence


By:   |   Updated: 2019-06-05   |   Comments   |   Related: More > 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 SQL Server Professionals, we are concerned about properly protecting our databases, not just with our 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 options?

Solution

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 data.  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 SQL Server versions (i.e. 2016 and 2017).  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.

The market offers a number of solutions to improve SQL Server's security.  All of them differ in value, functionality and pricing.  One 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 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.
DbDefence three pronged security consisting of Transparent Data Encryption, Data Masking and 100% Blackbox

New Data Masking functionality from DbDefence includes:

  • Data Masking - Data Masking for particular fields to protect sensitive data outside of approved business applications
  • No Code Changes - No code changes in your applications or stored procedures
  • 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 2017 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

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 screens 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.

Original data in Goldmine and Management Studio prior to configuring DbDefence

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. 

DbDefence Encryption Settings

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:

  • 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, number or symbol.

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.

Configure Data Masking at a Column Level with DbDefence

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”).

Database Successfully Encrypted with DbDefence

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.

masked_row
Masked data in Goldmine and Management Studio after completing DbDefence process

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.

Allowed Login tab in DbDefence

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.

DbDefence Configuration Tool for application access

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.

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 2017) 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
    6. Affordable with prices starting at $698 per server with volume discounts available
  6. Put DbDefence through its paces in your environment, share the results with your team and determine your next steps.
Next Steps

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



Last Updated: 2019-06-05


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools