Business Intelligence and DashBoard report for Sharepoint and Project 2013

Posted on 2013-06-27
Medium Priority
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:


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.


Question by:totoroha
  • 4
  • 2
LVL 12

Expert Comment

by:Dr. Thomas Henkelmann
ID: 39283694

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?



Author Comment

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.


Author Comment

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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 12

Accepted Solution

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?


Author Comment

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

Author Comment

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

i understand this problem now. please delete it.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
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…
Viewers will learn how the fundamental information of how to create a table.

597 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