Exam 70-765 All Questions

View all questions & answers for the 70-765 exam

Exam 70-765 topic 2 question 7 discussion

Actual exam question from Microsoft's 70-765

Question #: 7
Topic #: 2

Note: This questions is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You manage on-premises and Microsoft Azure SQL Database instances for a company. Your environment must support the Microsoft SQL Server 2012 ODBS driver.
You need to encrypt only specific columns in the database.
What should you implement?

A. transport-level encryption
B. cell-level encryption
C. Transparent Data Encryption
D. Always Encrypted
E. Encrypting File System
F. BitLocker
G. dynamic data masking

Show Suggested Answer

Suggested Answer: D 🗳️
To encrypt columns you can configure Always Encrypted.
SQL Server Management Studio (SSMS) provides a wizard that helps you easily configure Always Encrypted by setting up the column master key, column encryption key, and encrypted columns for you.
Always Encrypted allows client applications to encrypt sensitive data and never reveal the data or the encryption keys to SQL Server or Azure SQL Database. An
Always Encrypted enabled driver, such as the ODBC Driver 13.1 for SQL Server, achieves this by transparently encrypting and decrypting sensitive data in the client application.
Note: The ODBC driver automatically determines which query parameters correspond to sensitive database columns (protected using Always Encrypted), and encrypts the values of those parameters before passing the data to SQL Server or Azure SQL Database. Similarly, the driver transparently decrypts data retrieved from encrypted database columns in query results.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault#encrypt-columns-configure-always-encrypted https://msdn.microsoft.com/en-us/library/mt637351(v=sql.110).aspx

by t at March 21, 2019, 7:49 p.m.

Comments

Submit Cancel

sion111

5 years ago

I also think that due to using "Azure SQL DB" and proper odbc driver, Always Encrypted is the correct answer. Driver encrypts or decrypts data before sending it to database engine. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-ver15

upvoted 4 times

KC

4 years, 11 months ago

I think I agree: Microsoft ODBC Driver 13.1 for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, Analytics Platform System, Azure SQL Database and Azure SQL Data Warehouse. Microsoft ODBC Driver 13 for SQL Server should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2016 features.https://www.microsoft.com/en-us/download/details.aspx?id=53339

upvoted 2 times

Iva10

4 years, 9 months ago

If you have ODBC Driver 13.1, you can connect to SQL 2012, but this not mean that you can use Always encrypted feature, because this is new and it requires sever side implementation also which is available only on SQL server 2016 onward, so I think that the answer is cell level for SQL 2012-.

upvoted 1 times

...

welshdba

5 years, 1 month ago

I'm slightly confused. The question states your using an "Azure SQL Database" which is essentially SQL server 2016. So Always Encrypted is a valid option - it's whether you can use it to support a previous version of SQL server?

upvoted 1 times

Hoglet

4 years, 4 months ago

You have to support a SQL2012 ODBC driver. Always Encrypted is done by the driver, so wouldn't work with SQL 2012 ODBC driver.

upvoted 1 times

...

DudeHere

5 years, 1 month ago

due to the 2012 requirement, I would choose cell level. Available in all editions of SQL Server, cell-level encryption can be enabled on columns that contain sensitive data. The data is encrypted on disk and remains encrypted in memory until the DECRYPTBYKEY function is used to decrypt it. Therefore, although the SQL data is encrypted, it is not secure beyond simply using a function in the user context to decrypt it. Additionally, because a function is needed to decrypt the data, client applications must be modified to work with cell-level encryption. Always Encrypted is available in SQL Server 2016 and later, but only in Enterprise editions. Because of the extra client-side requirements, Always Encrypted is best suited to situations in which separation of data owners and managers is a primary requirement.

upvoted 2 times

...

Ugur

5 years, 6 months ago

It should be cell level encryption. Because always encryption is not a featured in 2012 version

upvoted 3 times

...

Varad

5 years, 6 months ago

"ALWAYS ENCRYPTED" is the correct answer. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

upvoted 4 times

Johnny303

4 years, 5 months ago

But that isn't available with the SQL 2012 ODBC Driver

upvoted 1 times

...

t

6 years, 2 months ago

wouldn't this be cell-level encryption if only encrypting specific columns in a database?

upvoted 1 times

Hoglet

4 years, 4 months ago

The correct answer is Cell-level encryption as Always Encrypted cannot be used in this case The requirement is to use the SQL 2012 ODBC driver, but AE was introduced SQL 2014. Always Encrypted is client side encryption so required a client driver 2014 or later.

upvoted 1 times

...