server VFPOLEDB gives Access Denied error

When I login to the windows server as administrator, the query runs. When I
login as anyone else (incl a user with as many admin rights as I can give
him), I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "A" reported an error.
Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
provider "VFPOLEDB" for linked server "A".
Any help would be appreciated."Ashley Brewerton" <AshleyBrewerton@.discussions.microsoft.com> wrote in
message news:800A97D6-2A65-4A2C-A746-56A4D4931C2A@.microsoft.com...
Hi Ashley,
Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
Here's what works for me to set up the Linked Server in SQL 2005 using the
Linked Server dialog:
Providers list: VFPOLEDB shows as an entry
Linked Servers > New Linked Server ...
Linked Server: MyLinkedServer
Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro
Product name: Visual FoxPro (I think this is optional)
Data source: "C:\Program Files\Microsoft Visual
FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
spaces in the string.)
Provider string: VFPOLEDB.1
Location: (blank)
Catalog: (blank)
Security page and Server Options page take defaults.
> When I login to the windows server as administrator, the query runs. When
> I
> login as anyone else (incl a user with as many admin rights as I can give
> him), I get the following error:
What permissions does the SQL Server local system account have?
I'm ok on my laptop with the SQL Server started with "local
system account" and my Windows login being MachineName\Cindy. When I tried
against my SQL Express instance which was started using the "NT
AUTHORITY\NetworkService" I had the same errors you report.
> Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> provider "VFPOLEDB" for linked server "A".
How are you executing your query? With the four-part naming or using
OpenQuery()?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com|||Hi Cindy
Yes, I saw your previous post and have already tried the setup you
indicated. I run the query using the standard select syntax::
select * from AURORA...CMaster
"Cindy Winegarden" wrote:
> "Ashley Brewerton" <AshleyBrewerton@.discussions.microsoft.com> wrote in
> message news:800A97D6-2A65-4A2C-A746-56A4D4931C2A@.microsoft.com...
> Hi Ashley,
> Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
> downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
> Here's what works for me to set up the Linked Server in SQL 2005 using the
> Linked Server dialog:
> Providers list: VFPOLEDB shows as an entry
> Linked Servers > New Linked Server ...
> Linked Server: MyLinkedServer
> Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro
> Product name: Visual FoxPro (I think this is optional)
> Data source: "C:\Program Files\Microsoft Visual
> FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
> spaces in the string.)
> Provider string: VFPOLEDB.1
> Location: (blank)
> Catalog: (blank)
> Security page and Server Options page take defaults.
> > When I login to the windows server as administrator, the query runs. When
> > I
> > login as anyone else (incl a user with as many admin rights as I can give
> > him), I get the following error:
> What permissions does the SQL Server local system account have?
> I'm ok on my laptop with the SQL Server started with "local
> system account" and my Windows login being MachineName\Cindy. When I tried
> against my SQL Express instance which was started using the "NT
> AUTHORITY\NetworkService" I had the same errors you report.
> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> > provider "VFPOLEDB" for linked server "A".
> How are you executing your query? With the four-part naming or using
> OpenQuery()?
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
>

server to Visual FoxPro Not Work

I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.

In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().

However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.

Here's some particulars:
--
EXEC sp_addlinkedserver
@.server='VFP',
@.provider='VFPOLEDB',
@.datasrc='\\hdmcpdctis1\tisrnddata\',
@.srvproduct='Visual FoxPro'

--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go

--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \\hdmcpdctis1\tisrnddata\ ',
'select * from [tislists.DBF]')


With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================

Any Help is greatly appreciated! Thanks,

peter :confused:Hi,

Do you still have this problem? If not, how did you get around it? I am experiencing the exact same thing and am quite confused at this point...

Thanks!|||At my last job I handled a few linked Visual FoxPro linked serves from a SQL 7 database and I did'nt encounter this but let me ask you 2 questions.

1. Are you able query any database in BOX B from BOX A?

2. Have you tried querying using the 4 part name? linked_server_name.catalog.schema.object_name|||Answers to the questions:

1) Yes I can query some DB's from BOX A while on BOX B, the success depends on what authentication type the SQL Server instance is registered with in my enterprise manager

2) I have not tried querying with the 4 part name, I have only gotten as far as trying to view the list of tables through enterprise manager when I get the error

Thanks|||Can you see the tables is enterprise manager on the local box?

If not you have problem with your linked server definition. I am leaving for the day but I can check back on this this evening if I am not totally dead to the world.|||I can see the tables in EM on the local box, just not from the remote box.

