Can’t connect to SQL Server Analysis Services 2014 with Excel 2013

  • Article

Recently I installed Excel 2013 with SP1 on a clean Windows 8.1 machine and tried to connect to an Analysis Services cube on SQL Server 2014. In Excel I selected Data > Get External Data > Other Data Sources > Analysis Services, entered the service name and clicked Next. Instead of being able to select the cube I got this:

Unable to connect to data source. Reason: Unable to locate database server. Verify that the database server name you entered is correct, or contact the database administrator for help.

Looking in the event log on the client, that’s all that was there as well. Looking on the server yielded no useful information. I installed SQL Server Management Studio and connected to the cube from the client and it worked fine. Going back into Excel I tried to connect to the database engine (Other Data Sources > SQL Server) and was able to connect just fine (both the database and analysis services were on the same server). Obviously the database was there so what’s going on?

In a word, SQL Server 2014.

It appears that Excel does not ship with the SQL Server Analysis Services 2014 ADO provider so Excel cannot access that type of data source. Once I got to the source of the problem, the solution was easy. If you’re in this predicament, go to this site:

https://www.microsoft.com/en-us/download/details.aspx?id=42295

Click Download and select the following three items:

  • SQL_AS_ADOMD.msi
  • SQL_AS_AMO.msi
  • SQL_AS_OLEDB.msi

Install all three items, re-launch Excel 2013 and voila!

For reference this is the article that I finally found that led me to the right answer: https://msdn.microsoft.com/en-us/library/dn141152.aspx