Encryption Protection for your Application and SQL Databases

By: Jeremy Kadlec   |   Updated: 2022-06-01   |   Comments   |   Related: > Encryption

Problem

Your organization has a number of "legacy" SQL Server instances and data centric applications that have grown over the years to support your core business needs and come up to compliance.  The time, energy, costs (hardware, software licensing, etc.) to upgrade is staggering.  You see data breaches all too often, from both external and internal threats, and know you need to take action.  You are just not sure how to comprehensively protect your data in an affordable manner.  You realize that protecting your data and being compliant is more than just protecting your SQL Server databases – you have sensitive data across your company in numerous applications that you need to protect.

Solution

Let’s break down a number of common scenarios faced by SQL Server Professionals and see how we can resolve compliance issues in an affordable manner.

Data Encryption for all SQL Server Versions and Editions

Particular versions and editions of SQL Server ship with Transparent Data Encryption as well as column level encryption, which offer options to encrypt data.  But these implementations generally have some shortcomings.  One of the first considerations is upgrading SQL Server, which is more than just clicking through a wizard and being done in an hour.  It is a major project.  The reality is:

• Time Commitment - This is a project that includes each portion of the organization. The time commitment across Development, Testing, DBA and User teams is significant to update the application.
• New Hardware \ Cloud Recurring Costs – Generally, either a new server is needed to support the upgrade or recurring costs from a public or private cloud provider become the new reality.  Often times to meet the performance expectations with shared cloud resources, the costs climb rapidly, which generally results in sticker shock.
• Licensing - With a new server comes licensing for both Windows and SQL Server which has become significantly more expensive with the latest OS and database versions.
• Access to Source Code - In circumstances where an upgrade consists of a multiple version update, source code generally needs to be updated.  In some instances, the source code is not available for all applications.  Further, IT teams are very surprised to see how many applications actually access the production database that are business critical that have popped up over the years.

Protect Online SQL Server Database

Although there are a number of legal requirements (GDPR, CCPA, HIPAA, GLBA, etc.) to protect data, the number of data breaches continues to be astounding.  Application and SQL Server security are necessary controls to prevent data misuse, but require consistent implementation and recurring management.

Unfortunately, permissions are just one piece of the puzzle.  When databases with sensitive data are not encrypted, there is risk of data being compromised.  In many environments, all the sensitive data is not known within the database.  At times, sensitive data is stored in free form text fields by users because the application does not support evolving needs.  With unencrypted SQL Server databases, the data is vulnerable. 

SQL Server FILESTREAM

The SQL Server FILESTREAM feature enables the integration of relational data with documents and images stored in the file system.  The data stored as varbinary(max) data types are accessible to SQL Server based applications via T-SQL commands and granted permissions with SQL Server security.  This integration can simplify management of unstructured data required by many business needs.  However, even with Microsoft’s SQL Server Transparent Data Encryption option, data outside the database is not encrypted.  This could include documents and images with sensitive data that need to be encrypted to be compliant.

Protect Offline SQL Server Data

From a disaster recovery perspective, getting SQL Server database backups off site is prudent if a co-location facility is not within your budget.  Even if there is a disaster recovery site, it is prudent to have SQL Server database backups off site in a third location as ternary protection.  There are also cloud based services that can be used to backup data to the cloud.  With each of these options, it is imperative to have encrypted data that is off site when outside of your direct control.

Unfortunately, I have seen and heard about couriers taking unencrypted tapes \ drives offsite.  This makes me cringe.  I have also heard about a Backup Technician for an organization with multiple offices within a relatively large city who didn’t think twice about having backup tapes rolling around in the back of his vehicle.  Little did he know the value of the data on each one of those tapes, nor was he able to account for all of the tapes in case someone broke into his vehicle knowingly or unknowingly.

Once your SQL Server database is outside of your application and SQL Server security, there is little to protect your database other than encryption.  SQL Server backup passwords are a "speed bump" rather than an impenetrable solution.

SQL Server Import and Export Data Protection

Beyond SQL Server data online and offline, there is often a significant amount of data exchanged between systems for reporting \ data warehousing \business intelligence and partners to support one another.  These text, CSV or Excel files are frequently unencrypted and store sensitive data.  These files are generally restricted with permissions at the folder level, but not always. 

There are similar data privacy concerns with one-time data extractions whether the data resides locally to the SQL Server or on a team members machine.  These files are generally clear text and store sensitive data that could be detrimental to the organization if the data is released to the public.

SQL Server Express Is Everywhere

SQL Server Express is everywhere – and for good reason.  It is a free and robust database engine from Microsoft to meet many small database management needs such as:

• ISVs
• Medical Devices
• Large Retail Point of Sale Terminals
• Retail Applications for Shipping
• IOT Data
• Tracking Data
• Third Party Applications
• Web Sites

Although SQL Server Express does include the same permissions-based management for objects and data as Standard and Enterprise editions, it does lack SQL Server Transparent Data Encryption to encrypt the data.

Code Protection

Protecting code as intellectual property makes business sense.  Organizations have teams of Developers spend a significant amount of time and energy building custom applications to meet their unique offerings.  With application logic stored on web servers (ASP.NET, PHP), client machines (.NET + DLLs), servers, file shares and remote desktop sessions, it is important to protect the code for numerous reasons:

