SQL Server 2005 - Linked Visual Foxpro Authorization
- by John
Here's the Scenario:
We have an existing SQL 2000 Server that has a linked server to a share directory (on another server) containing Visual FoxPro tables; all connections work correctly.
Porting the SQL 2000 server to a new SQL 2005 server results in questionable behavior: If you connect to the server, remotely, using Windows Authentication, you receive this error when running a query against the linked server:
OLE DB provider "MSDASQL" for linked server "[linked server name]" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'MyTable.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "[linked server name]".
However, logged in locally, the query works fine. The query also works correctly when logged in remotely, but using a SQL login. The only scenario I receive the error is when connected remotely, using windows authentication. As I mentioned before, this works on the SQL 2000 server, and both the old and new servers are running under the same network account (which has access to the folder the FoxPro files are in).
Doing a little searching on the internet it looks like others have run into this situation, but I haven't found a resolution. Has anyone run into this before?
