In SQL Server, column-level (cell-level) encryption protects data using symmetric encryption. You create a database master key, then a certificate protected by it, then a symmetric key protected by that certificate. Data is encrypted with the EncryptByKey function and decrypted with DecryptByKey, each using the symmetric key and certificate.
SQL Server encrypts table data at the column level using symmetric encryption, protected by a layered key hierarchy. You first create a database master key, then a self-signed certificate protected by that master key, then a symmetric key protected by the certificate. Data is encrypted with the EncryptByKey function and decrypted with DecryptByKey, both referencing the symmetric key and certificate. SQL Server also offers TDE and Always Encrypted.
Key Takeaways
- SQL Server column-level (cell-level) encryption uses symmetric encryption, protected by a hierarchy of keys and a certificate.
- The key hierarchy is: database master key protects a certificate, which protects the symmetric key that actually encrypts the data.
- Data is encrypted with EncryptByKey and decrypted with DecryptByKey, each using the symmetric key and the certificate name.
- SQL Server offers three main encryption options: cell-level encryption, Transparent Data Encryption (TDE), and Always Encrypted.
- TDE encrypts the whole database at rest transparently; Always Encrypted protects data end to end so even the server never sees plaintext.
How Column-Level Encryption Works in SQL Server
Data encryption of a table in SQL Server is done at the column level, column by column, using symmetric encryption (the same key encrypts and decrypts). This is often called cell-level encryption. It relies on a layered key hierarchy, where each key is protected by the one above it. The process:
- Create a database master key: A database master key is created to anchor the hierarchy. It is itself protected by a password and by the service master key at the server level.
- Create a certificate: A self-signed certificate is created, protected by the database master key.
- Create a symmetric key: A symmetric key, used for the actual column-level encryption, is created and protected by the certificate.
- Encrypt the data: The column is encrypted with the EncryptByKey function, supplying the symmetric key and the name of the certificate.
To decrypt, the DecryptByKey function is called, which also requires the symmetric key and the certificate. Because the encryption is symmetric, the same key both encrypts and decrypts, and that key is kept safe by the certificate and master key above it.
The SQL Server encryption key hierarchy
Each layer protects the one below, so compromising the data requires working back up the chain:
- Service Master Key (SMK): created at the server level when SQL Server is installed; protects the database master key.
- Database Master Key (DMK): protects certificates and asymmetric keys within a database.
- Certificate: protects the symmetric key.
- Symmetric key: encrypts and decrypts the actual column data.
The Three Encryption Options in SQL Server
Cell-level encryption is one of three main ways SQL Server can encrypt data. Which one fits depends on whether you need to protect specific columns, the whole database at rest, or data all the way to the client:
| Option | What it encrypts | Best for |
| Cell-level (column) encryption | Specific columns, via EncryptByKey/DecryptByKey | Protecting a few sensitive fields with granular control |
| Transparent Data Encryption (TDE) | The entire database at rest (data and log files) | Protecting data at rest with no application changes |
| Always Encrypted | Specific columns, encrypted and decrypted on the client | Keeping plaintext hidden even from the SQL Server itself |
Cell-level (column) encryption
The approach described above. It gives fine-grained control over individual columns and uses the EncryptByKey and DecryptByKey functions, but the application must explicitly encrypt and decrypt, and encrypted columns cannot be indexed or searched normally.
Transparent Data Encryption (TDE)
TDE encrypts the entire database at rest, both the data and log files, transparently. It uses a database encryption key protected by a certificate in the master database, and requires no changes to applications. TDE protects against someone stealing the physical files or backups, but it does not protect data in use or in transit, and it does not provide column-level granularity. It relies on the same kind of certificate and key protection as cell-level encryption.
Always Encrypted
Always Encrypted encrypts specific columns on the client side, so the data is encrypted before it ever reaches SQL Server and the database engine only ever sees ciphertext. This protects sensitive data even from database administrators and from anyone with access to the server itself, making it well suited to highly sensitive data and separation of duties. The trade-off is more limited query functionality on the encrypted columns.
How Encryption Consulting Helps
Choosing the right SQL Server encryption approach, cell-level, TDE, or Always Encrypted, and managing the underlying keys and certificates securely, is what Encryption Consulting’s Encryption Advisory Services help with. We assess where sensitive data lives, recommend the encryption model that fits your compliance and performance needs, and design sound key management (including HSM-backed key protection) aligned to standards such as NIST and PCI DSS. Backed by ISO/IEC 27001:2022 and SOC 2 certified practices.
Frequently Asked Questions
How is encryption done in SQL Server?
SQL Server most commonly encrypts data at the column level using symmetric encryption. You create a database master key, then a certificate protected by that master key, then a symmetric key protected by the certificate. Data is encrypted with the EncryptByKey function, supplying the symmetric key and certificate. This layered key hierarchy means each key is protected by the one above it. SQL Server also offers Transparent Data Encryption and Always Encrypted for other scenarios.
How do you decrypt data in SQL Server?
To decrypt column-level encrypted data, you open the symmetric key (which requires access to the protecting certificate and master key), then call the DecryptByKey function on the encrypted column, supplying the symmetric key and certificate. DecryptByKey returns the original plaintext value, which is typically converted back to its original data type. Only sessions with permission to open the symmetric key can decrypt the data, so it stays protected from unauthorized users.
What are EncryptByKey and DecryptByKey?
EncryptByKey and DecryptByKey are the built-in SQL Server functions for cell-level (column) encryption. EncryptByKey takes a symmetric key and a plaintext value and returns encrypted varbinary data, while DecryptByKey reverses the process to return the original value. Both require the relevant symmetric key to be open in the session, and that key is itself protected by a certificate and the database master key, forming SQL Server’s encryption key hierarchy.
What is the difference between TDE and cell-level encryption in SQL Server?
Cell-level (column) encryption protects specific columns using EncryptByKey and DecryptByKey, giving fine-grained control but requiring the application to encrypt and decrypt explicitly. Transparent Data Encryption (TDE) encrypts the entire database at rest, including data and log files, transparently and with no application changes. Cell-level encryption suits a few highly sensitive fields; TDE suits protecting the whole database against stolen files or backups. Many organizations use both together.
What is Always Encrypted in SQL Server?
Always Encrypted is a SQL Server feature that encrypts specific columns on the client side, before the data reaches the server, so the database engine only ever sees ciphertext. This means sensitive data is protected even from database administrators and anyone with server access, which supports separation of duties and compliance. The trade-off is reduced query functionality on encrypted columns. It differs from cell-level encryption, where the server performs the encryption and decryption.
Which encryption should I use in SQL Server?
It depends on what you need to protect. Use cell-level encryption for granular control over a few sensitive columns. Use Transparent Data Encryption (TDE) to protect the entire database at rest with no application changes, guarding against stolen files or backups. Use Always Encrypted when plaintext must be hidden even from the SQL Server and its administrators. Many environments combine them, and the right mix depends on your data sensitivity, performance, and compliance requirements.
Encrypt Your Databases the Right Way
SQL Server gives you several encryption options, and the right choice (plus sound key management) is what keeps sensitive data protected without breaking your applications. Explore Encryption Consulting’s Encryption Advisory Services to design and implement database encryption that fits your environment.