Link to home
Start Free TrialLog in
Avatar of DaMule
DaMuleFlag for United States of America

asked on

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

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')



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

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?
Avatar of timexist
Flag of Australia image

I think this is network security issue.
What can get if you run the script
EXEC xp_cmdshell 'dir \\RMFS\FOXPRO\CLIENT1\CLIENTDB\*.*;

I think if you fix this security issue, everything should work.

EXEC xp_cmdshell 'dir \\RMFS\FOXPRO\CLIENT1\CLIENTDB\*.*';

Open in new window

Avatar of Olaf Doschke
What is the service account? If it is NT AUTHORITY\LOCAL SERVICE like the default, then you have two seperate accounts on the computer sql server runs  and on \\RMFS and allowing access to the data on \\RFMS for NT AUTHORITY\LOCAL SERVICE does allow the local account on that server, but that is useless.

Additional to NT AUTHORITY\LOCAL SERVICE being a local account on each computer it is not allowed LAN access. You need to run SQL Server on a domain account to access data from other servers.

Bye, Olaf.
Also see here, paragraph "Using Startup Accounts for SQL Server Services"
Avatar of DaMule


The xp_cmdshell command works fine doing a directory listing of the path, both as a standard domain user and as domain admin from a pc.
Avatar of DaMule


The Service Account that SQL and the Agent are are running under is a domain account and a domain admin (for the purposes of getting it working - its always easier to peel back security when debugging). I also verified that the Service Account is a member of the groups below:

Avatar of DaMule


I also tried turning off UAC with no change.
Is there a hidden Folder on the way to the data? VFP and it's ODBC Driver and OLE DB Provider may have a problem with that.

What if you do this?


Bye, Olaf.
Avatar of DaMule


Thanks Olaf, while that syntax works in VFP, it doesn't work in TSQL While signed on interactively with the server I can do everything I need to do, its just when I try to do it from a client PC that it doesn't work - and unfortunately, thats what I need to be able to do.
alter PROCEDURE [dbo].[myVFP]
       @para1 varchar(50) = null,
      @para2 varchar(50)= null


you can put: WITH EXECUTE AS 'dbo'
inside the stored procedure.
and make sure dbo which is the database owner has the permission to read and write permission to the target file.
or you can use some other  sepcific account like " with excute as 'mydomain\admin' "

then the script should have enough privilege to avoid "access denied".

have fun.
I know this needs to be embedded within OPENROWSET.

Bye, Olaf.
Avatar of DaMule


Olaf, your suggestion works logged in locally to the SQL Server, but not from a PC
Avatar of DaMule


timexist, I tried this and received the "Access to the remote server is denied because the current security context is not trusted." I then set the trustworthy bit on the database to ON and I started getting the same errors as first reported
Avatar of DaMule
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for reporting back. I was about to ask about the authentication scheme you use, as I had the feeling it could also have to do with the Windows account of users, as you said it works directly logged in but not from clients connecting to SQL Server.

That double hop issue seems to exist for a linked server too, if you use Windows Authentication despite of a SQL Server account. I'm learning from this one, too. Thanks again for sharing.

Bye, Olaf.