70-764 Administering a SQL Database Infrastructure

Question 1

What is one of the most important assets to an organization?

Answer 1

Data

Question 2

What 3 ways can you protect your organization’s data?

Answer 2

protect sensitive data through encryption, to control data access, and importantly to audit data access

Question 3

What 4 forms does data loss come in?

Answer 3

hardware failure, database corruption, malicious activity, and user error

Question 4

What is the is a critical capability in any modern database engine?

Answer 4

The ability to encrypt data at the column level

Question 5

When did Column-level encryption become available?

Answer 5

Column-level encryption has been supported since SQL Server 2005

Question 6

How are layers of encryption protected?

Answer 6

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

Question 7

Extensible Key Management

Answer 7

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). It also helps secure the SQL Server instance from database administrators because they
will not necessarily have access to the external EKM/HSM module.

Question 8

Service Master Key

Answer 8

The Service Master Key (SMK) is the root of the database engine’s encryption hierarchy and is generated automatically the first time it is needed to encrypt another key. By default, the SMK is encrypted using the Windows data protection API (DPAPI) at the operating system level, which uses the local machine 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.

Question 9

Database Master Key

Answer 9

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.
When created it is encrypted using AES_256 and a password you provide. Query the [sys].[symmetric_keys] catalog view to get information about the DMK.

Question 10

Asymmetric Key

Answer 10

An asymmetric key consists of a private and corresponding public key. Asymmetric encryption is computationally more expensive, but more secure than
symmetric encryption. You can use an asymmetric key to encrypt a symmetric key within a database.

Question 11

Symmetric Key

Answer 11

A symmetric key is a single key that uses encryption. Symmetric encryption is generally used over asymmetric encryption because it is faster and less computationally expensive.

Question 12

Certificate

Answer 12

Certificates are a digitally signed security object that contain a public (and ptionally a private) key for SQL Server, which can generate certificates. You can also
use externally generated certificates, and just like with asymmetric keys, certificates cane used in asymmetric encryption.

Question 13

Can encrypted data be compressed?

Answer 13

Encrypted data cannot be compressed, but compressed data can be encrypted. When using compression, you should compress data before encrypting it for optimal results.

Question 14

How does stronger encryption affect processor resources?

Answer 14

Stronger encryption algorithms consume more processor resources.

Question 15

How can the database engine take advantage of hardware acceleration?

Answer 15

Starting with SQL Server 2016 the database engine can take advantage of hardware acceleration, using Intel AES-NI, when performing encryption/decryption tasks.

Question 16

Which algorithms does SQL Server 2016 support?

Answer 16

Starting with SQL Server 2016 the only algorithms that are supported with database compatibility 130 or above are AES-128, AES_192, and AES_256.

Question 17

How are older encryption algorithms supported?

Answer 17

Older encryption algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, and DESX are only supported under a database compatibility level of 120 or lower.

Question 18

Why should you not use the older encryption algorithms?

Answer 18

You should not use these older, unsupported encryption algorithms because they are
fundamentally less secure.

Question 19

How should you encrypt a lot of data?

Answer 19

If you are encrypting a lot of data it is recommended that you encrypt the data using a symmetric key, and then encrypt the symmetric key with an asymmetric key.

Question 20

What happens to the indexes on a column once you encrypt the column?

Answer 20

For all intents and purposes, once you encrypt a column, indexes on that column typically become useless for searching. Consider removing the indexes. In some cases you can add a helper column to the table, such as in the example of the last 4 digits of a
credit card.

Question 21

Does the DBA still have control over the SQL Server environment after data has been encrypted?

Answer 21

The database administrator generally still has complete control over the SQL Server environment and consequently the ability to potentially view the encrypted data.

Question 22

In order to encrypt data what are some tasks you can perform?

Answer 22

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 column encryption
4. Encrypt the column using the Symmetric Key