I have this posted in the VFP section of the forums but the more I find out about the issue the more I think there is an SQL Server security issues.
What I am doing is trying to connect to a LinkedServer of my SQL Express 2005 database running on windows vista. I have the linked server set up to connect to a local FoxPro table using the VFPOLEDB provider. If I log in as the 'sa' account I am able to do my query. But if I log into the server using a trusted connection it doesn't work. I get the error [The OLE DB provider "VFPOLEDB" for linked server "sys" reported an error. The provider did not give any information about the error.]
So I went in and set the BUILTIN\User login as the sysAdmin. I also made sure that all of the security settings in SQL server were set up the same between the trusted user and the 'sa' user.
My question is, What am I missing? Is there something that I need to set on windows or is there a setting in SQL server?
They actually are thrown by the linked server and pass by middle server to the client application.
By using delegation in distributed query, such as linked server query, the SQL instance obtains impersonated token of the user logon credential to gain access to resources of another SQL instance, the linked server. In delegation setting, the client connection and linked server object are configured to use integrated authentication in SQL Server’s term as opposed to SQL login. Some time integrated authentication also referred as trusted connection or Windows authentication.
Use sp_addlinkedsrvlogin to configure login to use self-mapping as following
“execsp_addlinkedsrvlogin ‘LinkedServer’, 'true'”
Step (2) makes middle server A try to use impersonated token of user to authenticate to server B. To verify that the linked server is setup for “self-mapping”, run query