Improve company productivity with a Business Account.Sign Up

x
?
Solved

SSRS Report In SharePoint

Posted on 2011-03-23
6
Medium Priority
?
788 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

595 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