Choosing the right encryption feature for SQL Server

Encryption is the process of encoding information to protect data from unauthorized access in different scenarios.

There are many different encryption technologies offered by SQL Server and Azure SQL Database.

This post shows these features to help you decide which technology to choose and combine them to provide a layered security approach.

SSL Transport Encryption

SQL Database, SQL Managed Instance, and Azure Synapse Analytics secure customer data by encrypting data in motion with Transport Layer Security (TLS).

Like websites, SQL Server can be configured to use Secure Sockets Layer (SSL) to encrypt traffic as it travels between the server and client applications. Additionally, the client can validate the server’s identity using the server’s certificate.

The SSL only protects data as it travels across the network, but, unlike most SQL Server encryption forms, SSL is available in all supported SQL Server versions and all editions.

Before enabling SSL, you’ll need to install a certificate on the SQL Server. The best way to do this is by requesting a certificate from your own enterprise certification authority (CA). Windows Server can be configured as a CA, and you can set up clients to trust the certificates that it issues. Alternatively, it is possible to use self-signed certificates, although this is best suited to test environments.

Check how to enable SSL encryption for an instance of SQL Server. SQL Database, SQL Managed Instance, and Azure Synapse Analytics enforce encryption (SSL/TLS) at all times for all connections.

As a best practice, recommend that you specify an encrypted connection in the application’s connection string and not trust the server certificate. This forces your application to verify the server certificate and prevents your application from being vulnerable to man in the middle type attacks.

Limitations

  • Encryption with a self-signed certificate is possible, but a self-signed certificate offers only limited protection.
  • The encryption level used by TLS, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system running on the application and database computers.
  • The SQL Server service account must have read permissions on the certificate used to force encryption on the SQL Server. For a non-privileged service account, read permissions will need to be added to the certificate. Failure to do so can cause the SQL Server service restart to fail.

Always Encrypted

Always Encrypted is explicitly designed to protect and safeguard sensitive data stored in Azure SQL Database or SQL Server databases such as social security numbers, national identification numbers, credit card numbers, just to name a few.

The Always Encrypted feature protects sensitive data from high-privilege users and malware in the database environment. By protecting data from high-privilege users who have no “need-to-know,” Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).

Data stored in the database is protected even if the entire machine is compromised, for example, by malware. Encrypted leverages client-side encryption: a database driver inside an application transparently encrypts data before sending it to the database. Similarly, the driver decrypts encrypted data retrieved in query results.

Always Encrypted, cryptographic operations on the client-side use keys that are never revealed to the Database Engine (SQL Database or SQL Server). There are two types of keys in Always Encrypted:

Column encryption keys are used to encrypt data in the database. These keys are stored in the database in encrypted form (never in plaintext).

Column master keys are used to encrypt column encryption keys. These keys are stored in an external key store, such as Windows Certificate Store, Azure Key Vault, or hardware security modules.  For keys stored in Azure Key Vault, only the client application has access to the keys, but not the database, unlike TDE.

Always Encrypted only supports very limited operations on encrypted database columns ( partially transparent to applications ). This is one reason why we recommend using Always Encrypted to protect truly sensitive data in selected database columns. In this Microsoft article, you can check how to configure Always Encrypted by using Azure Key Vault.

When to Use Always Encrypted

Because Always Encrypted functions by modifying the SQL operation before it interfaces with the SQL Server database, and because many complex SQL operations will not work with Always Encrypted, I would only recommend using Always Encrypted when the application architecture is very simple.

Always Encrypted is great for very limited amounts of data that you don’t search on or display to end-users.

For example, you might want to use Always Encrypted to send data from an internal SQL Server database to a web-hosted SQL Server database and application. The data will be protected in transit and encrypted in the database. As long as your web application makes simple SQL queries to the database, this approach can work well.

Limitations

  • To implement this feature, modification of the existing applications may be required;
  • You can’t involve tempdb to include part of AE;
  • Columns that are using one of the subsequent datatypes such as: xml, timestamp, rowversion, image, ntext, text, sql_variant, geography, hierarchyid, alias, geometry, user defined-types, etc.
  • Some of the encrypted data types may require a “bin2” collation type, which may require few DDL code changes.
  • The application administrator will need to fully understand the encryption keys to ensure that they are protected
  • Database administrators and other unintended audiences.
  • The encryption keys will also need to be backed up for disaster recovery.
  • Adding AE may increase your database size and CPU usage, and adding encryption may also prevent any deduplication algorithms.
  • Some of the SQL Server features such as replication are not currently supported in all editions. Instead, you might have to upgrade to the costlier Enterprise Edition (Prior to SQL Server 2016 (13.x) SP1, Always Encrypted was limited to the Enterprise Edition).

The Always Encrypted feature provides an entry-level additional security level for sensitive data that may reduce administrative costs. Yet, the requirements tend to depend on new application development rather than tweaking the existing systems.

Transparent Data Encryption

TDE adds a security layer to protect data at Rest from offline access to raw files or backups. Typical scenarios include data center theft or unsecured hardware or media disposals such as disk drives and backup tapes.

Enabling TDE on databases provides the ability to comply with many laws, regulations, and security guidelines established across various industries that require data to be encrypted at Rest. Unless data stored in a SQL database has no protection requirements, there should be no reason to disable TDE.  In this Microsoft article, you can check all the steps to enable TDE.

It is essential to recognize that TDE only adds one layer of protection for data at Rest, and the remaining risks must be addressed at the OS file system and hardware layer. See Bitlocker documentation to learn more.

TDE encrypts the entire database using an AES encryption algorithm, which doesn’t require developers to make changes to existing applications.

TDE performs real-time I/O encryption and Decryption of the data and logs files. Once the database is loaded into memory, the data is accessible to administrators (DBAs) of SQL Server (sysadmin, db_owner) and Azure SQL Database (cloud) administrators and can be used by all roles and applications that have access to the database.

TDE offers two options for encryption key management, service managed keys, and customer-managed keys. For service managed keys, the TDE protector is a certificate stored in the master database of the server. For customer-managed keys, the TDE protector is an asymmetric key protected by an EKM module or Azure Key Vault.

When using  Azure SQL Database TDE with Bring Your Own Keys (BYOK) the TDE Protector never leaves the key vault. Still, Azure SQL Database needs to access the key vault to decrypt and encrypt the Database Encryption Key (DEK) used to decrypt and encrypt the data. After the encrypted DEK is sent to the key vault for Decryption, the unencrypted DEK is stored in memory until the TDE Protector is deleted or access to SQL Database has been revoked. In this case, the database will go offline within 24 hours, and the DEK will be removed from memory.

Limitations

The following operations are disallowed during initial database encryption, key change, or database decryption:

  • Dropping a file from a filegroup in a database;
  • Detaching, Dropping, or Taking a database offline;
  • Transitioning a database or filegroup into a READ ONLY state

The following operations are disallowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, and ALTER DATABASE…SET ENCRYPTION statements:

  • Dropping a file from a filegroup in a database
  • Detaching, Dropping, or Taking a database offline;
  • Transitioning a database or filegroup into a READ ONLY state
  • Using an ALTER DATABASE command
  • Starting a database or database-file backup/restore
  • Creating a snapshot

The following operations or conditions prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, and ALTER DATABASE…SET ENCRYPTION statements:

  • A database is read-only or has read-only filegroups.
  • An ALTER DATABASE command is running.
  • A data backup is running.
  • A database is in an offline or restore condition.
  • A snapshot is in progress.
  • Database maintenance tasks are running.
  • When database files are created, instant file initialization is unavailable when TDE is enabled.
  • To encrypt a database encryption key with an asymmetric key, the asymmetric key must be on an extensible key-management provider

Cell-level encryption (CLE)

Another method for encrypting your data is via cell-level encryption (CLE) to protect and secure your data at Rest. Similar to Always Encrypted, CLE  is used to encrypt specific columns or cells. Cell-level encryption uses symmetric encryption and is often referred to or called column-level encryption.

A key benefit of CEL is that you can encrypt individual cells/columns with different keys. Cell-level is also relatively fast and is a great option when working with large amounts of data.

With CEL, encryption and Decryption are done by explicitly calling the ENCRYPTBYKEY or DECRYPTBYKEY functions.  Both return a Varbinary type, and thus when storing CLE-encrypted data, the column type must be Varbinary with a maximum size of 8000 bytes.

These functions required the use of a symmetric key, which must be opened to be used. When created, the database master key is encrypted using AES_256 algorithm along with a user-supplied password.

With the master key created, you need to create a certificate and symmetric key too. For more details, see on this link how to encrypt a Column of Data.

While a case for using TDE and CLE together could be made, they are typically used for different proposes. CLE has advantages over TDE when encrypting small amounts of data, but CLE should be considered when performance is not too much a concern.

With CLE, the data is still encrypted when it is loaded into memory and allows for a higher degree of customization. On the other hand, TDE can be very simple to deploy with no changes to the application or database, and the performance is better than CLE.

Figure 1 – Dataset with encrypted column (Credit card number).

When to use TDE or CLE

Transparent Data Encryption and Cell Level Encryption are implemented directly in the SQL Server database itself. This means that complex SQL operations are fully supported, and you will not find limitations imposed by your encryption or application strategy in the future.

The SQL Server implementation of TDE and CLE also supports a standardized interface for encryption key management. This means you will have choices in key management vendors and can readily find a solution.  TDE also has good performance metrics for most SQL Server customers and is exceptionally easy to implement.

Limitations

  • Requires changes to the schema since encrypted data is stored in VARBINARY format. This is not an issue for new databases though.
  • Encryption and Decryption affects the overall performance of the database.
  • Table scans could be expensive, as indexes are also encrypted.
  • Opening of Symmetric Key will not work in User Defined Functions (UDF);

Dynamic Data Masking (DDM)

Dynamic data masking was introduced in SQL Server 2016 edition and Azure SQL Database. It is configured by using basic Transact-SQL commands or using the Azure portal, see Get started with SQL Database Dynamic Data Masking (Azure portal).

This feature is to restrict the revelation of sensitive data. It prevents the users by eliminating access to the data to view it. This is a complementary security feature and needs to be used with other security features such as audit, encryption, or row-level security. It is highly recommended to use this feature in conjunction with them to better protect the sensitive data in the database.

Dynamic data masking (DDM) masks sensitive data on the fly while protecting sensitive data to be viewed by non-privileged or designated users. It has few simple masking functions that are either inbuilt or can customize based on your own need, and through that, you can prevent users from few unmasked data.

Figure 2 – Azure DDM feature overview.

When to use DDM

This feature’s advantage is that it does not require any coding effort from the application side or encrypting or applying any change to the real data stored in the disk.

One example of DDM is that when calling the customer care of a credit card company, they validate either the last 4 or 6 digits of SSN or any other sensitive customer data. If the application does support DDM, the call center agent may only ask for a few digits rather than asking for the whole sensitive number.

Limitations

A masking rule cannot be defined for the following column types:

  • Columns with already defined encryption such as Always Encrypted
  • Associated with FILESTREAM
  • It cannot be configured on a computed column. However, if the computed column depends on a column with a MASK, it will return the masked data.
  • It can’t be a part of Full-Text Index
  • It can’t be performed on a column with any dependency. As a workaround, remove the dependency first and then add DDM, and finally recreate the dependency. Let’s say if the dependency is on a column in an index, then drop the index first, then apply the mask, and finally recreate the dependent index.

Quick summary

Conclusion

In most cases, these are complementary technologies, and if you are getting serious about encryption, you may choose to implement more than one.

One thing that may influence your decision on what to use is what is available in the versions and editions of SQL Server that you have in production.

But, remember that any system is only as strong as its weakest link.

No solution you pick will be 100% secure and safe, just by implementing one tool or another.

Securing SQL Server can be viewed as a series of steps. Let’s get on the call, and we will walk you through how we will keep your database safe. Contact us!

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *