linked server - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

linked server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-29 : 06:33:19

mamzy writes "I am trying to create a linked server to non-cluster server(A) having (win 2003 & sql2000) from a cluster server (B) having ( win2000 sp4 & sql2000), i create an sql user1 on server A having access to db1 and used it in server B in the security tab when creating the linked server using the EM ,the linked server is created but when double clicking on tables and views a message saying error 18452 user1 not associated with trusted sql server connection ,please note the follwing
1- both server having mixed mode for authentication
2- i am registring both server using NT authentication in my pc
3- when registring the server B using sa user i am not getting any error and can view data from server A

please help me resolve this problem??????

thank you very much

mamzy"

kish
Starting Member

45 Posts

Posted - 2005-03-29 : 07:43:49

Try giving user1 sysadmin rights and then creating the linked server. It sounds like some security issue to me.

Kishore

VijayMallapur
Starting Member

4 Posts

Posted - 2005-03-30 : 11:49:56

If you have a sa authetication Rights and your servers are in a single domain then it should not have any problem

Just try with this SP compile it on one server and try this with my SP its using the in built stored procedure of sp_Addlink server

---------------------------------------------------------------------
CREATE PROCEDURE MyConnectionToOtherServer
(
@ConnectingServerName VARCHAR(50) = NULL,
@LocalLoginName VARCHAR(30) = NULL,
@LocalLoginPassWord VARCHAR(30) = NULL,
@DropAddFlag TINYINT
)
AS
/*
@DropAddFlag 1: Register the Server
2: Drop the Server
3:
*/
/* ---------------------------------------------------------------------*/
/* Created On : 01-Jul-2002 */
/* ModifiedOn : */
/* OutPut : */
/* Purpose : Establishing a Connection to the Remote Server */
/* ---------------------------------------------------------------------*/
/* Check the The Server is Already Connected Or Not */
IF( @DropAddFlag = 1 )
BEGIN
IF EXISTS( SELECT * FROM Master..SysServers
WHERE Master..SysServers.srvname = @ConnectingServerName )
BEGIN
RAISERROR ('Server is Already Registered', 16, 1)
RETURN
END
ELSE
BEGIN
/* -------------------------------------------*/
/* Add the Server in Master..SysServers Table */
/* so that you can Able to Access this Server */
/* -------------------------------------------*/
EXEC master..sp_addlinkedserver @ConnectingServerName, N'SQL Server'
/* --------------------------------------------------------*/
/* connect to the Linked Server with all the Local Logins */
/* --------------------------------------------------------*/
EXEC master..sp_addlinkedsrvlogin @ConnectingServerName, 'false', NULL, @LocalLoginName, @LocalLoginPassWord
END
END
IF( @DropAddFlag = 2 )
BEGIN
/* --------------------------------------------------------*/
/* Drop the Login from the Local List Of Remote Servers */
/* --------------------------------------------------------*/
IF EXISTS( SELECT * FROM Master..SysServers
WHERE Master..SysServers.srvname = @ConnectingServerName )
BEGIN
EXEC master..sp_dropserver @ConnectingServerName, 'droplogins'
END
ELSE
BEGIN
RAISERROR ('No Server Found', 16, 1)
RETURN
END
END
-----------------------------------------------------------------

the parameter you have to specify are
To register a server
eg.
exec MyConnectionToOtherServer 'Destination_ServerName', 'sa', 'myPassword', 1

If you get the error the server already exists then try with passing parameter 2
eg.
exec MyConnectionToOtherServer 'Destination_ServerName', 'sa', 'myPassword', 2

To access any records from the destination server
Select * from destServername.DestDBname,dbo.MyTable

try and reply me

Vijay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources