Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Business Intelligence and DashBoard report for Sharepoint and Project 2013

Posted on 2013-06-27
12
Medium Priority
?
854 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 12

Accepted Solution

by:
Dr. Thomas Henkelmann earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

650 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