Linked server Out-of-Process

  • We are trying to put our Oracle and DB2 Linked Servers out of process on our SQL server to help relieve some stress on our MemToLeave area, which is already set to 512mb. 

    The Problem is any non-administrative domain user can not access the linked server.  We have been through the Component Services (DCOM config) and set the security up based off of serveral articles we found and suggestion from Microsoft, but still find ourselves spinning our wheels.

    We are running SQL2KEE SP4 on Windows 2003 Enterprise edition SP1.  We were able to get it to work on a Windows 2000 Server, but that didn't help us much to figure out Windows 2003 because of the difference in the way they setup security now. The diffference I am referring to is how much more tighter the security is on Windows 2003.

    We believe this is a security issue, but we can't figure out what we are missing. We could make everyone an administrator of the server and we know it will work, but obviously that isn't something we are going to do.

    Error received on the client:

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandText::Execute returned 0x80004005:  The provider did not give any information about the error.].

     

  • Do you use a domain service account for sqlserver ?

    0x80004005 is always an access problem

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Yes, we are.

    We stumbled across something else this morning.  Here's the scenario:

    1) We open a SQL Query Analyzer window and attempt to connect to a linked server by running a openquery statement.
    2) Within 3 seconds after we execute the statement the following error is returned.
    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandText::Execute returned 0x80004005:  The provider did not give any information about the error.].

    3) If we execute the statement immediately after we receive the above error, the correct data is returned without error.
    4) If we wait 30 seconds or so before executing the statement again, it will error out.
     
    It is as if something gets cached.  we successfully get results to return if I continue to execute the statement; however, if we let some time lapse, it errors out.
  • So apparently the problem is related to the speed of building a connection or it might even be a connectionpooling problem.

    Check the timeout settings for connections.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • We changed the connection timeout and query timeout under the server options of the Linked Server, but that did not help.

    Under the connection tab in the server properties, the remote server connections Query-Time-out is set to 0 (unlimited) and we changed remote login timeout from 20 seconds to 0 (Change goes into affect immediately) and still no change.

    Is there some where else we should be looking?

     

  • if this connection is to be used frequently , maybe enabling connection pooling is an option

    control panel\data sources\connection pooling ....

    This way your connection is kept alive (after the application colsed it) for the time you've specified with the setting and will be used again when available.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • We were able to resolve this issue by configuring the MSDAINITIALIZE DCOM application to run as a specific local administrator account.

    Start, Run, DCOMCNFG.exe

    Component Services > Computers > My Computer > DCOM Config > right click on MSDAINITIALIZE and select Properties

    On the Identity tab, select the 'This user' option and specify a local administrator account and password.

    Select the OK button.

     

  • Thanks for posting the solution.

Viewing 8 posts - 1 through 7 (of 7 total)

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