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

Example of Column level Encryption using Certificate

USE [trans_DB];

GO

-- Create database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'GoodLuckWithExam!'

-- Create certificate

CREATE CERTIFICATE Cert_BAN

WITH SUBJECT = 'Bank Account Number';

GO

-- Create SMK

CREATE SYMMETRIC KEY Key_BAN

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE Cert_BAN;

GO

-- Create a column to store encrypted data

ALTER TABLE Purchasing.Suppliers

ADD EncryptedBankAccountNumber varbinary(128);

GO

-- Open the SMK to encrypt data

OPEN SYMMETRIC KEY Key_BAN

DECRYPTION BY CERTIFICATE Cert_BAN;

GO

-- Encrypt Bank Account Number

UPDATE Purchasing.Suppliers

SET EncryptedBankAccountNumber = EncryptByKey(Key_GUID('Key_BAN'),

BankAccountNumber);

GO

-- Close SMK

CLOSE SYMMETRIC KEY Key_BAN

GO

/*

Verify encryption was successful

*/

-- Query 1: Check encryption has worked

SELECT TOP 5 SupplierID, SupplierName, BankAccountNumber, EncryptedBankAccountNumber, CONVERT(NVARCHAR(50),  ecryptByKey(EncryptedBankAccountNumber)) AS DecryptedBankAccountNumber

FROM Purchasing.Suppliers

GO

-- Query 2: Open the SMK

OPEN SYMMETRIC KEY Key_BAN

DECRYPTION BY CERTIFICATE Cert_BAN;

GO

-- Query with decryption function

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM HumanResources.Employee;

-- Close SMK

CLOSE SYMMETRIC KEY Key_BAN;

GO

Qsn)  Drawback of Column level encrypted? Also suggest work around for same?

Ans :

• Indexes on encrypted columns are useless and consume needless resources in most cases. 

• help improve performance in such cases, such as creating a separate column and storing the hashed value of the sensitive column and incorporating that in your queries.

Qsn) How to find out there is encrypted data in any of the user databases. Is there an easy way to determine if this is the case?

Ans :

The easiest way to determine if there is encrypted data in a database is to get that information from whoever wrote the application. 

Sometimes this is through documentation and other times it's by contacting the development team or the vendor. 

In reality, this is the only way to be completely sure. However, barring this method, there are a few things you can look for which would suggest that you have encrypted data in a given database.

SQL Server Built-In Encryption Markers :

Query the correct catalog views, we should be able to determine if built-in encryption is being used

• Symmetric Keys:

     SELECT name, key_length, algorithm_desc, create_date, modify_date FROM sys.symmetric_keys;

• Asymmetric Keys:

SELECT name, algorithm_desc FROM sys.asymmetric_keys;

• Certificates:

       SELECT name, subject, start_date, expiry_date

        FROM sys.certificates

Qsn) How to check connection is encrypted?

Ans :

• You can query the sys.dm_exec_connections dynamic management view (DMV) to see if the connections to your SQL Server is encrypted or not. 
• If the value of encrypt_option is "TRUE" then your connection is encrypted.

SELECT session_id, encrypt_option FROM sys.dm_exec_connections

Qsn) How Check if SQL Server service has been restarted after the configuration changed?

Ans :

You can query the sys.dm_os_sys_info DMV to see when the SQL Server service has been started, just look for the sqlserver_start_time value.

SELECT sqlserver_start_time

FROM sys.dm_os_sys_info

Note :

sys.dm_os_sys_info Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.