Balancing new initiatives with operational best practices at our organization is always a challenge. To stay competitive in the market, we need to be constantly innovating new solutions to improve our customer experience. While meeting new initiatives can drive significant revenue, making the evening news for a data breach will have a long term negative impact with both tangible and intangible repercussions. We, as SQL Server Professionals, cannot be blind-sided by regulatory requirements and generally accepted SQL Server best practices such as encrypting our data online and offline. Our challenge is to offer the organization a seamless solution across numerous applications as well as editions and versions of SQL Server. How can we properly encrypt our SQL Server data to protect the organization?
Whether you are facing GDPR, HIPAA, PCI, etc. regulations, encrypting your SQL Server databases is a critical component to be compliant. SQL Server offers a few different options to encrypt databases including column level encryption and transparent data encryption with Enterprise Edition. Unfortunately, both of these options can come at a very high expense. SQL Server column level encryption is certainly a feasible option. However generally it comes at a very high cost to support the development effort to change all of the applications to use the native encryption and decryption stored procedures. Similarly, upgrading, migrating or consolidating all of your SQL Server instances to Enterprise Edition is time consuming, could require application changes and the licensing cost is a significant expenditure for production environments.
For example, upgrading a single 8-core server from Standard to Enterprise Edition could cost more than $80,000 USD. This is a common scenario for organizations in need of an encryption solution for their SQL Server databases. More often than not, this is too expensive of an undertaking when other more cost effective alternatives are available. One solution that I would like to introduce is Encryptionizer from NetLib, which delivers seamless enterprise level SQL Server encryption to all versions and editions.
How does Encryptionizer deliver value to organizations with mission critical SQL Servers?
- Seamless – Latest Encryption Standards and FIPS 140-2 compliant with no application code changes
- Ease of Use – Simple wizard-based interface to deploy and manage encryption across the enterprise
- High Performance – Based on informal testing, Encryptionizer is 10 to 15% faster than native SQL Server TDE on a properly sized and tuned server
- Multi-Key Management – Ability to setup encryption keys for specific servers, backups, log shipping, replication, etc. to provide multi-layered protection as well as centralized key management
- Automation – Beyond the Wizard based interface there is a feature rich command line interface (CLI) and script-based installer to automate deployments or embed the encryption functionality in applications
- End to End SQL Server Support – Not only supporting all editions and versions of SQL Server for legacy applications and new deployments, but also system databases (Master, Model, MSDB and TempDB), Filestream data, replication, log shipping, clustering, AlwaysOn and folders on the Windows Server
- Single Application for Enterprise Encryption – Encryptionizer supports more than just SQL Server – any Windows based application such as Exchange, MySQL, Access, IIS, Tomcat, etc. will benefit from Encryptionizer as well as hardware with embedded applications such as medical devices can all be secured and centrally managed.
- Supports all versions and editions of SQL Server down to SQL Express and LocalDB.
How does Encryptionizer protect SQL Server?
When you deploy Encryptionizer a Key Management Service and Kernel Mode Drivers are installed on the SQL Server. Next, launch the Encryptionizer Wizard to encrypt your database and configure the Key Management Service, which should be performed during a maintenance window. During this process, SQL Server will be shutdown to complete the database encryption which generally encrypts at a rate of 5 to 10 GB per minute. Once completed, SQL Server will be restarted and the Key Management Service will handle all the interactions between SQL Server and the database storage via the Kernel Mode Drivers. The process is completely seamless and no code changes are necessary.
For a more detailed explanation and comparison of SQL Server with and without Encryptionizer check out the following resource – Automatic Whole Database Encryption – How It Works.
How do I configure Encryptionizer for SQL Server?
Below is the main menu for the Encryptionizer Wizard based interface. On this interface you can access documentation, Manage Keys and the API, execute additional wizards such as the Administration Wizard for setting up the Key Management Service, Encrypting and Decrypting databases as well as Configure Folder Level Encryption.
Let’s get started with the Encryptionizer Encrypt/Decrypt Wizard. Once launched a splash screen loads followed by options to Encrypt or Decrypt Files as shown below. For this demonstration we are going to begin encrypting files.
The next step is to Choose Files to Encrypt by browsing to the SQL Server databases as well as choosing the destination directory for the final encrypted files (i.e. *.mdf, *.ndf and *.ldf). You have the option to overwrite the existing database files if you have limited storage or write the files to a new directory. Once complete it is time to select the encryption algorithm, key length and passphrase as shown below.
At this point the encryption process can begin and will update the Wizard as the process runs. Once completed Encryptionizer will report success.
If you restart SQL Server the newly-encrypted database is inaccessible yet protected. To configure the accessibility of the database via the SQL Server relational engine, it is necessary to configure the NetLib Key Management Service which is launched from the main menu with the Admin Wizard. The next step is to Choose the SQL Server instance to secure as shown below.
Once the SQL Server instance is selected, it is time to specify the encryption key information used to encrypt the database and enable Encryptionizer. This is also how the product can be configured to support multiple encryption keys for the database, backups, file stream, log shipping, etc.
Also, when you enable Encryptionizer you have additional options including:
- Encrypt the master database
- Encrypt all new SQL Server databases
- Encrypt new SQL Server database backups
- Locking the encryption key to the machine
- Rules to encrypt particular object only
Once the final options are configured for the instance, a summary screen is available to review the options selected before committing the changes as a secured SQL Server instance as shown below.
With just a simple installation and walking through two wizards, your databases are secured in a very short period of time with no code changes. Your databases are encrypted while online, on disk and when backed up which is a major step forward in protecting your client data and meeting regulatory compliance such as GDPR, HIPAA, PCI and 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
- No need to upgrade to SQL Server Enterprise Edition
- Support for all editions and versions of SQL Server as well as the remainder of your Windows Servers, IIS, Exchange, Access and more
- Intuitive wizard-based interface, CLI and script-based deployment options to encrypt SQL Server and any Windows based application data
- 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.
About the author
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.