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 !!!