Solved

SSRS Report In SharePoint

Posted on 2011-03-23
6
761 Views
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
0
Comment
Question by:pepps11976
  • 4
  • 2
6 Comments
 
LVL 3

Expert Comment

by:AndrewSkoraro
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.
0
 

Author Comment

by:pepps11976
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
0
 
LVL 3

Expert Comment

by:AndrewSkoraro
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:pepps11976
ID: 35200143
Could you give me an idear on how to do this i am very new to SQL
0
 
LVL 3

Expert Comment

by:AndrewSkoraro
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.
0
 
LVL 3

Accepted Solution

by:
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.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cross-referencing embedded datasets in SSRS Report Server 1 65
SQL RS 2014 - upload all RDL files to report server 7 48
SSRS 2012 URL 11 57
Grid querry results 41 74
After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now