Configure data access and auditing || a - i (Part -1)

Qsn) Explain encryption hierarchy and key management architecture.

Ans:

Layers of encryption are protected by preceding layers of encryption that can use asymmetric keys, certificates, and symmetric keys.

Extensible Key Management :

• SQL Server EKM enables the encryption keys that protect the database files to be stored outside of the SQL Server environment such as a smartcard, a USB device, and the EKM module of Hardware Security Module (HSM).

Service Master Key :

• SMK is generated automatically the first time it is needed to encrypt another key.

•  The SMK can only be opened by the Windows service account that created it, or by a principal that knows the service account name and its password.

Database Master Key :

• The Database Master Key (DMK) is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database

Asymmetric Key :

• An asymmetric key consists of a private and corresponding public key.

Symmetric Key :

• A symmetric key is a single key that uses encryption. 

Certificate :

• Certificates are a digitally signed security object that contain a public (and optionally a private) key for SQL Server, which can generate certificates.

•  You can also use externally generated certificates. and just like with asymmetric keys, certificates can be used in asymmetric encryption.

* Keep in mind the following concepts

• For best performance, encrypt data using symmetric keys instead of certificates or asymmetric keys. 

• Database master keys are protected by the Service Master Key. The Service Master Key is created by SQL Server setup and is encrypted with the Windows Data Protection API (DPAPI). 

• Other encryption hierarchies stacking additional layers are possible. 

• An Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside of SQL Server. 

• Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM. 

• The Service Master Key and all Database Master Keys are symmetric keys. 

• Symmetric and asymmetric keys in the EKM can protect access to the symmetric and asymmetric keys stored in SQL Server. The dotted line associated with EKM indicates that keys in the EKM could replace the symmetric and asymmetric keys stored in SQL Server.

Qsn) What is Column Level Encryption?

Ans:

• The ability to encrypt data at the column level is a critical capability in any modern database engine 

• Column level Encryption has been introduced since, SQL Server 2005

Qsn) How you will implement column level Encryption ?

Ans:

Column level Encryption can be implemented using Symmetric Key or using Certificate

Permission Required:

• CONTROL permission on the database.

• CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.

• ALTER permission on the table.

• permission on the key and must not have been denied VIEW DEFINITION permission.

Example of Column level Encryption using symmetric key

Use [trans_DB]

GO

-- Create sample table

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

EmployeeName VARCHAR(300),

Position VARCHAR(100),

Salary VARBINARY(128)

);

GO

-- Create SMK

CREATE SYMMETRIC KEY SMK_Emp

WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'Pass123$';

GO

-- Open SMK

OPEN SYMMETRIC KEY SMK_Emp DECRYPTION BY PASSWORD = 'Pass123$';

-- Verify open keys

SELECT * FROM sys.openkeys;

-- Insert data

INSERT Employees VALUES (1, 'Marcus', 'CTO',ENCRYPTBYKEY(KEY_GUID('SMK_Emp'), '$100000'));

INSERT Employees VALUES (2, 'Christopher', 'CIO',

ENCRYPTBYKEY(KEY_GUID('SMK_Emp'),'$200000'));

INSERT Employees VALUES (3, 'Isabelle', 'CEO',ENCRYPTBYKEY(KEY_GUID('SMK_Emp'),

'$300000'));

-- Query table with encrypted values

SELECT * FROM Employees; 

Output:

-- Query table with decrypted values

SELECT *, CONVERT(VARCHAR, DECRYPTBYKEY(Salary)) AS DecryptedSalary

FROM Employees;

Output :

-- Close SMK

CLOSE SYMMETRIC KEY SMK_Emp

-- Query table with decrypted values after key SMK is closed

SELECT *, CONVERT(VARCHAR, DECRYPTBYKEY(Salary)) AS DecryptedSalary

FROM Employees;

GO

Output:

-- Clever CTO updates their salary to match CEO's salary

UPDATE Employees

SET Salary = (SELECT Salary FROM Employees WHERE Position = 'CEO')

WHERE EmployeeName = 'Marcus';

-- Open SMK and query table with decrypted values

OPEN SYMMETRIC KEY SMK_Emp DECRYPTION BY PASSWORD = 'Pass123$';

SELECT *, CONVERT(VARCHAR, DECRYPTBYKEY(Salary)) AS DecryptedSalary

FROM Employees;

Output:

A major disadvantage of encrypting data using a symmetric key protected by a password is that the password needs to be embedded somewhere, which represents a security risk.

Ref : https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15