Skill No 1. Encryption

Updated: Jul 30, 2021

Before to start with this first edition about Encryption some things that you should know. First, familiarity with creating and querying SQL Server Databases.

Second, some experience with SQL Server Management Studio

Third, Admin credentials to SQL Server Instance

Fourth, Enterprise or Developer edition

Let's start this section with how to configure encryption in SQL Server. We will examine how you can encrypt both data at rest and data in flight. Each encryption technology will have its own strengths, weaknesses and administrative complexity. Some encryption technology will restrict the types of operations that you can perform on your data.

When you configuring encryption it is critical to choose the order of which algorithms, certificates, and keys to operate. It is important to understand what different encryption technologies encrypt, what they protect against, how to configure them.

Encryption Architecture

To understand and implement encryption in SQL Server you need to understand its encryption hierarchy and key management architecture. Layers of encryption are protected by preceding layers of encryption that can use asymmetric keys, certificates, and symmetric keys.

  • Extensible Key Management

  • Service Master Key

  • Database Master Key

  • Asymmetric Key

  • Symmetric Key

  • Certificate

Implement column-level encryption

When implementing column-level encryption, consider the following.

  • Encrypted data cannot be compressed, but compressed data can be encrypted. When using compression, you should compress data before encrypting it for optimal results.

  • Stronger encryption algorithms consume more processor and resources. SQL Server 2016 the database can take the advantage of hardware acceleration, using Intel AES-NI when performing encryption/decryption tasks.

  • SQL Server 2016 the only algorithms that are supported with database compatibility 130 or above are AES.128, AES-192, and AES-256

Symmetric keys can encrypt and decrypt data very quickly, but it's more difficult to secure, should a key get lost or somehow fall into the wrong hands. They can be password-protected, though, meaning that someone would need to know the password in order to use the key for encryption and decryption. So there's a little protection provided there against unauthorized use.

Asymmetric keys work a little differently. These keys come in pairs which are generally noted as public keys and private keys. Data encrypted with a private key can only be decrypted by the matching public key; and data encrypted with a public key can only be decrypted with the matching private key. This allows you to share the public key with anyone that needs to send secure information to you. They can encrypt the data on their end with the public key, and then transmit the encrypted values, and be sure that you will be only one that can decrypt them.


Need more review files, scripts or examples? OK Let me know here - Email Me and we have a complete zone of documentation, scripts and solutions.

Things that you can find in the Paddock Zone.

  1. Implementing column-level encryption using a certificate

  2. Implement Always Encrypted

  3. Implement Transparent Database Encryption

  4. Backup Encryption

39 views0 comments

Recent Posts

See All
Orange Splash
Orange Splash