Question 51 of 963

Implement a secure environment →hardMultiple ChoiceObjective-mapped

Stored Procedure Permissions with MFA and Audit in Azure SQL | DP-300

This DP-300 practice question tests your understanding of implement a secure environment. This is a configuration task: choose the command set that satisfies every stated requirement. Small differences — like 'secret' vs 'password' or 'transport input ssh' vs 'all' — change whether the answer is correct. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.

You are a database administrator for a financial services company that uses Azure SQL Database for a critical trading application. The application connects using a service principal (Microsoft Entra application) and executes stored procedures. You need to implement the following security requirements:

1. All connections must use Microsoft Entra authentication with MFA enforced for the service principal. 2. The application should only be able to execute specific stored procedures (usp_Trade, usp_GetQuote) and no other operations. 3. All data at rest must be encrypted using customer-managed keys stored in Azure Key Vault. 4. Auditing must capture all failed login attempts and all changes to the database schema. 5. The database must be protected against SQL injection attacks from the application layer.

You have already configured Microsoft Entra authentication and enabled TDE with customer-managed key in Azure Key Vault. Which additional steps should you take to meet all remaining requirements?

A
Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.
All requirements met.
B
Create a database role that only has EXECUTE permission on the required stored procedures. Configure dynamic data masking on sensitive columns. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.
Why wrong: Missing MFA enforcement and SQL injection prevention.
C
Create a contained database user for the service principal with EXECUTE permission. Enable Always Encrypted for sensitive columns. Configure audit for FAILED_DATABASE_AUTHENTICATION_GROUP only.
Why wrong: Always Encrypted does not prevent SQL injection; audit incomplete.
D
Create a database role with EXECUTE on the stored procedures. Implement row-level security to restrict data access. Use Microsoft Defender for SQL to detect SQL injection attempts.
Why wrong: Missing MFA enforcement and specific audit requirements.

Full breakdown with real-world context →

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.

Option A is correct because it addresses all remaining requirements: (1) A database role with EXECUTE permission on the required stored procedures restricts the service principal to only executing those procedures. (2) A Conditional Access policy enforcing MFA for the service principal meets the MFA requirement. (3) Audit enabled for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP captures failed logins and schema changes. (4) Using parameterized queries prevents SQL injection. Option B is incorrect because dynamic data masking does not prevent SQL injection and does not enforce MFA. Option C is incorrect because a contained database user with EXECUTE permission does not enforce MFA, and Always Encrypted does not prevent SQL injection. Option D is incorrect because row-level security restricts data access but does not prevent SQL injection, and Microsoft Defender for SQL only detects but does not prevent SQL injection; also, no MFA enforcement is mentioned.

Key principle: Count usable hosts — not total addresses — and remember that the network and broadcast addresses are not available to hosts in standard IPv4 subnets.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✓
Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.
Why this is correct
All requirements met.
Related concept
CIDR notation defines the prefix length.
✗
Create a database role that only has EXECUTE permission on the required stored procedures. Configure dynamic data masking on sensitive columns. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.
Why it's wrong here
Missing MFA enforcement and SQL injection prevention.
✗
Create a contained database user for the service principal with EXECUTE permission. Enable Always Encrypted for sensitive columns. Configure audit for FAILED_DATABASE_AUTHENTICATION_GROUP only.
Why it's wrong here
Always Encrypted does not prevent SQL injection; audit incomplete.
✗
Create a database role with EXECUTE on the stored procedures. Implement row-level security to restrict data access. Use Microsoft Defender for SQL to detect SQL injection attempts.
Why it's wrong here
Missing MFA enforcement and specific audit requirements.

Common exam traps

Common exam trap: usable hosts are not the same as total addresses

Subnetting questions often tempt you into counting all addresses. In normal IPv4 subnets, the network and broadcast addresses are not usable host addresses.

Detailed technical explanation

How to think about this question

Subnetting questions test whether you can identify the network, broadcast address, usable range, mask and correct subnet. Slow down enough to calculate the block size correctly.

KKey Concepts to Remember

CIDR notation defines the prefix length.
Block size helps identify subnet boundaries.
Network and broadcast addresses are not usable hosts in normal IPv4 subnets.
The required host count determines the smallest suitable subnet.