• First, is to hide unique, patented or patentable logic.
• Second, is to prevent reverse engineering of logic.
• Third, is to not expose any database names, user names, passwords, object names or connection strings which can be used to attack a system. 

Although data protection generally gets the lion share of the attention when it comes to encryption and data security, if the application logic is not properly encrypted then there may be a window to exploit an application.

Microsoft Access Database Encryption

Although Microsoft Access is generally outside the purview of SQL Server Professionals, many of us know that business processes are being run from these applications.  Frequently, the Microsoft Access databases were built for a one-time need, to meet a departmental project or to just complete "some reporting".  However, over time these Access databases began supporting sensitive data and many others were built prior to any data protection legislation.  Unfortunately, Microsoft Access has limited application security and sensitive data can be compromised.

The data in Microsoft Access also needs to be secured and protected, but the options to do so are generally time consuming and expensive.  Upgrading to SQL Server is once again costly (time, Windows and SQL Server licensing, etc.) and requires Developers to interpret and build an application to support the user needs.

File System Encryption

Similar to the business-critical Microsoft Access databases, there is often sensitive data in Word, Excel, PDF, text, CSV, etc. files residing on file systems.  Although they are outside of the database, these files can still contain sensitive data that must be secured and not overlooked.

The reality is that protection is needed for more than just textual data on a file system.  Images need protection as well and cannot be left vulnerable.  Whether the images are medically related, for identity verification, litigation or biometric (retina scans or facial recognition), these images need to be encrypted as a portion of your overall application security.

IIS Data Encryption and Protection

In a similar example, an organization has PDF’s with sensitive data served by a web application that resides on the web server.  Although the data is external to the SQL Server database, the IT Team still needs to secure this data beyond folder and file level permissions since the data is shared with partners and clients.  Unfortunately, encrypting these sensitive PDF files with the native Microsoft toolset is challenging.

Medical Device Encryption

Mobile devices are prevalent in numerous industries and present various challenges to protect the data local to the device.  Often times sensitive data is downloaded to these devices.  One example of sensitive data on mobile devices is in the Health Care industry where medical staff work with patients and their sensitive data.  On some of these devices SQL Server Express is embedded on the device since it is a robust and free database engine.  Unfortunately, SQL Server Express does not include Transparent Data Encryption nor any means to encrypt data outside of the database engine.

So, What’s the Solution?

With all of these technical and business challenges, how do you resolve these critical items in an affordable manner?

SQL Server Enterprise Edition or SQL Server 2019 Standard Edition Transparent Data Encryption (TDE) can solve some of the database issues, but does not resolve any of the other challenges related to earlier SQL Server versions, Express Edition, Microsoft Access, files on the file system and medical devices.

Encryptionizer from NetLib Security offers a single solution to support SQL Server databases from version 7.0 to 2019, and editions Express to Enterprise without upgrading or changing a line of code.  Something that native SQL Server TDE cannot support.  Further, Encryptionizer encrypts Microsoft Access databases, Word, Excel, PDF, Text, CSV files and more.  If it is on the Windows platform, Encryptionizer can encrypt and protect it.

How does Encryptionizer® from NetLib Security Solve These Problems?

• Comprehensive - Single application to encrypt data in SQL Server databases (regardless of the Edition or Version), Microsoft Access and Word, Excel, PDF, Text and CSV files in a file system
• Centralized Key Management
• Device Independent
• No Code Changes
• No Costly SQL Server Upgrades
• Simple Deployment, Configuration and Management

Comparing SQL Server TDE and NetLib Encryptionizer

How Do I Get Started with Encryptionizer?

1. Learn more about Encryptionizer:
   1. SQL Server
   2. Developer Versions
   3. Medical Device Encryption
   4. Compliance
2. Get started with Encryptionizer to see how to seamlessly encrypt your enterprise data.
3. Have a unique situation? Reach out to the NetLib support that pride themselves on timely and comprehensive solutions.
4. Enumerate all of the legal, regulatory and SQL Server best practices that you need to address and how Encryptionizer helps:
   1. No code changes to completely encrypt your SQL Server databases, files and folders
   2. No need to upgrade to SQL Server Enterprise Edition or SQL Server 2019
   3. Support for all editions and versions of SQL Server as well as the remainder of your Exchange, MySQL, Microsoft Access, IIS, Tomcat, Custom Applications and more
   4. Simple installation and configuration with an intuitive wizard-based interface to protect your databases in less than an hour
   5. Encryption for embedded applications such as medical devices that are in the field and need protection
5. Put Encryptionizer through its paces in your environment, share the results with your team and determine your next steps.

Next Steps

• Check out these Encryptionizer videos:
  • Encryptionizer for SQL Server Sample Deployment
  • Sample Deployment of Encryptionizer Key Manager (EKM)
• Get your free evaluation version
• Learn more about all of the NetLib solutions

MSSQLTips.com Product Editorial sponsored by NetLib Security, Inc., makers of Encryptionizer.

NetLib® and Encryptionizer® are Registered Trademarks of NetLib Security Inc.

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.

View all my tips