Search

Skill No 1. Encryption

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.


Perform the following tasks to encrypt data.

  1. Create DMK

  2. Create a certificate that will be protected by the DMK.

  3. Create Symmetric Key using the certificate that will be used by the column encryption.

  4. Encrypt the column using the Symmetric Key

Exercise Zone


With the a simple example where you can encrypt data using symmetric key protected by a password. Note the best practice of backing up the keys and certificates has been excluded. How clever does CTO be?




-- switch to the database

USE WideWorldImporters;

GO


-- add a column to hold encrypted data

ALTER TABLE Sales.Customers

ADD CreditCard Varbinary(max);

GO


SELECT CustomerID, CustomerName, CreditCard

FROM Sales.Customers

WHERE CustomerID = 1;

GO


-- create symmetric key

CREATE SYMMETRIC KEY CreditCardKey

WITH ALGORITHM = AES_128

ENCRYPTION BY PASSWORD = '$trongPassw0rd';

GO


-- add an encrypted credit card, then view data

UPDATE Sales.Customers

SET CreditCard = (EncryptByKey(Key_GUID('CreditCardKey'),'5555-1234-5678-0000'))

WHERE CustomerID = 1;

GO


-- open the key, then add the card again

OPEN SYMMETRIC KEY CreditCardKey

DECRYPTION BY PASSWORD = '$trongPassw0rd';

GO


-- view the data

SELECT CustomerID, CustomerName, CreditCard

FROM Sales.Customers

WHERE CustomerID = 1;

GO


-- view the decrypted data

SELECT CustomerID, CustomerName,

CONVERT (char(19), DecryptByKey(CreditCard)) AS CreditCardNumber

FROM Sales.Customers

WHERE CustomerID = 1;

GO


-- close the key and view decrypted data

CLOSE SYMMETRIC KEY CreditCardKey;

GO


-- create key with a certificate

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewPa$sw0rd';

GO

CREATE CERTIFICATE EncryptionCertificate

WITH SUBJECT = 'Encryption Certificate';

GO

CREATE SYMMETRIC KEY CreditCardKeyCert

WITH ALGORITHM = AES_128

ENCRYPTION BY CERTIFICATE EncryptionCertificate;

GO


-- open the new key

OPEN SYMMETRIC KEY CreditCardKeyCert

DECRYPTION BY CERTIFICATE EncryptionCertificate;

GO


-- close the key

CLOSE SYMMETRIC KEY CreditCardKeyCert;

GO


-- remove certificate

DROP CERTIFICATE EncryptionCertificate;

GO


-- reset the database to its original state

ALTER TABLE Sales.Customers

DROP COLUMN CreditCard;

GO

DROP SYMMETRIC KEY CreditCardKey;

GO

DROP SYMMETRIC KEY CreditCardKeyCert;

GO

DROP CERTIFICATE EncryptionCertificate;

GO

DROP MASTER KEY;

GO


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