Containers continue to make their way into enterprise use, and this article looks at how a Fortune 500 financial services firm is applying SQL Server containers as part of an enterprise Extensible Key Management system for encryption, with secure use of encrypted credentials in a secrets store. Full disclosure, I am a principal at Windocks, a provider of an independent port of Docker’s source to Windows, which is also a general purpose database cloning solution. The approach outlined here can be applied to both Windocks SQL Server containers and Microsoft’s SQL Server containers.
This article looks at a 500 GB SQL Server TDE enabled database, served in the form of a SQL Server database clone. Clones deliver full read/write operations in seconds, and use less than 40 MB of storage. Clones are ideal for supporting development, QA, as well as reporting and BI, and the approach outlined in this article runs on any public cloud or private infrastructure.
SQL Server TDE Enabled Database Clones
SQL Server cloned environments can include a single database or scores of databases, some of which may be encrypted and others not. The cloned environment is built with appropriate user/group permissions and includes data masking and other preparations during the image build. A single full byte copy is built as a parent image, which is cloned and delivered in seconds. Database clones are based on Windows Virtual Hard Drives, either locally or on SMB or NFS network attached file shares.
When we restore an encrypted backup or mount a TDE enabled database to either Windocks SQL Server containers or Docker SQL Server containers, we encounter SQL Server error 15581, “create or restore the Master Encryption Key prior to performing this operation.” In order to work with a TDE enabled database image, the containers are enabled with the Master Encryption Key prior to mounting the TDE enabled database.
A similar process can be supported by using Pure Storage or other Storage Arrays (SANs), by automating the delivery of a SAN snapshot. This is a popular approach and doesn’t alter the workflow described below. We’ll explore how containers offer a new approach to working with SANs in a future article.
SQL Server Containers With TDE Encryption
Windocks SQL Server containers are created by cloning a host installed SQL Server instance, and inherit the parent instance Master database configuration which includes the Master Encryption Certificates. The new container requires the Master Encryption Certificate be refreshed, to address the SQL error 15581 mentioned above. We do this by applying the following SQL script.
The key to making this work is the script must be run prior to mounting the encrypted databases. Windocks supports control of SQL script operations through the use of file extensions. Scripts with .sqlsys extensions are run prior to mounting the databases, while .sql scripts are run after databases are mounted. This allows the Master Encryption Certificate to be regenerated prior to attempting work with the TDE enabled database.
The complete process is accomplished with a dockerfile. Step 1 is the >docker build is submitted by the client, and the container is provisioned (Step 2). Then, the TDE.sqlsys script is run (Step 3), and the cloned database environment is mounted (Step 4).
In practice, most will work with a clonable database image, and the dockerfile captures the above steps in two lines. The image name, tdeclone_1_30, specifies the SQL Server version, and the cloned databases to be mounted. These clones can be used with any SQL Server application environment (Docker SQL containers and conventional instances).
Supporting Secure Credentials Within an EKM System
Extensible Key Management is common in financial service organizations that rely on Vormetric and other encryption systems for enterprise-wide solutions. The systems involve the use of VM level credentials, and the advent of short-lived containers raises the question: “How will a team use credentials securely during container provisioning?”
The solution involves an encrypted key store and environment variables. Windocks supports encrypted secrets that are decrypted for use by the Windocks service. Each secret is stored and referenced with an environment variable:
SQL_RUN_AS_PASSWORD1=”encrypted_password”
With the EKM user credentials encrypted, they can be used securely in building containers. File extensions are used to recognize scripts that incorporate user credentials, with the use of .sqlsysrunas or .sqlrunas.
Conclusions and Next Steps:
Combining SQL Server containers with database cloning provides fast delivery of complete environments, for development, QA, reporting, and BI. A complete environment with a Terabyte class database can be delivered in roughly 45 seconds and is an ideal on-ramp for developer self-service and a significant start toward modernizing full stack software development. The processes outlined in this article are an easy addition to almost any enterprise infrastructure and is the logical place to start for modernization workflows involving SQL Server.
This article highlights how containers are being adapted to meet enterprise needs, including the secure use of encrypted secrets, and sequential control over container build operations. Windocks is a general purpose SQL Server cloning tool with support for use with Windocks containers, or Microsoft’s official Docker SQL containers and conventional instances.
Explore the use of SQL Server containers and database cloning with the free Windocks Community Edition.