Linked server to FoxPro - permissions

I set up a linked server and it is working great.....when i'm logged in
under the same account that SQL runs under (also the account that i set up
the linked server as, if it matters). However, if i'm logged in as anyone
else i get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'VFPOLEDB' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
It even does this with my account (full sql and domain admin). Help!you'd want to specify the remote login and how local login is mapped.
--Everyone connects via Admin db user
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:AF38060C-1BCB-4A83-86BE-A04068521894@.microsoft.com...
>I set up a linked server and it is working great.....when i'm logged in
> under the same account that SQL runs under (also the account that i set up
> the linked server as, if it matters). However, if i'm logged in as anyone
> else i get the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'VFPOLEDB' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterface
> returned 0x80070005: Access denied.].
> It even does this with my account (full sql and domain admin). Help!|||doesn't that map logins from sql server to remote(foxpro) logins? I don't
believe there is any sort of security on the foxpro database (not even sure
that they're capable of that).
If I'm wrong about any of that, please let me know.|||ha can help me out in my prob ...? check out Ravi --> primary Key falling
short sorry for this ..!|||most jet engine has an 'admin' login.
either:
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
or
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
should work for vfp. The key is in the 'false' parameter. It tells sql not
use the current executing user credential for accessing vfp.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:6D4C43C2-8EFA-4109-BF77-4D1ADE7746D6@.microsoft.com...
> doesn't that map logins from sql server to remote(foxpro) logins? I don't
> believe there is any sort of security on the foxpro database (not even
> sure
> that they're capable of that).
> If I'm wrong about any of that, please let me know.|||Tried both, to no avail.........any other ideas?
Thanks!
"oj" wrote:
> most jet engine has an 'admin' login.
> either:
> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
> or
> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
> should work for vfp. The key is in the 'false' parameter. It tells sql not
> use the current executing user credential for accessing vfp.
>
> --
> -oj|||you're not doing this, i hope.
http://support.microsoft.com/kb/241267
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:2B4D4370-6256-4A77-B713-D0EC77326898@.microsoft.com...
> Tried both, to no avail.........any other ideas?
> Thanks!
> "oj" wrote:
>> most jet engine has an 'admin' login.
>> either:
>> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
>> or
>> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
>> should work for vfp. The key is in the 'false' parameter. It tells sql
>> not
>> use the current executing user credential for accessing vfp.
>>
>> --
>> -oj
>|||Nope. The DBC is on the SQL server.
"oj" wrote:
> you're not doing this, i hope.
> http://support.microsoft.com/kb/241267
> --
> -oj
>|||Is the folder where VFP resides shared to _everyone_.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:5F20399B-6EE4-43E3-A277-919F2B04C563@.microsoft.com...
> Nope. The DBC is on the SQL server.
> "oj" wrote:
>> you're not doing this, i hope.
>> http://support.microsoft.com/kb/241267
>> --
>> -oj
>>|||Yes, I gave everyone full control down to the folder. It is not shared
however.
"oj" wrote:
> Is the folder where VFP resides shared to _everyone_.
>
> --
> -oj
>|||please post your addlinkedserver query. perhaps, you want to update your
driver or use different provider.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:AD263E8B-498C-4D0F-90C6-25E919EFD2DF@.microsoft.com...
> Yes, I gave everyone full control down to the folder. It is not shared
> however.
> "oj" wrote:
>> Is the folder where VFP resides shared to _everyone_.
>>
>> --
>> -oj|||I'm actually using the gui to do it, but i'll list the info:
General Tab:
Provider:MS OLE DB Provider for Visual FoxPro
Data source: d:\geoffTest\test.dbc
Provider string: VFPOLEDB.1
On the security tab:
No mappings defined, all will be made without using a security context
Server Options tab:
Data Access is checked
Use Remote Collation is checked
The VFP driver on the machine is current, I just updated it last week or so.
Thanks for keeping with this!
"oj" wrote:
> please post your addlinkedserver query. perhaps, you want to update your
> driver or use different provider.
>
> --
> -oj
>|||try using version independent vfp provider (i.e. no .1).
Provider string: VFPOLEDB
also, specify "remote user" as "admin"
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
> I'm actually using the gui to do it, but i'll list the info:
> General Tab:
> Provider:MS OLE DB Provider for Visual FoxPro
> Data source: d:\geoffTest\test.dbc
> Provider string: VFPOLEDB.1
> On the security tab:
> No mappings defined, all will be made without using a security context
> Server Options tab:
> Data Access is checked
> Use Remote Collation is checked
>
> The VFP driver on the machine is current, I just updated it last week or
> so.
> Thanks for keeping with this!
>
> "oj" wrote:
>> please post your addlinkedserver query. perhaps, you want to update your
>> driver or use different provider.
>>
>> --
>> -oj|||I tried variations of both, but nothing seemed to change. I tried using the
mapping local logins to the remote login of admin, and also mapping undefined
logins to admin.
Changing the driver didn't seem to affect anything, it still queries ok when
logged in under the sql account
"oj" wrote:
> try using version independent vfp provider (i.e. no .1).
> Provider string: VFPOLEDB
> also, specify "remote user" as "admin"
> --
> -oj
>
> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
> news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
> > I'm actually using the gui to do it, but i'll list the info:
> >
> > General Tab:
> > Provider:MS OLE DB Provider for Visual FoxPro
> > Data source: d:\geoffTest\test.dbc
> > Provider string: VFPOLEDB.1
> >
> > On the security tab:
> > No mappings defined, all will be made without using a security context
> >
> > Server Options tab:
> > Data Access is checked
> > Use Remote Collation is checked
> >
> >
> > The VFP driver on the machine is current, I just updated it last week or
> > so.
> >
> > Thanks for keeping with this!
> >
> >
> > "oj" wrote:
> >
> >> please post your addlinkedserver query. perhaps, you want to update your
> >> driver or use different provider.
> >>
> >>
> >> --
> >> -oj
> >>
>
>|||i have no problem running this.
exec sp_addlinkedserver
'myVFP',
'',
'MSDASQL',
null,
null,
'Driver=Microsoft Visual Foxpro Driver;
DBQ=;
SourceType=DBC;
SourceDB=d:\geoffTest\test.dbc'
exec sp_addlinkedsrvlogin 'myVFP',false,null,'Admin',null
select * from openquery(myVFP,'select * from your_dbf')x
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:EB3FD65A-162D-46D0-8CD5-8AB1CC7708B5@.microsoft.com...
>I tried variations of both, but nothing seemed to change. I tried using
>the
> mapping local logins to the remote login of admin, and also mapping
> undefined
> logins to admin.
> Changing the driver didn't seem to affect anything, it still queries ok
> when
> logged in under the sql account
> "oj" wrote:
>> try using version independent vfp provider (i.e. no .1).
>> Provider string: VFPOLEDB
>> also, specify "remote user" as "admin"
>> --
>> -oj
>>
>> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
>> news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
>> > I'm actually using the gui to do it, but i'll list the info:
>> >
>> > General Tab:
>> > Provider:MS OLE DB Provider for Visual FoxPro
>> > Data source: d:\geoffTest\test.dbc
>> > Provider string: VFPOLEDB.1
>> >
>> > On the security tab:
>> > No mappings defined, all will be made without using a security context
>> >
>> > Server Options tab:
>> > Data Access is checked
>> > Use Remote Collation is checked
>> >
>> >
>> > The VFP driver on the machine is current, I just updated it last week
>> > or
>> > so.
>> >
>> > Thanks for keeping with this!
>> >
>> >
>> > "oj" wrote:
>> >
>> >> please post your addlinkedserver query. perhaps, you want to update
>> >> your
>> >> driver or use different provider.
>> >>
>> >>
>> >> --
>> >> -oj
>> >>
>>|||Awesome, using that syntax to create the linked server gave me a different
error message (never had to run the addlinkedserverlogin sproc):
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Since I am using different query syntax(... instead of openquery) i played
around and came up with this query, which works:
select top 10 * from geoff.test..test1
before i only had the linked server name, but i needed to add the DBC name
in there as the catalog, and now it works! Out of curiosity, is this using
the foxpro driver or the ODBC driver? Either way, thank you so much for the
help!
"oj" wrote:
> i have no problem running this.
>
> exec sp_addlinkedserver
> 'myVFP',
> '',
> 'MSDASQL',
> null,
> null,
> 'Driver=Microsoft Visual Foxpro Driver;
> DBQ=;
> SourceType=DBC;
> SourceDB=d:\geoffTest\test.dbc'
> exec sp_addlinkedsrvlogin 'myVFP',false,null,'Admin',null
> select * from openquery(myVFP,'select * from your_dbf')x
>
>
> --
> -oj
>
> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
> news:EB3FD65A-162D-46D0-8CD5-8AB1CC7708B5@.microsoft.com...
> >I tried variations of both, but nothing seemed to change. I tried using
> >the
> > mapping local logins to the remote login of admin, and also mapping
> > undefined
> > logins to admin.
> >
> > Changing the driver didn't seem to affect anything, it still queries ok
> > when
> > logged in under the sql account
> >
> > "oj" wrote:
> >
> >> try using version independent vfp provider (i.e. no .1).
> >>
> >> Provider string: VFPOLEDB
> >>
> >> also, specify "remote user" as "admin"
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
> >> news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
> >> > I'm actually using the gui to do it, but i'll list the info:
> >> >
> >> > General Tab:
> >> > Provider:MS OLE DB Provider for Visual FoxPro
> >> > Data source: d:\geoffTest\test.dbc
> >> > Provider string: VFPOLEDB.1
> >> >
> >> > On the security tab:
> >> > No mappings defined, all will be made without using a security context
> >> >
> >> > Server Options tab:
> >> > Data Access is checked
> >> > Use Remote Collation is checked
> >> >
> >> >
> >> > The VFP driver on the machine is current, I just updated it last week
> >> > or
> >> > so.
> >> >
> >> > Thanks for keeping with this!
> >> >
> >> >
> >> > "oj" wrote:
> >> >
> >> >> please post your addlinkedserver query. perhaps, you want to update
> >> >> your
> >> >> driver or use different provider.
> >> >>
> >> >>
> >> >> --
> >> >> -oj
> >> >>
> >>
> >>
> >>
>
>|||glad to hear it works for you. it's using fox driver.
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:CD8A20E1-5B31-477A-9CC7-03DB819A556E@.microsoft.com...
> Awesome, using that syntax to create the linked server gave me a different
> error message (never had to run the addlinkedserverlogin sproc):
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
> OLE DB error trace [Non-interface error: Invalid schema or catalog
> specified for the provider.].
> Since I am using different query syntax(... instead of openquery) i played
> around and came up with this query, which works:
> select top 10 * from geoff.test..test1
> before i only had the linked server name, but i needed to add the DBC name
> in there as the catalog, and now it works! Out of curiosity, is this
> using
> the foxpro driver or the ODBC driver? Either way, thank you so much for
> the
> help!
>