nerdexam
Microsoft

70-465 · Question #108

You work as a Database Administrator (DBA) at ABC.com. You are in the process of deploying new servers running SQL Server 2012. You need to deploy a SQL Server 2012 server to host databases used to…

The correct answer is B. Transparent Data Encryption (TDE). This question tests knowledge of SQL Server encryption features that protect database files when moved between servers. Transparent Data Encryption (TDE) encrypts the database files at rest, ensuring they remain encrypted if physically moved.

Submitted by wei.xz· Mar 5, 2026Design database security solutions

Question

You work as a Database Administrator (DBA) at ABC.com. You are in the process of deploying new servers running SQL Server 2012. You need to deploy a SQL Server 2012 server to host databases used to host databases used by Research and Development department. The databases used by the Research and Development department will store sensitive data. A company security policy states that if Research and Development department database files are moved to another server, the files must be encrypted. Which of the following solutions would meet the encryption requirement?

Options

  • AEncrypting File System (EFS).
  • BTransparent Data Encryption (TDE).
  • CWindows Bitlocker Drive Encryption.
  • DSecure Sockets Layer (SSL)

How the community answered

(31 responses)
  • A
    3% (1)
  • B
    81% (25)
  • C
    10% (3)
  • D
    6% (2)

Why each option

This question tests knowledge of SQL Server encryption features that protect database files when moved between servers. Transparent Data Encryption (TDE) encrypts the database files at rest, ensuring they remain encrypted if physically moved.

AEncrypting File System (EFS).

Encrypting File System (EFS) encrypts files at the Windows NTFS file system level and is tied to the Windows user account, not the SQL Server instance, meaning it does not provide SQL Server-aware encryption and can be bypassed when SQL Server reads the files during normal operation.

BTransparent Data Encryption (TDE).Correct

Transparent Data Encryption (TDE) encrypts SQL Server database files (.mdf, .ndf, and .ldf) at the storage level using a Database Encryption Key protected by a certificate stored in the master database. Because the encryption is tied to the server's certificate hierarchy, if the database files are moved to another server without the corresponding certificate and private key, the data remains encrypted and inaccessible, directly satisfying the security policy requirement.

CWindows Bitlocker Drive Encryption.

Windows BitLocker Drive Encryption encrypts an entire drive volume and is tied to the hardware TPM chip or a specific machine, so once the drive is mounted on another server, BitLocker may be unlocked independently of SQL Server, not guaranteeing the database files themselves remain encrypted.

DSecure Sockets Layer (SSL)

Secure Sockets Layer (SSL) encrypts data in transit over the network between clients and the SQL Server instance, but does not encrypt data at rest on disk, so it provides no protection if the database files are physically moved to another server.

Concept tested: SQL Server Transparent Data Encryption for data at rest

Source: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

Topics

#Transparent Data Encryption (TDE)#Data encryption#Database security

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice