Cannot set the initialization properties for OLE DB provider “MSOLAP” for linked server, Double Hop Authentication

Ohhh…

Well the above words can only describe the pain which I encountered again on a different combination of multi server components authentication of MSBI using windows authentication.

Well the set up, I worked with is as follows:

So as it goes, I had a chart on SSRS, which sources the data from the SQL server stored procedure (adding computed bands to the business data), which in turn uses a linked server to get business data from the SSAS cube.

And with the known issue of double hop authentication I was struck with the following error.

OLE DB provider “MSOLAP” for linked server “LinkedServerName” returned message “The following system error occurred: “.
Msg 7373, Level 16, State 2, Line 3
Cannot set the initialization properties for OLE DB provider “MSOLAP” for linked server “LinkedServerName”.

Well, now what ???

Looking over possible solutions, unfortunately there wasn’t much that I could progress with.

So I had to opt for the following:

With the linked server configured as follows:

Pheww…

And a sigh of relief, that now the setup was working as desired.

With a small drawback that using the IIS MSMDPUMP we disabled the authentication between the SQL and SSAS communication opening up the cube anonymously (not good… I know, but wait till I redesign my application).

Well to get the app working some times you have to take few bites which you are not happy with…
And same was the case above.

But I though to mention this as a quick work around if you ever come across a similar situation.

Hope it helps !!!