Legacy Connectivity in a 64 bit world, Part 5 of 5

And now, the final boot in the teeth...

As I detailed in my last post, it was decided to segregate access to Visual FoxPro data by running OPENQUERY statements on a 32 bit SQL Server 2005 instance. I did all of my initial tests on my laptop, using a local SQL Server instance to access the FoxPro files stored on network file shares. Before I deployed this to a server, my initial thinking was that this wouldn't be too bad once we put support for delegation into place.

Aside: For the uninitiated, delegation allows a SQL Server to use the Active Directory credentials (AKA "domain logins") of the currently-connected user to perform tasks. Essentially, you need to create an Active Directory object called a Service Principal Name or "SPN". This is not hard to set up. For me, the bigger chore was getting over the heebie-jeebies caused by the idea of having a service impersonate users.

In our case, one server would be doing remote queries to another SQL Server. By putting this into place, we could keep our linked server configurations simple, without lots of "mapping" of "user on localserver" to "user on remoteserver". This also works for SQL Server credentials (AKA "standard logins" or "sql logins"), as long as the usernames and passwords match on the local and the remote servers. We would just need to create logins and users on the 32 bit server, as we normally would. This would help to document our security situation and should make adding and removing users easy for the DBAs since we use Active Directory groups for everything. The network guys do the work of adding users to the AD groups, so the DBAs have effectively outsourced that function to the network security people.)

Here's the thing: The Visual Fox Pro OLEDB drivers do not work if you are querying them remotely.

And by "remotely", I mean anything that isn't running on the server itself. If I use SSMS from my workstation and try to use AD credentials, the query fails with the message:
OLE DB provider "VFPOLEDB" for linked server "OLEDBFOXPRO" returned message "Invalid path or file name.".

If I reconnect using SQL credentials, the query works. If I use NTLM authentication rather than Kerberos authentication, both of which authentiate my AD credentails, the query fails.

The situation is the same if I set up a linked server and query it instead of going directly to the Fox Pro server. This makes sense because a linked server will just go and query the Fox Pro server on my behalf. If I can't directly run a query with SSMS, the linked server won't be able to run that query either.

If I RDP onto the 32 bit server and use SSMS to run a query, it works fine. It doesn't matter if I either AD or SQL credentials; they both work.

This is very repeatable and fails even if I specify a full path to the file.

This doesn't seem to be affected by what credentials the SQL Server services run under.
If I copy the FoxPro files to the 32 bit server, it also works if I connect with AD credentials. Unfortunately, we need these files on the network file shares.

To sum up: The failure is specific to what kind of credentials I connect with. I believe that this is a bug in the OLEDB drivers.

So, we will have to set up a certain amount of mappings of user credentials in our linked servers. By luck, many of the applications use SQL Server credentials rather than Active Directory credentials, and those seem to work.