PL-300 Prepare the data Practice Question
You are developing a Power BI semantic model that must combine data from an on-premises SQL Server database and a SharePoint Online list. The organization requires that credentials for the on-premises data source be stored securely and not shared with users. Which data connectivity approach should you use?
⚠ Common exam trap
The trap is that candidates often choose DirectQuery with SSO (Option B) thinking it avoids credential sharing, but SSO relies on each user's own credentials and requires granting users direct database access, which violates the requirement that credentials be stored securely and not shared with users. The correct approach is to use a gateway with centrally stored credentials.
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
✓
Use an on-premises data gateway and store the SQL Server credentials in the gateway data source settings
Using an on-premises data gateway with credentials stored in the gateway data source settings allows the Power BI service to securely connect to the on-premises SQL Server without exposing credentials to users. The gateway acts as a secure bridge, storing the SQL Server credentials encrypted in the gateway configuration, which satisfies the requirement that credentials not be shared with users.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use an on-premises data gateway and store the SQL Server credentials in the gateway data source settings
Why this is correct
Using an on-premises data gateway is the recommended approach because it securely bridges Power BI service to your on-premises SQL Server. Storing the SQL Server credentials in the gateway's data source settings keeps them encrypted and accessible only to gateway administrators, enabling scheduled refreshes without requiring individual users to have database permissions. This also supports row-level security mapping when needed, making it a secure and scalable solution.
- ✗
Use DirectQuery with single sign-on for the SQL Server database
Why it's wrong here
DirectQuery with single sign-on (SSO) forwards the signed-in user's identity to the SQL Server database, so each user's permissions are evaluated directly against the source. While this is ideal for row-level security, it fails when users do not have direct database access—exactly the scenario here—because they will receive authentication errors. Additionally, DirectQuery sends live queries to the database, which can cause performance overhead and is not suited for large or heavily loaded data models.
- ✗
Export the SQL Server data to an Excel file stored in SharePoint and then import from SharePoint
Why it's wrong here
Exporting SQL Server data to an Excel file stored in SharePoint and importing it into Power BI creates a static snapshot, leading to data staleness and requiring manual or automated export processes to keep it current. It also introduces a security risk because the Excel file may have its own access controls that don't match the original database, potentially exposing sensitive data. Furthermore, this ceremony bypasses Power BI's native data connectivity and governance features, so it is not a recommended practice.
- ✗
Import data from SQL Server using Windows authentication embedded in the data source settings
Why it's wrong here
Importing data with Windows authentication embedded directly in the data source settings means the stored credentials are visible to anyone with access to the dataset's connection settings in the Power BI service. This exposure is a serious security vulnerability, especially for shared datasets, and it violates the principle of least privilege. Moreover, if the embedded Windows account lacks the necessary permissions or the password expires, refresh failures occur, making this approach brittle and unsafe.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 524-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-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 PL-300 exam.