Echoes from the field 8: Encryption in SQL Server (Part 1)
During a recent consulting engagement, I was asked about how to use column-based encryption in SQL Server. In this article which is the first of a two-part series, let’s explore the technologies involved and how to get started with using column-based encryption. Next time, we’ll continue with using these technologies for column-based encryption.
Encryption Terminology
Encryption is the science of hiding secrets. Information that needs to be protected (often called plaintext) is converted (or encrypted) to a form (often called ciphertext) that is difficult for another person to read within a reasonable period of time.
Note that I didn’t say impossible to read. The strength of the encryption determines how much effort would be required to decipher the encrypted data. The effort could relate to the speed of the computer that is attempting to crack the encryption and to how long the attempts would continue for. What we normally aim for today, is to make data unable to be read with typical computing power in a reasonably long period of time.
It is important, though, to realize that computing power tends to increase constantly. This means that data that can’t be cracked today might well be able to be cracked within a few decades, or in an even shorter time. Data may need to be re-encrypted in the future if long-term protection is required.
Forms of Encryption
Today, there are two basic forms of encryption: symmetric encryption and asymmetric (or public key) encryption. Symmetric encryption uses the same key for both encrypting and decrypting the data. The biggest challenge with symmetric encryption is the problem of exchanging keys. If I need to send you data securely, how do I send you the key that you will need to decrypt the data?
Asymmetric encryption gets around this problem by using one key for encrypting the data and another key for decrypting the data. The two keys are mathematically related but the combination of the complexity of the algorithm that is used and the length of the keys (measured in bits) means that it is computationally-infeasible to derive one key from the other key, at least it’s infeasible today. One key (the private key) is kept secret and the other key (the public key) can be sent in an unencrypted form to the other party.
If I encrypt data using my private key and you can decrypt it using my public key, then you know that it was me that sent the data. This is known as using a digital signature. If you encrypt data with my public key and send it to me, I am on the only one that can read it because I have the private key. Sending data in this way is referred to as using a digital envelope.
Certificates
You will notice that in the last section, I said if you use my public key. But how do you know that it really is my public key and not someone else’s public key? This is where certificates are used. Encryption is based on layers of trust. (This terminology reminds me of the circle of trust in the movie Meet the Parents).
What we need is for someone that you trust to assure you that I really am who I say I am. More specifically, we need them to confirm the key that I claim to be my public key.
A certificate is a digital document that associates an identity (such as my web server) with a public key. A party that we both trust (the certificate authority) digitally signs the document that matches my identity to my public key, using the private key of the certifying authority.
The public key of the certificate authority is already known to you. If you can decrypt the certificate with the public key of the certificate authority, then you know that the certificate was created by that authority. If you trust that authority, then you also believe that my identity matches my public key.
This still raises a chicken and egg problem in how you validate the public key of the certificate authority. And this is where Microsoft Windows helps. Microsoft have pre-certified a number of certificate authorities on your behalf. If you click on the Publishers button on the Content tab within the Internet Options for your browser, you will see something similar to the screenshot below:
The exact image will depend upon the browser but I picked a common old one to make the point that it’s everywhere.
On the Trusted Root Certification Authorities tab, you can see the list of certificate authorities that are currently recognized by your system. You can modify this list if needed.
You can also manually add another certificate authority as a Trusted Publisher. It is common to do this within a closed group such as a company. The company could run a certificate authority that generates certificates that are trusted by employees of the company but probably not trusted by anyone else.
Certificate Expiry and Revocation
Certificates can expire. Note that in the screenshot above, you can see the expiry date for each of the trusted root publishers. For a certificate to be valid, it needs to be:
- Issued by a trusted source
- Not yet expired
- Not revoked
- Used for the appropriate purpose
There are different types of certificates that can be used for different purposes. For example, web servers can have certificates that are used for ecommerce purposes but software vendors can use certificates for digitally signing software.
Certificates can also be revoked. The certificate may have been issued in error or the claims made in the certificate may no longer be valid. In that case, before trusting a certificate, the certifying authority provides the ability to check to see if a certificate has been revoked. Within each certificate, the certificate authority encodes the address of a certificate revocation list (CRL). This list details certificates that have been revoked.
We’ll see more in the next post in this series.
Encryption training
You should learn about how encryption and secrets work in SQL Server, as well as more about general security. You can do that with our course
SQL Server Encryption for Developers and DBAs
2025-11-18