cannot access data on linked server

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)

cannot access data on linked server

Author

Topic

olliman
Starting Member

3 Posts

Posted - 2011-07-28 : 05:49:16

Hello,

this is the set-up

- SQl Server 2005 on a Win2003(x86) server
- Set up ODBC Datasource on that machine for Access MDB with MDW security located on network share in the same LAN
- Tested ODBC access -> works
- Set up linked server (DFS_BSTAR_PROD) object for the Access MDB
- Defined local logons against MSSQL mapping to remote user/pw definitions in the MDW
- Tested linked server by sp_testlinkedserver -> works
- From SQL SMS I can perform a query like this:

SELECT * FROM OPENQUERY (DFS_BSTAR_PROD, 'SELECT * FROM capdata')

this is the problem

- want to access data on the linked server by running SQL queries from another machine, this fails
- error on the client is: datasource object MSDASQL for linked server DFS_BSTAR_PROD could not be initialized.
- error code is 7421
- on the server log: Error: 18456, Severity: 14, Status: 5.

This is the first time I am using a linked server. The idea is to access the linked server data through th MSSQL because it sits in the same LAN
with the MDB file. The application which ultemately needs the data is in a remote location and I want to speed up the access that way.

What am I doing wrong?

Thanks for any hints!

Oliver

marty1976
Starting Member

22 Posts

Posted - 2011-07-28 : 12:03:00

Error 18456 means "login failed". Can you post the output of SELECT * FROM sys.linked_logins?

olliman
Starting Member

3 Posts

Posted - 2011-07-28 : 13:32:59

quote:
Originally posted by marty1976

Error 18456 means "login failed". Can you post the output of SELECT * FROM sys.linked_logins?

yes, it says:

server_id local_principal_id uses_self_credential remote_name modifiy_date
0 0 yes Null 24.01.2011 22:03:45
1 0 no BSTAR_PRG 25.01.2011 10:46:45
1 263 no BSTAR_PRG 25.01.2011 10:46:45
2 0 no BSTAR_PRG 25.01.2011 10:47:22
2 263 no BSTAR_PRG 25.01.2011 10:48:12
0 0 no BSTAR_PRG 25.01.2011 10:52:49

thanks, Oliver

marty1976
Starting Member

22 Posts

Posted - 2011-07-29 : 03:01:34

Which server_id are you trying to access? I had the same problem and could solve it by setting uses_self_credentials=0
Right-Klick linked server, security, "be made without a security context" (translated from German, might be slightly different)...

olliman
Starting Member

3 Posts

Posted - 2011-07-29 : 06:58:11

quote:
Originally posted by marty1976

Which server_id are you trying to access? I had the same problem and could solve it by setting uses_self_credentials=0
Right-Klick linked server, security, "be made without a security context" (translated from German, might be slightly different)...

I try to access server no. 2. Changing the default security context handling doesn't change anything.

I have to add that the Access MDB uses local security credentials, which are defined in an MDW. The MDW is being referenced in the ODBC data source which the linked server refers to. I entered the working credentials into the linked server security dialog.

Best regards, Oliver

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources