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

    Question

  • 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 have also messed around with EXECUTE AS and setting the TRUSTWORTHY bit on the database to ON.

    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 and that I am using Windows Authentication.

    Anyone able to help?

     

    Wednesday, September 15, 2010 12:37 PM

Answers

All replies

  • This sounds like a classic double-hop security delegation issue.  If so, it probably means that something about the Kerberos setup on your server needs to be corrected, such as resolving a duplicate SPN or some other easy to make error. If so, here is a helpful blog:

    http://blogs.msdn.com/b/psssql/archive/2010/06/23/my-kerberos-checklist.aspx

    You can check your connection by the following query (derived from http://www.marcvalk.net/2009/04/sql-query-to-identify-kerberos-or-ntlm-connection/).  If it has an auth_scheme of NTLM, then that is how your are connecting and is the indicator of the problem. 

    SELECT
        s.session_id,
        s.login_name,
        c.protocol_type,
        c.auth_scheme,
        s.HOST_NAME
    FROM sys.dm_exec_sessions s
    JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id
    AND s.session_id = @@spid

    RLF

    • Marked as answer by AdvanceIT Wednesday, September 15, 2010 6:52 PM
    Wednesday, September 15, 2010 2:43 PM
  • That was exactly what the problem was.

    I used the article referenced above along with this knowledgebase article http://support.microsoft.com/kb/319723/en-us to setup the correct SPN for the SQL Service Account.

    I wasn't able to run it from my PC until I cleared my own Kerberos tickets (by rebooting) and also using the klist command subsequently as I was rolling back permissions I had added in an effort to get this to work.

    Thank you so much!

    Wednesday, September 15, 2010 6:56 PM