Updated: Jul 30
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.
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
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.
Implementing column-level encryption using a certificate
Implement Always Encrypted
Implement Transparent Database Encryption