Linked Server SQLSetConnectAttr Failed (Security Issue)

[kakmouth]

Hi, i'm having an issue with linked servers on a Server 2003 box running sql server 2005... the linked server uses an odbc connection into a foxpro database on the network... I can access the linked server (and the data it connects to) from enterprise manager as a windows authenticated user yet i cannot from a sql authenticated user thats set up as a db owner and sysadmin. also, when i try to access the linked server from a remote box as a windows authenticated user, again i cannot access the linked server... i receive the below error:

Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "MSDASQL" for linked server "FMI_SYSDATA" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

The sql connect string is as follows (and i've tried variations of this):

"driver=SQL Server;Trusted_Connection=Yes;server=SQL3;uid=xdomain\administrator;pwd=xxxxxx;DATABASE=CARGOTRACKING;Integrated Security=SSPI"

I connect to the database just fine, its only the attempt to use the linked servers where i run into a problem.

Better yet, if i run a query against the linked server in enterprise manager as a windows authenticated user, i can then access the linked server from the remote box! almost as if running that query opens up some pipe/connection to that dsn??

It appears that he problem has to do with security rights - whether permissions to the dsn or the database that dsn connects into? I've adjusted the security in the linked server to specify a "connection should be made using a security context" of our network admin user, but that didnt work... anywhere else i can adjust


I just reallized that if the foxpro data the dsn is accessing is local to the machine, there are no issues.... The problem is accessing data across the network.


let me also point out that all of this works on an existing windows server 2000 box thats running sqlserver 2000.

any thoughts?

thanks in advance!

mike