Solved

Business Intelligence and DashBoard report for Sharepoint and Project 2013

Posted on 2013-06-27
12
785 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
  • 4
  • 2
12 Comments
 
LVL 12

Expert Comment

by:thausla
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 12

Accepted Solution

by:
thausla 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

707 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

14 Experts available now in Live!

Get 1:1 Help Now