Backup encryption - Transparent data encryption - Always Encrypted -Column level encryption differences

This blog post covers

1.Backup Encryption
2.TDE
3.Always Encryption
4. Cell level & column level encryption

5. To encrypt a column of data using symmetric encryption that includes an authenticate

6.its relevant questions for practice.

~~~~~~~~~~~~~~~1.Backup Encryption~~~~~~~~~~~~~

-- Executed on SOURCE

-- Create a database "master" key.
USE Master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'els2234234ljljasdfLJLJ23'
GO 

CREATE CERTIFICATE CryptoBackup

WITH SUBJECT = 'CryptoBackup',

 EXPIRY_DATE = '20301031';

GO 

-- Does not have to be same password as "master" key.

BACKUP CERTIFICATE CryptoBackup TO FILE = 'C:\Temp\CertBackup.cer'
  WITH PRIVATE KEY (FILE = 'C:\Temp\CertBackup.pvk',
  ENCRYPTION BY PASSWORD = 'els2234234ljljasdfLJLJ23')
GO

BACKUP DATABASE [ToEncrypt]
TO DISK = N'C:\Temp\ToEncrypt.bak'
WITH
  COMPRESSION,
  ENCRYPTION  
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = CryptoBackup
   ),
  STATS = 10
GO 

---------------------------
-- Executed on TARGET

-- Create a database "master" key.
USE Master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'els2234234ljljasdfLJLJ23'
GO

-- Copy Certificate to target then execute create command
CREATE CERTIFICATE CryptoBackup
    FROM FILE = 'C:\Temp\CertBackup.cer'
    WITH PRIVATE KEY (FILE = 'C:\Temp\CertBackup.pvk',
    DECRYPTION BY PASSWORD = 'els2234234ljljasdfLJLJ23');
GO

-- Copy bak to TARGET then Restore database from encrypted backup using Certificate
USE [master]
RESTORE DATABASE [ToEncrypt]
 FROM DISK = N'C:\Temp\ToEncrypt.bak' WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5
GO

-- Error if missing the certificate
Msg 33111, Level 16, State 3, Line 56
Cannot find server certificate with thumbprint '0xA70B9D1DC7B5ECFF4B84205340987FA692EA2889'.
Msg 3013, Level 16, State 1, Line 56
RESTORE DATABASE is terminating abnormally.

~~~~~~~~~~2.TDE ~~~~~~~~~~~~~

USE Master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
CREATE CERTIFICATE TestSQLServerCert
WITH SUBJECT = 'Certificate to protect TDE key',
 EXPIRY_DATE = '20301031';
GO

-- Create a database to be protected by TDE.
CREATE DATABASE CustRecords;
GO
-- Create a database encryption key, that is protected by the server certificate in the master database.
USE CustRecords;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;
GO
-- Create a backup of the server certificate in the master database.
-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server
-- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).
Use master
go
BACKUP CERTIFICATE TestSQLServerCert
TO FILE = 'C:\Temp\SQLServerCertificate'
WITH PRIVATE KEY
(
    FILE = 'C:\Temp\SQLServerPrivateKeyFile',
    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
-- Switch to the new database.
-- Alter the new database to encrypt the database using TDE.
use CustRecords
go
ALTER DATABASE CustRecords SET ENCRYPTION ON;
GO


~~~~~~~3.Always Encrypted~~~~~

If exists drop otherwise ignore

USE [master]
/****** Object:  ColumnEncryptionKey [MyCEK]    Script Date: 21-03-2018 12:27:57 ******/
DROP COLUMN ENCRYPTION KEY [MyCEK]
GO




USE [master]
/****** Object:  ColumnMasterKey [MyCMK]    Script Date: 21-03-2018 12:27:09 ******/
DROP COLUMN MASTER KEY [MyCMK]
GO


The following Transact-SQL creates 
1.column master key metadata, 
2.column encryption key metadata, 
3.A table with encrypted columns.

• ## Always Encrypted Terms
• -- Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
• -- Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

• -- Column master keys are protecting keys used to encrypt column encryption keys. Column master keys must be stored in a trusted key store. Information about column master keys, including their location, is stored in the database in system catalog views.
• -- Column encryption keys are used to encrypt sensitive data stored in database columns. All values in a column can be encrypted using a single column encryption key. Encrypted values of column encryption keys are stored in the database in system catalog views. You should store column encryption keys in a secure/trusted location for backup. 

CREATE COLUMN MASTER KEY MyCMK  
WITH (  
     KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',   
     KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'  
   );  
---------------------------------------------  
CREATE COLUMN ENCRYPTION KEY MyCEK   
WITH VALUES  
(  
    COLUMN_MASTER_KEY = MyCMK,   
    ALGORITHM = 'RSA_OAEP',   
    ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86  
);  
---------------------------------------------  
CREATE TABLE Customers (  
    CustName nvarchar(60)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    SSN varchar(11)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
        ENCRYPTION_TYPE = DETERMINISTIC ,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    Age int NULL  
);  
GO 

4.Encrypt a Column of Data

--------------------------------------

 Encrypt a Column of Data

How to encrypt a column of data by using symmetric encryption in SQL Server 2017 using Transact-SQL. This is sometimes known as column-level encryption, or cell-level encryption.

--You must have a database master key
-- This is used to encrypt & decrypt the key


CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '<some strong password>';

-- Create a certificate
-- This is used by SQL Server to encrypt & decrypt the data when it is used

USE AdventureWorks2012;
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = 'Customer Credit Card Numbers',
 EXPIRY_DATE = '20301031';
GO
--Create a symmetric key
  --( This is used by the certificate to encrypt & decrypt the data when it is accesses)
CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
    ADD CardNumber_Encrypted varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
    , CreditCardID)));
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.

SELECT CardNumber, CardNumber_Encrypted
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 ,
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

To encrypt a column of data using symmetric encryption that includes an authenticator

   In Object Explorer, connect to an instance of Database Engine.
   On the Standard bar, click New Query.
   Copy and paste the following example into the query window and click Execute.

USE AdventureWorks2012;
GO

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers',
    EXPIRY_DATE = '20301031';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks2012];
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;

GO

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Q.Which of the following option should you use with the Backup to ensure that your backup will be encrypted?

1. with description
2.with checksum
3.with server certificate
4. with encryption

Ans : kindly reply, if you get correct answer.

Q2.You have on premise sql server that has a user database name HR_Applications you configured column based cell level encryption on the database.
You need to backup the keys that was used to perform the encryption. Your solution must maximize the security.. What should you do?
Ans
1. Backup the user database
2. Backup the key to an NTFS directory that is protected by access control list.
3. BAckup the model database
4.Store the key in the system registry key.

Ans:- : kindly reply, if you get correct answer.