Advantage to SQL 2008 Linked Server Access Denied

Question

I am trying to create a linked server in SQL 2008 to an Advantage v7 database server. I've successfully created the linked server in SQL 2008 using the commands below and the connection tests out fine. Also, the Advantage DB I'm connecting to does not have a data dictionary.

SQL to create the Linked Server

EXEC master.dbo.sp_addlinkedserver 
  @server = N'ADVANTAGE', @srvproduct=N'Advantage', 
  @provider=N'Advantage.OLEDB', 
  @datasrc=N'\\asc1\questtest$\spaulrun'
 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin       
  @rmtsrvname=N'ADVANTAGE',
  @useself=N'False',
  @locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

When I run the following query:

select * from
openquery(ADVANTAGE,'select * from members')

I get this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Advantage.OLEDB" for linked server "ADVANTAGE" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Advantage.OLEDB" for linked server "ADVANTAGE".

I've verified that this is not an NT File permissions issue. Also, just before the error messages are displayed, I can see all of the column names on the screen for just a second. So I'm getting at least some table information back from Advantage. I just don't know why I'm getting the Access denied message.

Any help is greatly appreciated!

Answer 1

This sounds familiar, but I don't remember the exact error message I got.

Try going to the Advantage OLE DB Provider in the Linked Server setup and right click and choose properties and set "Allow Inprocess".

If I remember correctly, in Management Studio Express i went to Server Objects -> Linked Servers -> Providers -> Advantage OLE DB Provider (Right Click->Properties or Double Click)

Set "Allow Inprocess"