Martina Ponca's Blog

Environment:
Windows XP Professional (32-bit)
SSIS 2008 R2
Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'VFP',
	@srvproduct=N'foxpro', @provider=N'VFPOLEDB',
	@datasrc=N'c:\MyFolder\', @provstr=N'VFPOLEDB.1'

GO

I tried to access the FoxPro files from the linked server but was getting the following error:

The OLE DB provider “VFPOLEDB” for linked server “VFP” reported an error. Access denied. Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “VFPOLEDB” for linked server “VFP”. (Microsoft SQL Server, Error: 7399)

When the SQL Server service account run under NETWORK SERVICE or a DOMAIN Account, it didn’t work even though the domain account did have permissions to the FoxPro files. When I changed it to “Local System” it worked. To make it work with a domain account, I found the following answer in http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a525b569-77ca-41da-96e1-2ba1938dd32d

1. Start –> Run –> Dcomcnfg
2. Component services –> My computer –> DCOM config –> MSDAINITIALIZE
3. Right click on MSDAINITIALIZE –> properties –>security
4. Add the SQL Start-up account ( you may have to go to services in administrative tools under control panel to check log on account) under “launch and activation permission”, “Access permission” and “Configuration permission”.
5. Give full rights.