Advantage to SQL 2000 Linked Server Access Denied Error

Question

I am trying to create linked servers in SQL Server 2000 and SQL Server 2005 x64 to a Sybase Advantage database using the Advantage OLE DB Provider, 32-bit and 64-bit respectively.

I kept getting the following error when trying to browse the catalog and when performing a query with openquery from both SQL Servers:

OLE DB error trace [OLE/DB Provider 'Advantage OLE DB Provider' IUnknown::QueryInterface returned 0x80070005:  Access denied.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Advantage OLE DB Provider' reported an error. Access denied.

The following Stack Overflow question and answer helped me resolve this problem on SQL Server 2005 x64:

Advantage to SQL 2008 Linked Server Access Denied Error

However, I am still getting the access denied error in SQL Server 2000, despite setting Allow inprocess as suggested above.

Both linked servers use the same data source, login credentials and server options. Any help is greatly appreciated.

Answer 1

Try running a query in the Query Analyzer. You should see the Advantage specific OLE DB errors returned. For example:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Advantage OLE DB Provider' reported an error.
[OLE/DB provider returned message: Error 7078: The Advantage Database Server cannot authenticate the user. Make sure the user name and password are correct. axServerConnect
AdsConnect]

In this case it was a bad user/password