Sybase Linked Server Error

7 Nov 2007 5:28 PM

Doug

Running SQL 2000 with a Linked Server to Sybase using Microsoft OLDE DB
Provider for ODBC and having some sporadic problems with the following:

select * from openquery(LINKEDSERVERNAME, 'select * from dbname.tablename')

If we run this using query analyzer from a desktop client using Windows Auth
for SQL connection get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].

If we run this using a SQL Login the query works just fine. Additionally,
the query using Windows Auth then begins to work as well. But only for a few
minutes and then we start seeing the error again.

Is there some kind of security setting that we have wrong? Any other info
that I could provide?

Thanks
Doug

7 Nov 2007 5:50 PM

BS

On Nov 7, 11:28 am, Doug <D***@discussions.microsoft.com> wrote:
Show quoteHide quote

> Running SQL 2000 with a Linked Server to Sybase using Microsoft OLDE DB
> Provider for ODBC and having some sporadic problems with the following:
>
> select * from openquery(LINKEDSERVERNAME, 'select * from dbname.tablename')
>
> If we run this using query analyzer from a desktop client using Windows Auth
> for SQL connection get the following error:
>
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> If we run this using a SQL Login the query works just fine. Additionally,
> the query using Windows Auth then begins to work as well. But only for a few
> minutes and then we start seeing the error again.
>
> Is there some kind of security setting that we have wrong? Any other info
> that I could provide?
>
> Thanks
> Doug

You will need to provide a username and password for the linked server
(Sybase).

19 Nov 2007 9:29 PM

Doug

Well, we figured out some of this error. We had to give "Authenticated
Users" read access to the folder on the SQL 2000 server box where the sybase
drivers are. Once we did that all was well with the world!

We were getting the same problem on our SQL 2005 box as well so we applied
the same permission to the sybase folder. The error went away but the
queries just hang and never return. I'm certain it's still another
permission problem because if we put "Authenticated Uers" into the
Administrator group the queries run fine.

Any ideas?

Is there anything documented that details what permissions are necessary
when trying to run client queries against a linked server using Windows Auth?

Thread options