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\FOX
or
SELECT number FROM OPENROWSET('VFPOLEDB', '\\RMFS\FOXPRO\CLIENT1\CLI
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\CLIE
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?
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
or
SELECT number FROM OPENROWSET('VFPOLEDB', '\\RMFS\FOXPRO\CLIENT1\CLI
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\CLIE
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?
Microsoft SQL Server 2008FoxPro
Last Comment
Olaf Doschke
EXEC xp_cmdshell 'dir \\RMFS\FOXPRO\CLIENT1\CLIENTDB\*.*';
GO
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.
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"
http://technet.microsoft.com/en-us/library/ms143504.aspx
http://technet.microsoft.com/en-us/library/ms143504.aspx
ASKER
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.
ASKER
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$Compute
SQLServerSQLAgentUser$Comp
SQLServerMSASUser$Computer
SQLServerReportServerUser$
SQLServerFDHostUser$Comput
SQLServerMSSQLUser$Compute
SQLServerSQLAgentUser$Comp
SQLServerMSASUser$Computer
SQLServerReportServerUser$
SQLServerFDHostUser$Comput
ASKER
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?
SELECT number FROM \\RMFS\FOXPRO\CLIENT1\CLIE
Bye, Olaf.
What if you do this?
SELECT number FROM \\RMFS\FOXPRO\CLIENT1\CLIE
Bye, Olaf.
ASKER
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
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.
@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.
I know this needs to be embedded within OPENROWSET.
Bye, Olaf.
Bye, Olaf.
ASKER
Olaf, your suggestion works logged in locally to the SQL Server, but not from a PC
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
What can get if you run the script
EXEC xp_cmdshell 'dir \\RMFS\FOXPRO\CLIENT1\CLIE
GO
I think if you fix this security issue, everything should work.