I currently have stored procedures running in SQL2K on a Win2K3R2 that gather data from our over 1500 free-table Foxpro databases. The stored procedures use MSDASQL and the VFP ODBC driver. This has been working well since at least 2005. I am now in a situation where I need to port the SQL databases over to SQL2K8 on Win2K8R2. I have a 32-bit SQL instance running so that I can still use the Foxpro drivers. I have installed the VFPODBC and the VFP OLE DB drivers and configured the Providers in the SQL instance to "Allow Inprocess" and not "Disallow adhoc access", and all those wonderful things,
I have it to the point where when logged on locally to the SQL2K8 I can run either one of these statements below successfully:
SELECT number FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceDB=\\RMFS\FOX
e=DBF', 'SELECT number FROM ARCUSTMR')
SELECT number FROM OPENROWSET('VFPOLEDB', '\\RMFS\FOXPRO\CLIENT1\CLI
ENTDB'; ' '; ' ', 'SELECT number FROM ARCUSTMR')
However, if I try to run the same query from my PC (and a Domain Admin) I get the following errors respectively:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'arcustmr.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
OLE DB provider "VFPOLEDB" for linked server "(null)" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "(null)".
After doing considerable research I have implemented both the Temporay Folder *hack* for the Service Account (as was setup on the original server) and made the modifications to the DTC settings in Component Services as outlined at http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/83300532-e780-4ac6-9a64-7246e58c253a
I also used ProcMon to watch what happened as I attempted to run the query, and rather than getting an ACCESS DENIED message on C:\Temp, ProcMon reports an ACCESS DENIED message on \\RMFS\FOXPRO\CLIENT1\CLIE
NTDB as it impersonates my user account, so in my opinion it looks like an impersonation error or ACL or something.
I should mention that the FoxPro files are on a file server in a child domain, but both the Service Account and the user accounts have access to the files.
Anyone able to help?