Good luck on the interview...

server to VFPOLEDB working in 2000 but not in 2005?

I have run the following command and opened a working linked server in SQL Server 2000 but it fails for 2005.

sp_addlinkedserver 'AZ','','VFPOLEDB','c:\comp01\company.dbc'

To test, I run: select * from openquery(AZ, 'select * from contact') and I ge

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "VFPOLEDB" for linked server "AZ" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "VFPOLEDB" for linked server "AZ".

The specs on the working machine: WinXP Pro, SQL Server 2000 Developer's Edition, SP4, latest VFPOLEDB from MS site.

The specs on the non-working machine: Windows Server 2003 (std), SQL Server 2005 Developer Edition SP4, latest VFPOLEDB from MS site.

Other things I have done and/or noticed:

- in the install notes, it says something about security through component services, MSDTC, having to check everything. Did that.

- if I don't use a SQL command from query analyzer and try to add the linked server by right clicking on Server Objects\Linked Servers, it demands a Product Name. Null (and ' ') is not a valid entry.

Looking for help on this.

Doug

Think I'm closer. Turns out that MDAC has not recognized VFPOLEDB despite having downloading and installed it on Windows Server 2003. I have now tried regsvr32 and it says it's registered but still not listed in MDAC (used the component checker). Thoughts?
|||Sorry, I don't have any suggestions for you. I just thought I'd sympathize because I am running into the same problem with MSDAORA instead of VFPOLEDB. I'd be interested to hear what progress you end up making.

server to VFPOLEDB working in 2000 but not in 2005?

I have run the following command and opened a working linked server in SQL Server 2000 but it fails for 2005.

sp_addlinkedserver 'AZ','','VFPOLEDB','c:\comp01\company.dbc'

To test, I run: select * from openquery(AZ, 'select * from contact') and I ge

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "VFPOLEDB" for linked server "AZ" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "VFPOLEDB" for linked server "AZ".

The specs on the working machine: WinXP Pro, SQL Server 2000 Developer's Edition, SP4, latest VFPOLEDB from MS site.

The specs on the non-working machine: Windows Server 2003 (std), SQL Server 2005 Developer Edition SP4, latest VFPOLEDB from MS site.

Other things I have done and/or noticed:

- in the install notes, it says something about security through component services, MSDTC, having to check everything. Did that.

- if I don't use a SQL command from query analyzer and try to add the linked server by right clicking on Server Objects\Linked Servers, it demands a Product Name. Null (and ' ') is not a valid entry.

Looking for help on this.

Doug

Think I'm closer. Turns out that MDAC has not recognized VFPOLEDB despite having downloading and installed it on Windows Server 2003. I have now tried regsvr32 and it says it's registered but still not listed in MDAC (used the component checker). Thoughts?
|||Sorry, I don't have any suggestions for you. I just thought I'd sympathize because I am running into the same problem with MSDAORA instead of VFPOLEDB. I'd be interested to hear what progress you end up making.

server to VFPOLEDB working in 2000 but not in 2005?

I have run the following command and opened a working linked server in SQL Server 2000 but it fails for 2005.

sp_addlinkedserver 'AZ','','VFPOLEDB','c:\comp01\company.dbc'

To test, I run: select * from openquery(AZ, 'select * from contact') and I ge

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "VFPOLEDB" for linked server "AZ" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "VFPOLEDB" for linked server "AZ".

The specs on the working machine: WinXP Pro, SQL Server 2000 Developer's Edition, SP4, latest VFPOLEDB from MS site.

The specs on the non-working machine: Windows Server 2003 (std), SQL Server 2005 Developer Edition SP4, latest VFPOLEDB from MS site.

Other things I have done and/or noticed:

- in the install notes, it says something about security through component services, MSDTC, having to check everything. Did that.

- if I don't use a SQL command from query analyzer and try to add the linked server by right clicking on Server Objects\Linked Servers, it demands a Product Name. Null (and ' ') is not a valid entry.

Looking for help on this.

Doug

Think I'm closer. Turns out that MDAC has not recognized VFPOLEDB despite having downloading and installed it on Windows Server 2003. I have now tried regsvr32 and it says it's registered but still not listed in MDAC (used the component checker). Thoughts?
|||Sorry, I don't have any suggestions for you. I just thought I'd sympathize because I am running into the same problem with MSDAORA instead of VFPOLEDB. I'd be interested to hear what progress you end up making.