SSRS Report In SharePoint

Posted on 2011-03-23
Last Modified: 2012-05-11
Hi I have created a Report Using Report Builder 3, i have chosen to use a connection embedded in my report i have created the report on the reporting server which also has sharepoint installed on the same machine, and i am logged in as administrator.

When logged in as administrator i can click on the document libary and view the report, However whenever a domain user trys to look at the report they get the following error

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Please Help
Question by:pepps11976
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Expert Comment

ID: 35199485
This sounds like a double hop issue.  You can view the report on the server, because its a single hop.  You will need to provide credentials in the Data Source within SharePoint.

Author Comment

ID: 35199581
The only way that i ahve managed to get this to work is by editing the datasiurce for the report to use the stored credentials of the SA administrator, does that shed anymore light on the problem.

Please Help

Expert Comment

ID: 35200129
Yes.  The admin has privileges in SQL.  Integrated auth does not pass to SQL, because of the double hop.  You will want to create accounts with limited access to SQL data and use those in your connection.
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations


Author Comment

ID: 35200143
Could you give me an idear on how to do this i am very new to SQL

Expert Comment

ID: 35212038
Go to the data Source in the SharePoint library.  Hover over the Name until you see the drop down arrow.  In the drop down, select Edit Data Source Definition.  

Change the connection string to something like this "Data Source=DBServer\Server01;Initial Catalog=Database;" - do not include quotes, datasource should be the server name or instance name, catalog is the database name.

Select stored credentials.
Add the user name "sa" - no quotes, use a more secure account though.  
Add the password "pwd" - no quotes

Test.  If it works you are good to go (after you save the changes).  Now you will need to ensure the account has read access (in SQL Server) to all items the report(s) will be accessing.  This is why I recommend stored procedures.

Accepted Solution

AndrewSkoraro earned 500 total points
ID: 35212049
One other thing I did not mention.  The security for the report is not maintained in SharePoint. Any user that can view the report in SharePoint can view the data in SQL server.  So in short, you need to secure your items in SharePoint. This is apposed to pass through authentication (which you don't have) where a user may be able to see the report, but when they open it, they don't have privileges in SQL to view the data.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. (htt…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question