Tuesday, June 20, 2006 9:33 AM
I have two sql server 2000 with mixed mode authetication.
I stand at one server and setup a linked server to the other using the same user id and password. However when I click to the Tables icon of linked server in Enterprise manager, there is an error message: Error 17 SQL Server does not exist or access denied. And It does not show any table. I register remote sql server in Enterprise manager fine. Could any one help me ? Thanks.
Wednesday, June 21, 2006 4:10 AM
What remote login did u used for linked servers.
The remote login should exists on both the server.
Thursday, June 22, 2006 9:14 PM
S1 - local server
S2 - remote server
1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.
2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.
Tuesday, June 27, 2006 6:50 AM
both S1 and S2 have the same user id and password.
I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17
Do you have any more suggesstion ?
Wednesday, June 28, 2006 12:31 AMModerator
Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?
Wednesday, June 28, 2006 6:22 AM
Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp
Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp
At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:
select * from QLNSParent.qlns.dbo.hoso
It alway returns the error message:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.
Do you have any suggestion ?
Wednesday, June 28, 2006 5:41 PMModerator
Can you post the output of the following commands on the 237 server?
exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'
Thursday, June 29, 2006 3:04 AM
the result of the first command:
name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0
and the result of the second command:
Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa
I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.
can you help me ?
Thursday, June 29, 2006 1:51 PM
Do u have a named instance on sqlserver2005.
Friday, June 30, 2006 1:07 AM
Both server run windows server 2003 enterprise edition.
In each server, we have a named instance of Sql server 2000.
Friday, June 30, 2006 8:49 AM
Connect by give the servername with instance.
ex: svt_test(server name\sql_2000(instance name)
Friday, June 30, 2006 7:35 PMModerator
I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?
Monday, July 03, 2006 1:14 AM
sp_addlinkedserver @server = N'QLNSParent',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'10.0.10.111',
@catalog = N'qlns',
@provstr = N''
sp_addlinkedsrvlogin @rmtsrvname = 'QLNSParent',
@useself = false,
@locallogin = 'sa',
@rmtuser = 'sa',
@rmtpassword = 'vbsp'
do you have any more suggesstion ?
Wednesday, July 05, 2006 7:24 PMModerator
Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?
If the name is ok, have you tried simply specifying:
sp_addlinkedserver N'QLNSParent', N'SQL Server'