Solved

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

Posted on 2010-09-13
15
2,627 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:DaMule
  • 7
  • 5
  • 3
15 Comments
 
LVL 4

Expert Comment

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

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

0
 
LVL 4

Expert Comment

by:timexist
ID: 33668487

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

GO

Open in new window

0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33669593
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33669604
Also see here, paragraph "Using Startup Accounts for SQL Server Services"
http://technet.microsoft.com/en-us/library/ms143504.aspx
0
 

Author Comment

by:DaMule
ID: 33670900
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.
0
 

Author Comment

by:DaMule
ID: 33671816
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:

SQLServerMSSQLUser$ComputerName$InstanceName
SQLServerSQLAgentUser$ComputerName$InstanceName
SQLServerMSASUser$ComputerName$InstanceName
SQLServerReportServerUser$ComputerName$MSRS10_50.InstanceName
SQLServerFDHostUser$ComputerName$MSSQL10_50.InstanceName
0
 

Author Comment

by:DaMule
ID: 33672566
I also tried turning off UAC with no change.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33674214
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?

SELECT number FROM \\RMFS\FOXPRO\CLIENT1\CLIENTDB\ARCUSTMR.DBF

Bye, Olaf.
0
 

Author Comment

by:DaMule
ID: 33674758
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.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33678081
alter PROCEDURE [dbo].[myVFP]
       @para1 varchar(50) = null,
      @para2 varchar(50)= null
WITH EXECUTE AS 'dbo'
AS
Begin
....

end

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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33679876
I know this needs to be embedded within OPENROWSET.

Bye, Olaf.
0
 

Author Comment

by:DaMule
ID: 33681425
Olaf, your suggestion works logged in locally to the SQL Server, but not from a PC
0
 

Author Comment

by:DaMule
ID: 33681436
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
0
 

Accepted Solution

by:
DaMule earned 0 total points
ID: 33685581
I posted this question on the MSDN Forums and got a solution. The thread found here http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/7b66009c-3592-4af2-98bc-b12ea2aab4a6/ reveals that it was a Kerberos problem with the service account setup and it needed reconfigured for delegation, etc.

Thank you gentlemen for your efforts.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33699710
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now