SSAS linked server

Question

SSAS linked server

Gangadhara MS

Hall of Fame

Points: 3377
More actions
December 5, 2010 at 7:30 pm

#388368

Hi.
I have a cube deployed in SQL server 2005 Enterprise edition on the same host server.
When i try to connect and try to execute sp on linked server i am getting the error
Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "MSOLAP" for linked server "SFE_Cube".
In the security contest i have "Be made using the logins current security context"
Thanks,
Ganga

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 1

This is not an SSAS issue. It is a security issue. Does the "context user" on the SSAS server have permissions on the target linked server? If not ... no Data !

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 2

Hi,

This error will not come always i have refreshed database and executed i ran successfully with out any issue.

What could be the reason for this ?

How do i check why exactly i am getting the error.

Thanks,

Gangadhar

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 3

again i am getting the error

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "MSOLAP" for linked server "" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "".

This error is unusual psl advice

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "MSOLAP" for linked server "" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "".

Yours

Ganga

James Perry Right there with Babe Points: 718 More actions · Answer 4

Are you trying to connect to the linked server using windows authentication or sql authentication? Using current security context with sql authentication would create this error. Or using windows authentication of the AS server is on a seperate box woul dalso cause this error if you dont have kerberos security setup.

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 5

Hi,

If that is the case it should't connect always sometime it will connect and work properly but for some other time it won't work what should be the cause for this.

How to identify this.

Thanks,

Gangadhar

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 6

Hi All,

Today again my linked server drops out and thrown with an error:

Error: Cannot create an instance of OLE DB provider "MSOLAP" for linked server "Expense_Cube".

Post restart of SQL server services it worked out fine.

The security in linked server properties having BE MADE HAVING THE LOGINS CURRENT SECURITY CONTEXT

And user is trying to access by using windows authentication and very sure the case will resolve after sql service restart.Attached the error.

Please help me

Thanks

GAngadhar

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 7

Does the WebUser have permissions on the Target server? and have you checked the network connection between the two servers?

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 8

yes web user is having an access to linked server as this SSAS is hosted on the same server.

If web user don't have access then it should failed always but sometimes it connects and some times drops out.

is something else ??

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 9

Have a look at the linked server options.

1. Connection timeout, and

2. Query Timeout

try setting these to 0

Also have a look at the target and the originating server properties concerning "Remote server connection" have the remote query timeout set to 0 (No Time out)

Failing that I would be getting some network monitoring installed and make sure the connectionis stable and fast/

Also have a read of this

http://www.sql-server-performance.com/tips/linked_server_p1.aspx

CodeOn

😛

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 10

Hi

Thanks for your valuable comments here.

Also have a look at the target and the originating server properties concerning "Remote server connection" have the remote query timeout set to 0 (No Time out)

Means source server (querying from SSMS) the Remote server connections with checked checkbox (Allow remote connection to this server)

and with a value 600 is exist..

Can u pls suggest what could be ideal value for this ?? ,,and if i need to found some network related troubleshoot on productions server how do we do.any tools or related link will be helpful.

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 11

Change the query timeout value to zero (0). this means no timeout enforced by SQL server. I would also make very sure that the code that the WebUser is activating is efficient and by that I mean it uses available and updates indexes. This will ensure that the queries execute quickly and don't cause bottlenecks for the application. These query timeout are designed to protect you against "rogue" code and hung transations. Test Test Test ! and the TEST again..

Have fun

CodeOn

😛

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 12

Thank you much for your comments here.

I will set the source server connection timed out to 0 and monitor for some more few days for good.Anyhow thanks for your help.

But still i have a doubt sometimes it works fine and not for some other time even in DEV server the connection timed out value is 600 but there is no time out in DEV server but production server have.

I am little much confused here totally 😛

marco Newbie Points: 2 More actions · Answer 13

Hello

I know it's been over 10 years but have you found a solution?

Thanks