Configure data access and auditing || a - ii

Qsn) what is always encrypted?

Ans :

Always Encrypted (AE) is a new feature in SQL Server 2016 that allows you encrypt both data at rest and data in flight.

most important capability is its ability to secure the data with your database outside of the database engine in the client application.

AE was designed so that encryption and decryption of the data happens

transparently at the driver level, which minimizes the changes that have to be made to existing applications.

At a high level the AE architecture works as below :

The client application issues a parameterized query. It uses the new Column Encryption Setting=Enabled; option in the connection string.

The enhanced ADO.NET driver interrogates the database engine using the

[sp_describe_parameter_encryption] system stored procedure to determine which parameters target encrypted columns. For each parameter that will require encrypting the driver retrieves the encryption algorithm and other information that will be used during the encryption phase.

The driver uses the Column Master Key (CMK) to encrypt the parameter values before sending the ciphertext to the database engine.

The database engine retrieves the result set, attaching the appropriate encryption metadata to any encrypted columns, and sends it back to the client application. The data is encrypted both at rest within the database and in flight from the database engine to the client application.

The client application’s driver decrypts any encrypted columns in the result set and returns the plaintext values to the application.

Qsn) AE supports the Which types of encryption?

Ans :

   1) Deterministic

    2) Randomized

Deterministic

   Deterministic encryption uses a method that always generates the same ciphertext for any given plaintext value.

   It allows for the transparent retrieval of data through equality comparisons. Point lookups, equality joins, grouping and indexing are all supported through deterministic encryption.

   With deterministic encryption a BINARY2 collation, such as Latin1_General_BIN2, must be used for character columns.

   Users might be able to guess encrypted columns values for columns with a small domain of values, such as an example of the[Gender] or [State] fields.

Randomized

   With randomized encryption, different ciphertext will be generated for the same plaintext.

   This makes randomized encryption much more secure than deterministic encryption.

   Effectively no search/comparison operations are allowed. Use randomized encryption for columns that you want to retrieve.