This is a guest post by Allan, a bloke I work with. Allan asked my opinion of what was wrong, and I suggested a DNS server issue. (Our DNS servers have a habit of not replying on the first query, but I was wrong. )
Recently I have spent a lot of time troubleshooting what appeared to be a DLL conflict issue when a customer installed PowerPivot for Excel 2010.
Without PowerPivot installed, you could happily connect to the SQL Analysis service using the msolap100.dll that gets installed by default (version 10.0.2733.0)
Install PowerPivot, and Microsoft Excel starts throwing you vague error messages, but ONLY the first time you try and connect.
And in the eventlog:
So as usual I ran off and started playing with regsvr32, different DLL files and all that good stuff believing it was a faulty DLL.
The big issue was, there was nothing on Google. Nothing that reflected the error message I was getting, nothing that was really remotely close.
Working for a shared service organisation, I was able to install PowerPivot on another customers SOE that was also Windows XP.
Surprise Surprise… It worked.. No connection issues, no dramas.
So the next thing was to trouble shoot Group Policy, no issues there.. Logon as local admin… Nope, still have the issue. Make sure the security settings in Excel matched between the two environments… Nope…. Same deal, same vague error message.
So I installed Wireshark and noticed I was getting “RST,ACK” on the TCP packets on the initial connection attempts…
So after banging my head against the desk trying to find a solution.. It hit me, it could be the AntiVirus. One environment uses Mcafee, the other Etrust.
Etrust AntiVirus is a wonderful product, and just to be clear… That statement is laced with a healthy dose of sarcasm.
One thing that many people may not be aware of, is that the Hooks that Etrust uses, stay in place even with the services are stopped, you need to disable the services and reboot.
And that is exactly what I did…. A lo and behold…. The connection issue disappeared. And who said AV is never at fault.. Oh wait, that was me and its usually correct.
Luckily it’s a simple matter to disable the hook in to the msolap100.dll file.
- Fire up regedit and navigate to HKLM\SOFTWARE\ComputerAssociates\ITMRT
- Double click on the HookExclude key and add msolap100.dll at the end of the list.
- Hit OK, then reboot your PC.
Problem solved. You should be able to connect to your SQL R2 Cubes with no issues at all.