Today I had a really painful session with SQL Server Analysis Services 2005 (SSAS)!
A client had sent me a back up of their Analysis Database as they were getting incorrect data in their Reporting Services Reports.
I restored the database and tried to browse it. I got the following error:
The query could not be processed:
o Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the 'XXX' server.
o Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the 'XXX' server.
Puzzled I did the usual things restarting services, trying Excel etc but to no avail. Nothing would let me look at the data in the cube. I tried restoring the data with every combination of options I could think of but nothing.
At last my hunt took me to the Windows Application Event Log where I saw the following error each time I tried to connect:
"The description for Event ID ( 22 ) in Source ( MSOLAP$CHARLESM2005 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Internal error: An unexpected exception occured.." Gee thanks!
However, scrolling down I saw this event earlier in the day:
"Automatic certificate enrollment for DOMAIN\charles.maitland failed to contact the active directory (0x8007054b). The specified domain either does not exist or could not be contacted.
Enrollment will not be performed.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp."
Now as I was working from home and not connected to the VPN that could make sense.
In a blinding flash I connected the 2 errors! I made a connection to my work VPN and Lo and Behold my cube was accessible again.
In conclusion. For some reason SQL Server Analysis Services will sometimes throw a fit and not let you browse your data if you cannot be authenticated AT the time against your domain controller.
If anyone can shed any light on why such an authentication process is required I would be delighted to hear it! As it stands I have just wasted over 4 hours chasing down an undocumented screw up – Sorry not a happy bunny at this end!