If you need to store confidential data in your database, you can use data encryption. SQL Server supports encryption with symmetric keys, asymmetric keys, certificates, and password phrases. Let's first have a theoretical look at each of these encryption techniques.

When you use symmetric key encryption, the party that encrypts the data shares the same key with the party that decrypts the data. Because the same key is used for encryption and decryption, this is called symmetric key encryption. This encryption is very fast. However, if an unauthorized party somehow acquires the key, that party can decrypt the data. Protecting symmetric keys is a challenge. The symmetric key must remain secret. Symmetric encryption is also called secret key encryption.

In asymmetric key encryption, you use two different keys that are mathematically linked. You must keep one key secret and prevent unauthorized access to it; this is the private key. You make the other key public to anyone; this is the public key. If you encrypt the data with the public key, you can decrypt the data with the private key; if you encrypt the data with the private key, you can decrypt it with the public key. Asymmetric encryption is very strong; however, it is much slower than symmetric encryption. Asymmetric encryption is useful for digital signatures. A developer applies a hash algorithm to the code to create a message digest, which is a compact and unique representation of data. Then the developer encrypts the digest with the private key. Anybody with a public key from the same pair can decrypt the digest and use the same hash algorithm to calculate the digest from the code again. If the re-calculated and decrypted digests match, you can identify who created the code.

A certificate is a digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. It identifies the owner of the public/private keys. You can use certificates for authentication. A certificate can be issued by a trusted authority or by SQL Server. You can create a certificate from a file (if the certificate was issued by a trusted authority) or a digitally signed executable file (assembly), or you can create a self-signed certificate in SQL Server directly. You can use certificates to encrypt data; of course, this way you are actually using asymmetric encryption.

You should use symmetric keys to encrypt data because secret key encryption is much faster than public-key encryption. You can then use asymmetric encryption to protect symmetric keys and use certificates for authentication. You combine certificates and keys to encrypt data in the following manner:

1. The server sends a certificate and public key to a client. The certificate identifies the server to the client.
2. The client creates two symmetric keys. The client encrypts one symmetric key with the public key and sends it to the server.
3. The server's private key can decrypt the symmetric key. The server and client encrypt and decrypt data with symmetric keys.

When encrypting data, you should consider all possible surface areas for an attack. For example, if you encrypt data in SQL Server, but send clear text over the network, an attacker could use a network monitor to intercept the clear text. You should use on-the-wire encryption, such as Internet Protocol Security (IPSec), a framework of open source standards for network security, or Secure Sockets Layer (SSL)/Transport Layer Security (TLS), which are protocols based on public key cryptography. An attacker can even sniff client computer memory to retrieve clear text. Therefore, you should use .NET encryption in client applications in addition to or instead of server encryption.

Consider the following trade-offs when you design a solution that uses data encryption:

• Encrypted data is typically stored in a binary data type column; space is not allocated according to the original data type as it is with unencrypted data. This means you need to change your database schema to support data encryption.
• Sorting encrypted data is different from sorting unencrypted data and, of course, makes no sense from a business point of view.
• Similarly, indexing and filtering operations on encrypted data are useless from a business point of view.
• You might need to change applications to support data encryption.
• Encryption is a processor-intensive process.
• Longer keys mean stronger encryption. However, the stronger the encryption, the higher the consumption of CPU resources.
• When the length of the keys is the same, then asymmetric encryption is weaker than symmetric encryption. Asymmetric key encryption is also slower than symmetric encription.
• Although you probably already know this, it is still worth mentioning that long and complex passwords are stronger than short and simple ones.

Instead of storing all keys in SQL Server, you can also use an external cryptographic provider to store asymmetric keys used to encrypt and decrypt symmetric keys stored in SQL Server, or to store both asymmetric and symmetric keys outside SQL Server. This is called Extensible Key Management (EKM). For example, you can use the Azure Key Vault service as the external cryptographic provider.

As already mentioned, you can protect symmetric keys with asymmetric keys or certificates. In addition, you can protect them with passwords or even other symmetric keys. You can protect certificates and asymmetric keys stored in SQL Server with the Database Master Key (DMK) and passwords. You protect the DMK when you create it with the Service Master Key (SMK) and password. The SMK is created by SQL Server Setup, and is protected by the Windows system Data Protection Application Programming Interface (DPAPI). This whole encryption hierarchy looks quite complex. The following figure shows the encryption hierarchy in a condensed way. It shows all components and a couple of possible paths to the encrypted data:

SQL Server supports many encryption algorithms. You can choose from DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES. However, from SQL Server 2016 onwards, you should use only the AES-128, AES-192, and AES-256 algorithms; all other algorithms have been deprecated.

You don't need to encrypt all of the data all of the time. SQL Server provides many encryption options, and you should choose the one that suits you the best. In addition, you should also consider encrypting and decrypting the data in the client application. In this section, you will learn about the different encryption options in SQL Server, including the strengths and weaknesses of each option. These options include:

• Backup encryption
• Column-level encryption
• Transparent data encryption
• Always encrypted