Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSRS Report In SharePoint

Posted on 2011-03-23
6
Medium Priority
?
786 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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

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 your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

564 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