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 Database Encryption for GDPR Compliance with DbDefence


By:   |   Last Updated: 2019-05-28   |   Comments   |   Related Tips: More > Encryption

Problem

The European Union (EU) General Data Protection Regulation (GDPR) has worldwide implications to meet an upcoming deadline on May 25, 2018.  What is important to note is that GDPR is just not for large companies in the EU, it is for any company that does business in the EU even with a small SQL Server database.  Fines are expected to be steep for non-compliance based on the terms set forth in Articles 23 and 30 outlining general practices to protect client data, handle erasure and client access to the data.

Beyond, GDPR it is also prudent to protect your client data to build trust with your client base.  As a technology professional, how would you feel if your data is not protected regardless of the legislation?  Think about how upset you would be if an organization you trusted with your information did not protect it and the impact is has on you to correct the issues with your identity for years to come.  As a SQL Server Professional, how should you approach this issue?

Solution

In this tip we will focus on protecting online client data.  SQL Server natively offers a number of ways to encrypt and protect your data.  One option is Column Level Encryption.  A second is Transparent Data Encryption.  You also have the ability to add passwords to your backups and Windows offers some native encryption options.

Unfortunately, with SQL Server Column Level Encryption there is a need to make programming changes to encrypt and decrypt the data.  If there are only a few columns that need to change and all data access is via stored procedures, then this may be a viable option.  If code is embedded in multiple applications the challenges rise dramatically.  Transparent Data Encryption offers a seamless option, but it is only available in Enterprise Edition, so the upgrade time and associated licensing costs quickly becomes cost prohibitive with numerous installations of Standard and Express Editions of SQL Server in your enterprise.  The backup password is helpful but is not available for SQL Server Express and Web Editions nor does it protect online data.

Another major challenge is finding all of the data that is sensitive across the enterprise.  Being familiar with a few if not many databases, tables and columns where client data is stored is probable.  Querying system views for keywords is certainly possible, but not bullet proof.

What about data stored in notes fields or communication logs?  What about the clever user who could not get their feature enhancement approved and still needs to do their job, so they have client data in another column where client data would not be expected?  How do you discover these situations? Without inspecting every field with some sort of Regular Expression, what do you do?

The reality in upgrading all of your SQL Server’s is costly and time consuming requiring resources across all of the Technology Disciplines (Application Development, Infrastructure, Database Administration and Quality Assurance).  Moving to the cloud is not a simple answer either as much of the same technology disciplines need to engage to address the migration issues.  What if you could just implement encryption on your existing SQL Servers to protect the online databases and backups to meet this looming deadline?

Properly encrypting your SQL Server environment is a reality with DbDefence from Activecrypt which is a 17 year old company focusing on SQL Server security with prominent customers around the globe.  DbDefence was introduced in 2011 and the current DbDefence 7.3 version delivers the following value:

  • 1 Button 128-bit or 256-bit AES Encryption - Simple interface for configuration, encrypting and decrypting databases with a FIPS 140-2 validated solution
  • No application changes – True transparent data encryption for all editions (Standard, Express, Web, LocalDB, Enterprise) and versions (2017, 2016, 2014, 2012, 2008 R2, 2008, 2005) of SQL Server
  • Automation - Comprehensive T-SQL API to manage encrypting a SQL Server database to automate the process
  • Performance – Fast encryption and decryption for SQL Server database objects and data with minimal overhead
  • Customization – Ability to customize the encryption for particular tables, logins and applications
  • Login or Application Restrictions – Ability to restrict access to the database such that only specific logins are able to access the database regardless of their SQL Server server roles or login permissions
  • Application Restrictions – Ability to restrict particular applications (including Profiler) or IIS application pools from accessing the database to protect against internal and external threats
  • Protection – Certificate based encryption to protect the database online and all backups
  • High Availability – Ability to transfer certificates between servers to support Log Shipping, Replication and Availability Groups
  • Multi-Layer Protection – Ability to bind a license to a client so that a database cannot be restored without the client license to add another layer of protection when restoring the database

All you need to do is protect and manage your encryption key to the database to lock and unlock the database.  Depending on the size of your database, within 1 hour you can encrypt it and eliminate one part of the regulatory headache.

Let’s dive into DbDefence to learn more.

Configure SQL Server Database Encryption

To begin the encryption process you can simply install DbDefence and connect to your SQL Server database.  Once authenticated you can simply enter a password to use as your encryption key and press the encrypt button.  Depending on the size of your database this can takes seconds to perhaps an hour.  This level of encryption will be with all of the default parameters.

Please note the management of your encryption key is critical and your responsibility as a SQL Server Professional.  Be sure to store this key in a safe place that is in accordance with your organizational policies.

dbdefence encryption key

To begin the customization process you can decrypt the database and begin to make updates to the configurations.  On the Encryption tab you have the ability to customize the objects and schema that are encrypted, change the AES encryption levels and manage the data SQL Server Profiler is able to see.

dbdefence encryption options

On the Key Storage tab of DbDefence you can configure whether the keys are stored in the file system or in Windows internals protected by the Windows Data Protection API. Additionally, there is an option to store keys in PKCS#11 compatible hardware devices.

dbdefence encryption options

In the screen shot below the Keys are exported and stored with the database as two dbd_key files.

dbdefence exported encryption key

Restrict Application Access with DbDefence

Beyond all of the encryption features with DbDefence the product also has the ability to restrict access to your SQL Server database by login or application.  Below is the Allowed Login tab of DbDefence where you can restrict access to the database which supersedes the SQL Server login and server role permissions.  This type of functionality is also valuable for software vendors who want to deliver their database as a “black box” to protect it from being viewed or modified by local super users.

dbdefence encryption options

Beyond the login restrictions, you can use the DbDefence Configuration tool to establish permissions on a per application and per IIS Application Pool level with access to the decrypted data and objects.

dbdefence configuration

SQL Server Backup Encryption with DbDefence

One little known fact is that SQL Server database backups can be easily opened with a text editor and data can be viewed in clear text. With DbDefence this vulnerability is eliminated because the SQL Server database backups are encrypted to not only protect the online data, but offline as well.  See the screen shot below with a native SQL Server backup opened with Notepad versus a DbDefence protected backup.

unencrypted and encrypted sql backup

One item to be mindful of when considering this product or many other security products is what’s called detouring.  Many products use detouring, but it is one of many techniques that Microsoft does not support because it greatly extends the functionality of SQL Server.  However, DbDefence has numerous multi-national and government clients around the globe that are successfully using the product without any performance or unexpected issues.  Try DbDefence in your environment to see if it meets your requirements.

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. Learn about the DbDefence FIPS 140-2 validated encryption module.
  4. Download DbDefence to see how it can help you.
  5. Checkout the full T-SQL API to see how easily you can automate this solution.
  6. 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. No code changes to implement SQL Server database encryption across every version and edition
    2. Simple interface and API to manage your encryption implementation
    3. Protection of online and offline data with the ability to restrict logins, applications and IIS Pools
    4. Peace of mind that you are protecting your client data and meeting GDPR compliance
    5. Affordable with prices starting at $698 per server with volume discounts available
  7. Put DbDefence through its paces in your environment, share the results with your team and determine your next steps.
Next Steps

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



Last Updated: 2019-05-28


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




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