Solved

SSRS Report In SharePoint

Posted on 2011-03-23
6
762 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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