troubleshooting Question

OPENROWSET Problem with FoxPro and SQL Server 2008 on Windows 2008 R2

Avatar of DaMule
DaMuleFlag for United States of America asked on
FoxProMicrosoft SQL Server 2008
15 Comments1 Solution3662 ViewsLast Modified:
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\FOXPRO\CLIENT1\CLIENTDB;SourceType=DBF', 'SELECT number FROM ARCUSTMR')

or

      SELECT number FROM OPENROWSET('VFPOLEDB', '\\RMFS\FOXPRO\CLIENT1\CLIENTDB'; ' '; ' ', '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)".

and

      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\CLIENTDB 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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 15 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros