+1.800.357.1019

+1.800.357.1019

Feel free to call us toll free at +1.800.357.1019.

If you are in the area you can reach us at +1.360.359.4400.

Standard support
6:30am - 4:00pm PST, Monday - Friday, Free

Premium support
If you own Townsend Security 24x7 support and
have a production down issue outside normal
business hours, please call +1.800.349.0711
and the on-call person will be notified.

International customers, please dial +1.757.278.1926.

Townsend Security Data Privacy Blog

Microsoft SQL Server Automatic Encryption - Cell Level Encryption

Posted by Patrick Townsend on Feb 21, 2017 9:11:00 AM

In this third part of the series on Microsoft SQL Server encryption we look at Cell Level Encryption, or CLE, which is Microsoft terminology for Column Level Encryption. With CLE the manner and timing of SQL Server’s call to the EKM Provider software is quite different than for Transparent Data Encryption. It is important to understand these differences in order to know when to use CLE or TDE. Let’s look at some aspects of the CLE implementation:

Encrypted Columns
Encryption-Key-Management-SQL-Server Cell Level Encryption is implemented at the column level in a SQL Server table. Only the column you specify for encryption is protected with strong encryption. You can specify more than one column for CLE in your tables, but care should be taken to avoid performance impacts of multiple column encryption (see below).

With Cell Level Encryption you may be able to minimize some of the encryption performance impacts on your SQL Server database. Because the EKM Provider is only called when the column must be encrypted or decrypted, you can reduce the encryption overhead with careful implementation of your database application code. If a SQL query does not reference an encrypted column, the EKM Provider will not be invoked to perform decryption. As an example, if you place the column Credit_Card under CLE encryption control, this query will not invoke the EKM Provider for decryption because the credit card number is not returned in the query result:

SELECT Customer_Number, Customer_Name, Customer_Address FROM Orders ORDERBY Customer_Name;

You can see that judicious use of SQL queries may reduce the need to encrypt and decrypt column data.

SQL Application Changes
Unlike Transparent Data Encryption you must make a change to the SQL statement in order to implement Cell Level Encryption. The SQL Server functions “encryptbykey” and “decryptbykey” are used on SQL statements. Here is an example of a SQL query that encrypts a CLE-encrypted column:

select encryptbykey(key_guid('my_key'), 'Hello World');

Implementing CLE encryption in your SQL Server database requires modifications to your applications, but may be well worth the additional work.

Encryption and Key Retrieval
The EKM Provider software is called for each column value to perform encryption and decryption. This means a larger number of calls to the EKM Provider compared to Transparent Data Encryption. Because the number of calls to the EKM Provider may be quite large it is important that the encryption and key management functions of the EKM Provider are highly optimized for performance (see the next section).

The EKM Provider software from your key management vendor is responsible for performing encryption of the data. From a compliance point of view it is important to understand the encryption algorithm used to protect data. Be sure that the EKM Provider software uses a standard like the Advanced Encryption Standard (AES) or other industry recognized standard for encryption. It is common to use 128-bit or 256-bit AES for protecting data at rest. Avoid EKM Providers which implement non-standard encryption algorithms.

Encryption Key Caching
When deploying CLE it is important that the EKM Provider software optimize both encryption and key management. The number of calls to the EKM Provider software can be quite high. Good EKM Providers will securely cache the symmetric key in the SQL Server context rather than retrieve a key on each call. The retrieval of an encryption key from a key server takes precious time and multiple calls to retrieve a key can have severe performance impacts. Secure key caching is important for CLE performance. The use of the Microsoft Windows Data Protection Application Program Interface (DPAPI) is commonly used to protect cached keys.

Performance Considerations
When properly implemented Cell Level Encryption can reduce the performance impact of encryption on your SQL Server database. For very large tables with a small number of columns under encryption control, the performance savings can be substantial. This is especially true if the column is used less frequently in your applications.

CLE Vendor Note
Note that each vendor of EKM Provider software implements encryption and key management differently. Some EKM Providers only implement Transparent Data Encryption (TDE). If you suspect you will need Cell Level Encryption be sure that your key management support includes this capability.

In the next part of this series we will look at encryption key management in SQL Server.

Patrick

Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, Cell Level Encryption, SQL Server encryption

4 Ways to Encrypt Data in Microsoft SQL Server

Posted by Patrick Townsend on May 6, 2013 4:29:00 PM

Almost every organization has at least one application built on Microsoft’s SQL Server database. Whether you build an application in-house using Microsoft’s development tools or you deploy a software package from a software vendor, chances are that your organizations has one or more SQL Server databases to help you manage information.

The Challenge: Protect Data with SQL Server’s Encryption

Encryption-Key-Management-SQL-Server Today it is almost impossible to run a business without handling sensitive information and storing storing data such as customer names, credit card numbers, bank account numbers, passwords, email addresses, or other personally identifiable information (PII) or private health information (PHI) in your SQL Server database. If your organization must meet data security regulations such as PCI-DSS, HIPAA/HITECH, or GLBA/FFIEC, you probably already know that this data must be encrypted in order to protect your customers and prevent data loss in the event of a data breach.

What you may not know is that in order to truly protect your data, you must manage your encryption keys in adherence to key management best practices such as dual control and separation of duties using an external encryption key manager (key managers are available in VMware, Cloud, as a traditional harware security moule or HSM). Your company will only be able to avoid data breach notification if you are using these best practices.

The good news is that SQL Server 2008-2016 comes equipped with transparent data encryption (TDE) and extensible key management (EKM) to make encryption and key management using a third-party key manager easier than ever. Older versions of SQL Server can also be easily encrypted using different tactics, and you can manage those encryption keys just as easily with an encryption key manager as well.

Encrypting Data in SQL Server Depends on Your Version

If you’re currently looking into encrypting your SQL Server database or deploying a key management system, you may be concerned about how to protect your data depending on the version, code, and language used to build your database. To help ease your worries, here are 4 ways to encrypt your SQL Server database and protect your encryption keys:

  1. Since SQL Server 2008 Microsoft has supported automatic encryption with TDE and cell level encryption for Enterprise Edition users and above. Without any programming you can encrypt the SQL Server database or an individual column, and store the keys on an encryption key management HSM.
  2. If you have an older version of SQL Server, or you have SQL Server Standard Edition or Web Edition, you don’t have access to TDE. But you can still automate encryption: Through the strategic use of SQL Views and Triggers, you can automate encryption of sensitive data on your SQL Server without extensive program modifications, and still use a secure key managemer to protect the encryption keys.
  3. Your developers might have written custom application code to implement your SQL Server database. But SQL Server encryption and key management is still within your reach. A good key management vendor should supply you with software libraries that easily add into your applications and implement SQL Server encryption.
  4. You might have a SQL Server database, but not be using Microsoft programming languages. Perhaps your applications are written in Java, Perl, or PHP. Again, it is simple to deploy software libraries that encrypt the SQL Server data and which store the encryption keys on an external centralized key manager.

SQL Server encryption and good key management is not difficult to achieve. Although key management has a reputation for being difficult and costly, today key management for SQL Server is cost-effective, easy, has little to no performance impact, will get your company in compliance, and will keep your organization out of the headlines by helping to prevent a data breach.

To learn more about key management for SQL Server, download the White Paper, “Encryption Key Management for Microsoft SQL Server 2008/2016.”

Encryption and Key Management for Microsoft SQL Server

 

Topics: Extensible Key Management (EKM), Microsoft, Encryption Key Management, White Paper, SQL Server, SQL Server encryption


Subscribe to Email Updates

Posts by Topic

see all