SSAS linked server

  • 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

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

  • 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

  • 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

  • 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.

  • 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

  • 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

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

  • 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 ??

  • 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

    😛

  • 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.

  • 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

    😛

  • 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 😛

  • Hello

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

    Thanks

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

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