TExam Day Tips

Write the block size before choosing the subnet.
Check whether the question asks for hosts, subnets or a specific address range.
Do not confuse /24, /25, /26 and /27 host counts.

Key takeaway

Count usable hosts — not total addresses — and remember that the network and broadcast addresses are not available to hosts in standard IPv4 subnets.

Real-world example

How this comes up in practice

A media company stores terabytes of video archives that are accessed once a year for audit purposes. Moving these objects to a cold storage tier (Azure Archive, S3 Glacier, or Google Nearline) costs a fraction of hot storage. Questions like this test whether you understand storage tiers, access frequency tradeoffs, and retrieval latency requirements.

Quick reference

OSI Model Reference

Layer	Name	PDU	Key Protocols / Devices
7	Application	Data	HTTP, HTTPS, DNS, SMTP, FTP, SSH
6	Presentation	Data	TLS / SSL, JPEG, ASCII encoding
5	Session	Data	NetBIOS, RPC, SIP
4	Transport	Segment / Datagram	TCP, UDP
3	Network	Packet	IP, ICMP, OSPF — Routers
2	Data Link	Frame	Ethernet, Wi-Fi, PPP — Switches, Bridges
1	Physical	Bits	Cables, NICs, Hubs, Repeaters

What to study next

Got this wrong? Here's your next step.

Review block sizes, usable host formulas (2^n − 2), and how to find network and broadcast addresses for /24 through /30. Then practise related DP-300 subnetting questions on CIDR, address ranges, and subnet selection.

Related DP-300 practice-question pages

Use these pages to review the topic behind this question. This is how one missed question becomes focused revision.

Plan and configure a high availability and disaster recovery environment practice questions

Practise DP-300 questions linked to Plan and configure a high availability and disaster recovery environment.

Plan and implement data platform resources practice questions

Practise DP-300 questions linked to Plan and implement data platform resources.

Monitor, configure, and optimize database resources practice questions

Practise DP-300 questions linked to Monitor, configure, and optimize database resources.

Configure and manage automation of tasks practice questions

Practise DP-300 questions linked to Configure and manage automation of tasks.

Plan and configure high availability and disaster recovery practice questions

Practise DP-300 questions linked to Plan and configure high availability and disaster recovery.

Implement a secure environment practice questions

Practise DP-300 questions linked to Implement a secure environment.

DP-300 fundamentals practice questions

Practise DP-300 questions linked to DP-300 fundamentals.

DP-300 scenario practice questions

Practise DP-300 questions linked to DP-300 scenario.

DP-300 troubleshooting practice questions

Practise DP-300 questions linked to DP-300 troubleshooting.

Practice this exam

Start a free DP-300 practice session

Short sessions build daily habit. Longer sessions build exam-day stamina. Try a timed session to simulate real conditions.

10 questions 20 questions 30 questions 50 questions Timed 30

DP-300 practice-test guide →Study guide →Browse all practice tests

FAQ

Questions learners often ask

What does this DP-300 question test?

Implement a secure environment — This question tests Implement a secure environment — CIDR notation defines the prefix length..

What is the correct answer to this question?

The correct answer is: Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries. — Option A is correct because it addresses all remaining requirements: (1) A database role with EXECUTE permission on the required stored procedures restricts the service principal to only executing those procedures. (2) A Conditional Access policy enforcing MFA for the service principal meets the MFA requirement. (3) Audit enabled for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP captures failed logins and schema changes. (4) Using parameterized queries prevents SQL injection. Option B is incorrect because dynamic data masking does not prevent SQL injection and does not enforce MFA. Option C is incorrect because a contained database user with EXECUTE permission does not enforce MFA, and Always Encrypted does not prevent SQL injection. Option D is incorrect because row-level security restricts data access but does not prevent SQL injection, and Microsoft Defender for SQL only detects but does not prevent SQL injection; also, no MFA enforcement is mentioned.

What should I do if I get this DP-300 question wrong?

What is the key concept behind this question?

CIDR notation defines the prefix length.

About these practice questions

Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →

How Courseiva writes practice questions · Editorial policy

Keep practising

Question Discussion

Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.

Loading comments…

This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.