Encryption Protection for your Application and SQL Databases
By: Jeremy Kadlec | Updated: 2020-04-22 | Comments | Related: More > Encryption
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.
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:
- 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.
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
|Feature||NetLib Encryptionizer||SQL Server TDE|
|SQL Server Encryption
Every Edition – Enterprise, Standard and Express
Every Version – 2019 to 7.0
|Microsoft Access Encryption||Yes||No|
|File System Encryption
Excel and Word
Text and CSV
|IIS Server File Encryption||Yes||No|
|Medical Device Encryption||Yes||No|
|Protection against certain types of code injection attacks Learn more
How Do I Get Started with Encryptionizer?
- Learn more about Encryptionizer:
- Get started with Encryptionizer to see how to seamlessly encrypt your enterprise data.
- Have a unique situation? Reach out to the NetLib support that pride themselves on timely and comprehensive solutions.
- Enumerate all of the legal, regulatory and SQL Server best practices that
you need to address and how Encryptionizer helps:
- No code changes to completely encrypt your SQL Server databases, files and folders
- No need to upgrade to SQL Server Enterprise Edition or SQL Server 2019
- 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
- Simple installation and configuration with an intuitive wizard-based interface to protect your databases in less than an hour
- Encryption for embedded applications such as medical devices that are in the field and need protection
- Put Encryptionizer through its paces in your environment, share the results with your team and determine your next steps.
- Check out these Encryptionizer videos:
- 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.
Last Updated: 2020-04-22
About the author
View all my tips