nerdexam
Microsoft

70-473 · Question #76

Your company has a data warehouse that contains a database named DB1. You plan to implement data encryption for DB1. You need to configure DB1 to meet the following requirements: - Prevent the system

The correct answer is B. Back up DB1 by using VSS snapshots. C. Implement the Always Encrypted feature.. To protect Personally Identifiable Information (PII) from system administrators and ensure comprehensive backup and recovery, implement Always Encrypted and utilize VSS snapshots for consistent backups.

Design and implement data security

Question

Your company has a data warehouse that contains a database named DB1. You plan to implement data encryption for DB1. You need to configure DB1 to meet the following requirements: - Prevent the system administrators of App1 from reading the Personally Identifiable Information (PII) stored in DB1. - Ensue that all backups are Stored in an on-premises local drive for three days. - Ensure that all backups are stored in a remote location for 10 days. - Encrypt all backups. All solution must support the ODBC Driver for Microsoft SQL Server 2012. Which two solutions should you implement for DB1? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Options

  • AEncrypt within the application tier.
  • BBack up DB1 by using VSS snapshots.
  • CImplement the Always Encrypted feature.
  • DBack up DB1 to a disk and a URL by using Mirror TO.
  • EEncrypt within DB1 by using EncryptBypassword.

How the community answered

(47 responses)
  • A
    17% (8)
  • B
    68% (32)
  • D
    4% (2)
  • E
    11% (5)

Why each option

To protect Personally Identifiable Information (PII) from system administrators and ensure comprehensive backup and recovery, implement Always Encrypted and utilize VSS snapshots for consistent backups.

AEncrypt within the application tier.

Encrypting within the application tier is a generic concept; Always Encrypted (option C) is the specific SQL Server feature that implements this securely for PII.

BBack up DB1 by using VSS snapshots.Correct

VSS snapshots enable consistent point-in-time backups of the database files, which can be stored on-premises and subsequently managed for remote storage and retention requirements.

CImplement the Always Encrypted feature.Correct

Always Encrypted provides client-side encryption, ensuring that sensitive data is encrypted before it ever reaches the database server, thus preventing administrators from accessing it in plaintext and ensuring encrypted backups.

DBack up DB1 to a disk and a URL by using Mirror TO.

`MIRROR TO` is used for log shipping or creating redundant backup sets, not for simultaneous backup to disk and URL in SQL Server 2012 in the context implied, and it's not a primary backup mechanism for consistency like VSS.

EEncrypt within DB1 by using EncryptBypassword.

`EncryptBypassword` is not a valid SQL Server feature for database encryption; Transparent Data Encryption (TDE) is available but does not prevent sysadmins from reading data if they have control over the server keys.

Concept tested: SQL Server data encryption and backup strategies

Source: https://learn.microsoft.com/sql/relational-databases/security/always-encrypted-database-engine

Topics

#Data encryption#Always Encrypted#SQL Server backup#VSS snapshots

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-473 Practice