mediumMultiple ChoiceObjective-mapped
DP-203 Practice Question: A data engineer at a healthcare analytics company
You are a data engineer at a healthcare analytics company. The company uses Azure Data Factory (ADF) to orchestrate data pipelines that ingest patient data from on-premises SQL Server databases into Azure Synapse Analytics. Recently, the pipeline has been failing intermittently with the following error: 'Failure happened on 'Sink' side. ErrorCode=SqlFailedToConnect, Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=Cannot connect to SQL Server Database. The TCP connection to the host <server_name>, port 1433 has failed. Error: 'Connection timed out.'.' The on-premises SQL Server is behind a corporate firewall. The ADF self-hosted integration runtime (SHIR) is installed on a VM inside the corporate network. You have verified that the SHIR is running and that the SQL Server is accessible from the SHIR VM using SQL Server Management Studio (SSMS). The error occurs sporadically, not consistently. What is the most likely cause of the intermittent connection timeout?
⚠ Common exam trap
Candidates often assume the error is due to resource exhaustion (CPU, memory, or data skew) because those are common causes of intermittent failures, but the specific 'Connection timed out' error points to a network-layer issue, not a server-side performance bottleneck.
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
✓
The corporate firewall or network device is closing idle TCP connections to the SQL Server database.
The intermittent nature of the timeout, combined with the fact that the SHIR VM can connect to SQL Server via SSMS, strongly suggests that the corporate firewall or a network intermediary (such as a load balancer or NAT device) is closing idle TCP connections. ADF pipelines may hold connections open between activities or during long-running data transfers, and if no keep-alive packets are sent within the firewall's idle timeout window (commonly 4–30 minutes), the firewall drops the TCP session. When ADF attempts to reuse that connection, it receives a 'Connection timed out' error because the socket is no longer valid.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The data being transferred is skewed, causing the sink to be overwhelmed.
Why it's wrong here
Data skew affects processing performance, not the ability to establish a TCP connection to the database.
- ✓
The corporate firewall or network device is closing idle TCP connections to the SQL Server database.
Why this is correct
Firewalls often drop idle connections after a timeout period. When the pipeline uses a connection from the pool that has been idle, the connection is no longer valid, causing a timeout. This explains the intermittent nature.
- ✗
The SQL Server database is experiencing high CPU utilization during the pipeline execution window.
Why it's wrong here
High CPU would cause slow queries or timeouts but would not produce a TCP connection timeout error; the error is about establishing a connection, not executing queries.
- ✗
The self-hosted integration runtime is running out of memory during peak loads.
Why it's wrong here
Memory exhaustion would typically result in 'out of memory' errors or slow performance, not TCP connection timeouts.
Visual reference
Go deeper
Related to this question
Learn chapter
Introduction to Azure Data Engineering
Key term
Azure Data Factory
Azure Data Factory is a cloud-based data integration service that lets you create, schedule, and orchestrate data pipelines to move and transform data from various sources to destinations.
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
About these practice questions
One of 760 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 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-203 exam.