70-467 · Question #128
A group of report writers develop reports. The report writers currently use Report Builder 1.0 and report models. The report writers are not trained on SQL Server query writing. Their report…
The correct answer is D. Create one shared data source that uses integrated security. A shared data source using integrated security passes each report writer's Windows credentials to SQL Server, enforcing their existing database permissions automatically and preventing access to unauthorized data.
Question
A group of report writers develop reports. The report writers currently use Report Builder 1.0 and report models. The report writers are not trained on SQL Server query writing. Their report assignments come from various departments within the organization. The company is upgrading to SQL Server 2012 with SQL Server Reporting Services (SSRS) in native mode. After the upgrade, reports will retrieve data from a large new data warehouse that will reside on an instance of the SQL Server 2012 Database Engine. Reports for each department will retrieve data from only a subset of the data warehouse tables. You are designing the data access strategy. You have the following requirements:
- Ensure that report writers can create only reports that display data
which they have database permissions to view.
- Minimize the effort required to update connection strings for all
reports developed by the report writers.
- Minimize the number of fields visible in a specific report for report
writers who work for multiple departments. You need to design the data access strategy. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- ACreate report models by using SQL Server Data Tools (SSDT).
- BCreate one shared data source that uses integrated security.
- CCreate one shared data source that uses integrated security.
- DCreate one shared data source that uses integrated security.
How the community answered
(29 responses)- A3% (1)
- B10% (3)
- C3% (1)
- D83% (24)
Why each option
A shared data source using integrated security passes each report writer's Windows credentials to SQL Server, enforcing their existing database permissions automatically and preventing access to unauthorized data.
Report models in SSDT define a metadata layer that controls which tables and fields are exposed to report writers, but they do not enforce row- or table-level database security - a writer could still query unauthorized data if the data source connection is not secured with integrated credentials.
This choice is identical to choice D and represents a duplicate option in the question rather than a distinct technical alternative.
This choice is also identical to choices B and D and is a duplicate option, not a separate approach.
Integrated security on a shared data source causes SSRS to delegate the authenticated user's Windows identity to the SQL Server database engine instead of using a fixed service account. This means SQL Server enforces each report writer's existing database-level permissions on every query, so writers cannot retrieve data from tables they are not authorized to access. Using a single shared data source centralizes this configuration, reducing administrative overhead.
Concept tested: SSRS shared data source integrated security for data permissions
Source: https://learn.microsoft.com/en-us/sql/reporting-services/report-data/specify-credential-and-connection-information-for-report-data-sources
Topics
Community Discussion
No community discussion yet for this question.