Linked Server: Works local, but not remote

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server: Works local, but not remote

Author

Topic

drueter
Starting Member

2 Posts

Posted - 2007-11-14 : 11:39:58

I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).

However, when I execute a query from a remote machine I get an error (see below for the exact message).

I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?

Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?

Errors:

OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".

Environment: SQL 2005, Standard Edition SP2

Thanks in advance for any suggestions.

David Rueter
drueter@assyst.com

drueter
Starting Member

2 Posts

Posted - 2007-11-14 : 11:51:31

UPDATE: the problem has to do with security rights, and not being local vs. remote to the SQL server.

If I log in via integrated security, queries to the linked server work fine.

If I log in via SQL authentication, I receive the error.

I guess this makes sense, as the integrated security user has access to the machine SQL Server is running on (and thereby to the ODBC DSN)--whereas the SQL authenticated user does not.

My problem is the same though: I would like to find a way for a SQL authenticated user to initiate a query against the linked server.

Is this possible?

David Rueter
drueter@assyst.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-14 : 23:15:38

How did you map sql id to user on linked server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources