none
Unable to connect to a data source using excel 2013 RRS feed

  • Question

  • We are trying to access a sql cube by creating a new connection using excel 2013.

    The error message received is "Unable to connect to data source.Unable to locate database server.Verify that the database server name you entered is correct,or contact the database administrator for help."

    We are using a 64 bit machine and tried everything possible including reinstalling ms-office. All other connections work but user is unable to create new connection

    The problem is related to using analytical services from microsoft excel 2013

    Sunday, December 1, 2013 11:09 PM

All replies

    • Edited by Sriindu Monday, December 2, 2013 7:33 AM
    Monday, December 2, 2013 7:32 AM
  • Check also your (local) Firewall settings if connections to SSAS are allowed (unblocked); see Configure the Windows Firewall to Allow Analysis Services Access

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, December 2, 2013 8:24 AM
  • Hi there

    This did not work , All other people in the office are able to connect to the same server using exactly the same technique. Only one person is not able to connect

    Tuesday, December 3, 2013 3:16 AM
  • Hi

    We have reinstalled excel 2013 a couple of times on this person's machine. All other people can connect except for him

    Tuesday, December 3, 2013 3:17 AM
  • Hi mathurabhishek,

    There are two likely principle causes for a failure to connect with an OLAP cube.

    • The file that Excel is connecting to has had its name change, or it's been moved. Open the Pivot Table wizard and make sure that the address for the OLAP file is correct. This includes making sure the file has the same name.
    • Loss of network connectivity, if your computer is not on a network, it cannot connect to network assets like an OLAP Cube.

    For the detail information about it, please refer to the following link.
    Excel Can't Connect to the OLAP Cube

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, December 3, 2013 12:19 PM
    Moderator
  • Hi

    I think both the assumptions are wrong. All other users are able to access the cube exactly the same way this person is trying to do. Using the same 64 bit machine, same excel 2013 and same cube name and the connections works.

    The connection even works for a 32 bit version but the user does not wana downgrade from 64 to 32 bit version.

    The computer is connected to network as well because all applications including internet works as well.

    Tuesday, December 3, 2013 9:43 PM
  • Did you check the client library versions??  http://msdn.microsoft.com/en-us/library/dn141152.aspx 

    Heidi Steen (MSFT)

    Tuesday, December 3, 2013 10:08 PM
  • Excel must have a valid licence key activated else you will get this error.
    Friday, June 13, 2014 12:48 AM
  • I'm getting the same problem. I have Excel 2013 with SQL Server 2012 SP1. I can connect to analysis services with SSMS. I can connect to a SQL database on the same server with Excel, I just can't connect to Analysis Services. There are no firewalls on either of these machines and they are both on a domain connection.

    I've played with the IP settings of analysis services and there are no errors on the server - this looks to be a client problem.

    Are there any other areas in which to check as none of the suggested items have worked.

    Thanks.

    Monday, July 14, 2014 8:57 PM
  • Are you saying both Excel 2013 and SSMS are on the same client, and you're connecting to SSAS on a separate server under the same user identity and role?  Unless you already did this, I really recommend looking at your client libraries. SSMS uses AMO for connections; Excel uses ADOMD.NET (see  http://msdn.microsoft.com/en-us/library/dn141152.aspx ). I would also run SQL Profiler to see what errors are generated -- refer to the last section in this topic http://msdn.microsoft.com/en-us/library/dn141153.aspx#bkmk_tshoot.

    Heidi Steen (MSFT)

    Monday, July 14, 2014 10:16 PM
  • Ya'll look for zebras when you see horse tracks. It's a client problem. Our whole team could connect to analysis services using excel. Then we upgraded to Office 2013 and no one can connect.
    Thursday, April 2, 2015 6:55 PM
  • I scratched my head for a few days over this issue, the thing was that I had hte same symptoms, fixed it, installed Dev edition SQL once it became free to my laptop, then it stopped working again.

    The issue i found was that there is no error message pertaining to the actual problem. 

    I dug around logs and ended up using power pivot to attempt to connect which gave a better error that was that hte MSOLAP libarary wasn't registered.

    So i just went and registered it and viola, it worked. The problem i found was that by installing SQL server again, it somehow deregistered/replaced the DLL files and thus i assume whatever was registered before was now invalid. I registered mine via cmd line with elevated privs and just registered the msolap120.dll in C:\Program Files\Microsoft Analysis Services\AS OLEDB\120

    regsvr32 c:\program files\microsoft analysis services\As oledb\120\msolap120.dll

    For 32 bit windows just change the program files to program files(x86)

    Hope this helps!

    Chris



    • Edited by PirateBob Thursday, May 26, 2016 10:43 PM
    • Proposed as answer by PirateBob Friday, May 27, 2016 1:14 AM
    Thursday, May 26, 2016 10:42 PM
  • I scratched my head for a few days over this issue, the thing was that I had hte same symptoms, fixed it, installed Dev edition SQL once it became free to my laptop, then it stopped working again.

    The issue i found was that there is no error message pertaining to the actual problem. 

    I dug around logs and ended up using power pivot to attempt to connect which gave a better error that was that hte MSOLAP libarary wasn't registered.

    So i just went and registered it and viola, it worked. The problem i found was that by installing SQL server again, it somehow deregistered/replaced the DLL files and thus i assume whatever was registered before was now invalid. I registered mine via cmd line with elevated privs and just registered the msolap120.dll in C:\Program Files\Microsoft Analysis Services\AS OLEDB\120

    regsvr32 c:\program files\microsoft analysis services\As oledb\120\msolap120.dll

    For 32 bit windows just change the program files to program files(x86)

    Hope this helps!

    Chris



    This worked for me as well.

    Thanks Chris!


    Monday, July 3, 2017 12:57 PM
  • I had an end user with the same problem and this process fixed her issue as well. The folder and dll was 110, instead of 120, but once that was changed and the dll was reregistered, the end user was able to connect and user her cubes correctly. Thanks, Chris.
    • Edited by The Wallace Wednesday, December 6, 2017 3:51 PM
    • Proposed as answer by Rick Teixeira Friday, August 16, 2019 6:05 PM
    Wednesday, December 6, 2017 3:48 PM
  • Thanks Chris. At first I tried switching dlls and that did not work.

    Your solution worked for me as well. I used a slightly different syntax (on excel for Office 365 - 16) - 64 bit:

    regsvr32 "c:\program files\microsoft analysis services\As oledb\140\msolap.dll"

    Good luck if you are reading this.

    Rick

    • Proposed as answer by Rick Teixeira Friday, August 16, 2019 6:04 PM
    Thursday, August 15, 2019 4:18 PM