THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Analysis Services Data Connections and SharePoint - One Solution to "Connection Cannot Be Made"

Recently I came across a problem with an Analysis Services data connection when I was trying to create a status indicator in SharePoint 2010 (formerly known as a key performance indicator in SharePoint 2007). I had successfully created the data connection in Excel, verified that I could create a PivotTable with it, and then saved the connection to Excel Services as an Office Data Connection (ODC) file. However, when I tried to use the ODC to create a status indicator, the following error displayed:

"A connection cannot be made. Ensure that the server is running."

Well, I knew that the server was running, because I had just created a PivotTable with the same ODC. Then I tried searching the Web, but couldn't find anything useful (which prompted me to write this post!). In this scenario, I am running all servers on the same machine for demonstration purposes, so I knew it wasn't a double-hop problem. I had read that enabling SQL Browser would help, or using the IP address rather than the server name could be solutions also, but neither of those recommendations helped.

I tried different connections that I had created over time and several had the same problem - connection cannot be made. After fussing around with different options, I finally went back to study my connection strings and saw my problem:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008R2;Data Source=.;Location=.;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

When I removed Location=.; from the string, and exported the new connection to SharePoint, the connection worked just fine! In further testing, I found that I put the server name, rather than the . placeholder to represent localhost, the connection also worked.

Now the mystery is how I got that property into the connection string in the first place. I can't reproduce it by using the Data Connection Wizard in Excel, but that's generally the only way I produce an ODC file. Regardless of how it came to be in my connection string, I now know that the Location property is not required for the ODC file to work, or must reference the actual server name if it is included.

Published Sunday, June 27, 2010 12:56 PM by smisner
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment

Privacy Statement