Excerpt from the eBook "Encryption & Key Management for Microsoft SQL Server."

Microsoft SQL Server has become a ubiquitous storage mechanism for all types of digital assets. Protecting these data assets in SQL Server is a top priority for business executives, security specialists, and IT professionals.  The loss of sensitive data can be devastating to the organization and in some cases represents a catastrophic loss. There is no alternative to a digital existence and cybercriminals, political activists, and state actors have become more and more adept at stealing this information.  To properly protect this information, businesses are turning to encryption and key management.

Encryption

Encryption in the broadest sense means obscuring information to make it inaccessible to un- authorized access. But here we will use the term in its more precise and common use – the use of well accepted encryption algorithms based on mathematical proofs and which have been embodied and approved as international standards.

Many approaches to encryption do not meet minimal requirements for security and compliance. Our definition of encryption excludes:

• Homegrown methods developed by even experienced and talented programmers.
• Emerging encryption methods that are not yet widely accepted.
• Encryption methods that are widely accepted as secure, but which have not been adopted by standards organizations.
• Data substitution and masking methods not based on encryption.

An example of an encryption method that does meet our criteria would include the Advanced Encryption Standard (AES) which is sometimes knows as Rijndael, Triple Data Encryption Standard (3DES), RSA, and Elliptic Curve encryption methods.

In the context of protecting data in a SQL Server data- base, the most common encryption method protecting whole databases or an individual column in a table is AES. All key sizes of AES (128-bit, 192-bit, and 256-bit) are considered secure and are appropriate for protecting digital assets. Many organizations chose 256- bit AES for this purpose due to the larger key size and stronger security.

One major additional benefit of using an industry standard such as AES is that it meets many compliance requirements or recommendations for the use of industry standard encryption. This includes the PCI Data Security Standard (PCI-DSS), HIPAA, FFIEC, and the EU General Data Protection Regulation (EU GDPR).

Key Management

It is not possible to discuss an encryption strategy without discussing the protection of encryption keys. An encryption strategy is only as good as the method used to protect the encryption keys. Encryption algorithms such as AES and Triple DES are public and readily available to any attacker. The protection of the encryption key is the core to the security of the encrypted data. This is why security professionals consider the loss of the encryption key as equivalent to the loss of the digital assets. Once an attacker has the encryption key it is trivial to decrypt and steal the data.

Generating strong encryption keys and protecting them is harder that it might at first appear. The generation of strong encryption keys depends on the use of random number generation schemes, and modern computers do not excel at doing things randomly. Specialized software routines are needed to generate strong encryption keys. Encryption keys must also be securely stored away from the data they protect, and yet must be readily available to users and applications that are authorized to access the sensitive data. Authenticating that a user or application is authorized to an encryption key is a large focus of key management systems.

Over the years standards and best practices have emerged for encryption key management and these have been embodied in specialized security applications called Key Management Systems (KMS), or Enterprise Key Management (EKM) systems. The National Institute of Standards and Technology (NIST) has taken a lead in this area with the creation of Special Publication 800-57 entitled “Recommendation for Key Management”. In addition to this important NIST guidance, the organization publishes the Federal Information Processing Standard (FIPS) 140-2 “Security Requirements for Cryptographic Modules”. To serve the needs of organizations needing independent certification that a key management application meets this standard, NIST provides a validation program for FIPS 140-2 compliant systems. All professional key management systems have been validated to FIPS 140-2.

When protecting sensitive SQL Server data with encryption, look for these core principles of key management:

• Encryption keys are stored away from the data they protect, usually on specially designed security devices or dedicated virtual servers.
• Encryption keys are managed by individuals who do not have access to the data stored in the SQL Server database (Separation of Duties).
• Encryption key management requires more than one security administrator to authenticate before performing any critical work on keys (Dual control).
• Key retrieval requests from users and applications are authenticated using industry standard methods.
• Encryption management and key usage are logged in real time and logs are stored on secure log collection servers.
• Encryption key management systems have been validated to FIPS 140-2 and the Key Management Interoperability Protocol (KMIP).

These are just a few of the core requirements for deploying a professional key management solution to protect your SQL Server data.