Connecting to AS2005 through Excel 2003

Question

Connecting to AS2005 through Excel 2003

Sam Peascod

SSCrazy

Points: 2497
More actions
February 22, 2010 at 3:26 pm

#230203

I have a very frustrating issue trying to connect Excel 2003 to an Analysis Services 2005 cube. While creating the connection, I'm prompted to type in my server name and login details. When I do this, then click Next, Microsoft Query/the AS connection builder hangs. It doesn't time out; it sits there indefinitely. This might imply a network issue, so to that end:
- I've disabled any firewalls (including Integrity Flex by Zone Alarm)
- I've tried with and without Windows authentication (i.e. typing in a username, different to or the same as my current logon, or just leaving it blank)
- I can ping the Analysis Services server
- I installed SQL Client Components and can connect to the Analysis Services server through SSMS
- I've reviewed as many network settings as I can find and can't see any weird options which would be causing the failure
- I've tried connecting using the IP address of the AS server
- I can't identify the port because it's a named instance of Analysis Services, otherwise I'd try connecting using port notation rather than named instance
From a different PC on the same network (indeed, using the same network port), I can connect fine from Excel 2003. I have installed XML 6 and the OLE DB 9.0 connector on both systems. Both systems run Office 2003 SP3. This is a corporate build laptop and is problematic on other laptops with the same build. The only differences I can find between this laptop and the workstation (which works) are that my laptop has an older version of Symantec AntiVirus and has SafeNet Protect Drive installed. I don't see how either of these would be causing the failure from Excel when everything else seems to work (including SSMS), and I'm reluctant to disable these things until I can see a reason why they'd be breaking things.
Can anyone shed any light on this?
Sam

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sam Peascod SSCrazy Points: 2497 More actions · Answer 1

Actually, I have a Windows 7 laptop running Excel 2003 as well. I'd really prefer to get that running, but I need to make either of these work.

In the Windows 7 configuration, Excel 2003 always returns this error:

"Excel was unable to get necessary information about the cube. The cube might have been reorganized or changed on the server...". The cube has been processed.

Again:

- Excel 2003 SP3

- XML 6 (installed by default on Windows 7)

- OLE DB 9.0 (also tried OLE DB 10.0 for good measure, but no dice)

- No SSMS installed on this one, but can do if it'd prove anything

- Can ping AS server

nawillia SSCrazy Points: 2401 More actions · Answer 2

woo hoo.. finally got some 'help' turns out that the laptop i was using had 2 firewalls. i had only turned off one of them. i'm a little PO'd because I kept asking for help and saying is there a 'network' firewall. when the network admin finally paid some attention to this issue i was having, it dawned on him about the 2nd firewall. I spent 5 days researching and testing, on a problem that took 2 minutes to resolve. I had asked for help after the 1st day because i was sure i had check all i could on my end (DBA). This is what happens when you expect one type of worker to do all types of work. i don't mind learning other technologies and other components of IT. i learned some stuff in the last 5 days, but in the end, i knew i just needed someone with 'experience' to help me pin point what was going on.

robj_jackson Grasshopper Points: 14 More actions · Answer 3

Hi Sam,

I have the same issue as you.

Win7 > Excel 2003 > cubes (SQL 2005 and 2008r2)

Did you ever get this working?

Thanks

Rob

nawillia SSCrazy Points: 2401 More actions · Answer 4

well, the first part was allowing the SSAS port to be on the exception list for windows firewall, which i had done. But it turned out that there was another firewall Zone Labs installed on the computer i was u sing, and once we turned that off, it worked.

robj_jackson Grasshopper Points: 14 More actions · Answer 5

OK - I have found the answer to this issue, and here it is.

I setup an analysis services trace and attempted to connect to excel (pivot table)

This captured an error talking about locale.

To resolve I changed the WIN 7 machine regional settings from English (Australia) to English (US) and then back to English (Australia) and now it works!

Weird but it works.

Thanks

Rob