We help IT Professionals succeed at work.

SQL Server linked server error

I'm trying to set up a linked server within a sql server 2008 r2 express database.
The user for sql service it's not the default one, i'm using, sqlsrv (created within user domain group).
The linked server is created but every query returns this error
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB.1" for linked server "amee_sec" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB.1" for linked server "<linked server name>".
Comment
Watch Question

BRONZE EXPERT
Commented:
If the SQL Server is 64 bit than it cannot communicate with 32 bit VFP OLE DB.
If the SQL Server is 32 bit than you have to grant access to the SQL user for VFP OLE DB provider and also for the data folder of this provider.

The VFP OLE DB provider should be available for everybody so the problem is in the data access most probably. You may log in as the SQL user and try to create or open some file in your VFP data folder. It will show whether the user used for SQL Server service is able to access your VFP data folder.
CaptainCyrilFounder, Software Engineer, Data Scientist
Commented:
cSQL = "DRIVER=SQL Native Client"
cSQL = "DRIVER=SQL Server"
cSQL = cSQL + ";SERVER=111.222.333.444"
cSQL = cSQL + ";TRUSTED_CONNECTION=No"
cSQL = cSQL + ";DATABASE=mysteryshopper"
cSQL = cSQL + ";UID=surveyor"
cSQL = cSQL + ";PWD=sax"

I used SQL Native Client and SQL Server drivers and they worked pretty well.
BRONZE EXPERT

Commented:
Of course, the preferred way is the opposite direction as Captain suggested but I have nothing against Linked server tests, so I've been answering the question at the first place. :-)
CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
:-)

Author

Commented:
Thanks for your help.
Actually I solved this issue adding the user for SQL Server service to the admin users group.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.