Solved

Business Intelligence and DashBoard report for Sharepoint and Project 2013

Posted on 2013-06-27
12
824 Views
Last Modified: 2014-08-18
Hello everyone,

I will state my situation with Sharepoint and Project Server 2013 in detail so that everyone that has the idea or opinion about fixing it can understand and help me.

I have a Windows Server 2008 R2 installed on Server 1. I installed SharePoint 2013 and project server 2013 and SQL 2012 on Server 2.

The problem that I am confronting right now is the Reports matter. I have 20 members and 5 projects, so 4 members for each project.

Every time we try to create a report, the error is:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

when I click "OK", it opens a new windows as Capture 1.2 that I attached with this post.

I tried to find the solution over the internet, on book such as "Implementing and Administering Microsoft Project Server 2013 for implementers and administrators" by Gary L.Chefetz & Bill Raymond.

I tried to use this instruction but that didn't help much:

http://whitepages.unlimitedviz.com/2012/09/using-per-user-identity-with-reporting-services-and-sharepoint/

The reason for this issue that after so much test-fail solution is that if your computer is domain computer and your user is added to SQL server, you can run the report pretty well.

But in my case, i couldn't add 20 public computers or more into my domain because that will exposed security risks for my system.

I try to look for some solution like proxy or an implementation of gateway that allow the connections from outside to my Project and SharePoint Server.

This issue has been the pain in my neck for almost a month now, i would be grateful for any information or solution that our great members can provide.

Sincerely,

Peter
capture-1.1.JPG
capture-1.2.JPG
0
Comment
Question by:totoroha
[X]
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
12 Comments
 
LVL 12

Expert Comment

by:Dr. Thomas Henkelmann
ID: 39283694
Hi,

a few questions and remarks to get down to the real issue, OK?
- Are you using the built-in Excel reports when you "create a new report"? Then you NEED to add the user account for the person designing the report as db_datareader to SQL Server
- Or another reporting tool?
- Do you want to make these reports (Excel) available to your team members via Excel Services? Have you setup and configures Secured Store Service (SSS) for that?

Regards

Thomas
0
 

Author Comment

by:totoroha
ID: 39310992
Hi Thomas,

Sorry for my late response. I have a business trip abroad so I couldn't focus on this matter. I will double check my system and reply right away.

Thanks.
0
 

Author Comment

by:totoroha
ID: 39346326
Wow, I don't know why but my post wasn't show up here so I will repeat it again.
I use the built-in excel report of sharepoint 2013 to create the report for my project. I added the BI-report-author group into the SQL Server 2012.
I configured and enabled the SSS already. But the problem still exist. So this is the solution from my discussion with Microsoft technician.

We create an account in SQL Server 2012, and modify the connection string in the ODC file of the Database connection of Sharepoint 2013.
So from now, every student can use the report and open it in Excel. When the excel program open the report file, it will use the information in the ODC file to connect to the SQL database and generates the report. So in this case, the credentials that exist in the ODC file will be display in plain text, and this solution brought another problem to myself.

So if anyone has a better solution to solve this double-hop problem, please sharepoint it me and i really appreciate for this.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Accepted Solution

by:
Dr. Thomas Henkelmann earned 500 total points
ID: 39429430
Ok, maybe this helps.

To CREATE the report in Excel you (or any other user) needs to be able to access the SQL Server directly. Hiowever the usual way of READING the report is through Excel Services where the reports initially open. After being rendered in Excel Services users can refresh the data in the ribbon of the browser. As this also requires access to the database but you don't want everybody to have a user account there, the Excel Services connection can be set to use SSS while you edit the report in Excel.
This is what I found most confusing at the beginning: in EXCEL you must be able to connect to SQL directly with your user account AND can set the option for EXCEL SERVICES to use SSS. EXCEL itself can NOT use SSS to connect to the database. In general the concept makes a lot of sense as admins who build the report can be authorized to read at database level and the majority of users is just using Excel Services with an "anonymous" account maintained in SSS.

Makes sense?

Thomas
0
 

Author Comment

by:totoroha
ID: 40258848
I understand this matter now. Please close this topic.
0
 

Author Comment

by:totoroha
ID: 40258871
I've requested that this question be deleted for the following reason:

i understand this problem now. please delete it.
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 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