June 18, 2010 at 1:44 pm
I have a client that was previously running SQL Server 2005 and has since upgraded to SQL Server 2008. They have a linked server using MSOLAP to the client. On the 2005 server the linked server connects just fine but on the 2008 server using the 10.0 provider they get the following message:
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server...
OLE DB provider "MSOLAP" for linked server ... returned message "The HTTP server returned the following error: Method Not Allowed."
It seems as though there is some compatibility issue between using the 2005 (9.0) provider vs the 2008 (10.0) provider. The rest of the settings are identical.
Can anyone confirm this to be an issue?
Is there a work around? Can we install the 9.0 provider in the 2008 server and use that?
Thanks in advance,
John
June 23, 2010 at 12:43 am
We have a similar issue.
We upgraded our development servers from 2005 t0 2008. The application server with the cubes still runns 2005. I installed the OLAP 9.0 provider. But when you go onto the 2008 machine and try to create a linked server to the 2005 cubes, the only option showing is the "Microsoft OLE DB Provider for Analysis Services 10.0" and 9.0 is not available in the list.
We therefore cannot query the 2005 cubes from the 2008 machine... I get a Logon Failure error whereas it used to work before.
OLE DB provider "MSOLAP" for linked server "SERVERNAME" returned message "The following system error occurred: Logon failure: unknown user name or bad password. ".
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "SERVERNAME".
Can someone confirm that the 10.0 version of the provider can be used when working with 2005 cubes?
Help!
June 23, 2010 at 6:14 am
Michelle Mc Cabe (6/23/2010)
We have a similar issue.We upgraded our development servers from 2005 t0 2008. The application server with the cubes still runns 2005. I installed the OLAP 9.0 provider. But when you go onto the 2008 machine and try to create a linked server to the 2005 cubes, the only option showing is the "Microsoft OLE DB Provider for Analysis Services 10.0" and 9.0 is not available in the list.
We therefore cannot query the 2005 cubes from the 2008 machine... I get a Logon Failure error whereas it used to work before.
OLE DB provider "MSOLAP" for linked server "SERVERNAME" returned message "The following system error occurred: Logon failure: unknown user name or bad password. ".
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "SERVERNAME".
Can someone confirm that the 10.0 version of the provider can be used when working with 2005 cubes?
Help!
I decided to delete and recreate and it now works. Not sure what happened there.
June 23, 2010 at 8:17 am
What did you delete and recreate? The cubes?
The problem on my side is that we have no control over the remote side's system. It's a partner system that we're just pulling data from.
John
June 23, 2010 at 10:51 am
I recreated the linked server.
I was just reading your post and error message again. I'm not sure that I understand exactly how your architecture fits together (i.e. db, web front end, and how it is connecting to the cube)
Are you getting this error when the client application tries to access information via the linked server? (I'm assuming this because of the "HTTP" in the error)
Have you tried to execute the query the client application is executing using SSMS just to determine if it is really the linked server or if it is the interaction between the provider and the client application?
June 23, 2010 at 10:53 am
We get that error message when testing the connection in SSMS.
It works fine with the exact same configuration in SQL Server 2005. The only difference is the provider version. OLAP 9.0 in 2005 and OLAP 10.0 in 2008.
John
June 23, 2010 at 12:23 pm
Remember to also check that the settings are the same, not just in the linked server properties, but also for the provider. (Under Linked Servers > Providers > MSOLAP) I have "Allow Inprocess" ticked.
I would like to reproduce your scenario. Can you please be so kind as to describe a little more detail.
For example:
Before Situation:
Server 1: SQL 2005 - Db with Linked server set up to Server 2
Server 2: SQL 2005 - Cube
Run basic MDX query using OPENQUERY method from server 1 to cube on server 2, all works fine.
Now Situation:
Server 1: Upgrade to SQL 2008 - Db with Linked server set up to Server 2
Server 2: Upgrade to SQL 2008 - Cube
Run basic MDX query using OPENQUERY method from server 1 to cube on server 2 - fail
Maybe it is also worth mentioning that I uninstalled all the SQL 2005 components after upgrading the servers to 2008 R2 (see attachment for everyting with "SQL" that is now installed on server), and reinstalled the Analysis Services 9.0 provider that I downloaded from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
June 23, 2010 at 12:34 pm
The one thing to point out, in your example above... the second server is a partner server and they are on 2005 still. So the cube is 2005.
Does that mean there is no way to connect 2008 linked servers to the 2005 cube?
June 23, 2010 at 1:01 pm
Nope, it is possible
This is my setup:
Server 1 :
- SQL 2005 - Contains OLAP cube
Server 2 :
- SQL 2008 R2 (upgraded from 2005)
- Contains linked server pointing to Server 1
- Provider for linked server is Microsoft OLE DB Provider for Analysis Services 10.0
After re-creating my linked server, I am able to open Query window in SSMS on 2008 R2 machine, and type:
SELECT * FROM OPENQUERY(SERVER1, '<some MDX here>') and return a result without error.
So if this is what you are trying to achieve, it is definitely possible.
Please feel free to ask as many questions as I can assist you with (versions, service packs etc) I know it is hard to troubleshoot because an environment can contain so many variables, but I will help anyhow I can.
June 23, 2010 at 1:39 pm
Thanks for the test.
Just one question, what happens when you click "test connection"? Does it succeed?
June 23, 2010 at 2:27 pm
Yes it does.
"The test connection to linked server succeeded."
PS: It is now 22:30 here in South Africa. Keep posting, I'll help if you need me for anything in the morning.. (well, my morning, that is.)
🙂
June 23, 2010 at 2:39 pm
Thank you so much for your help!
I'll keep testing!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply