linked SQL Servers - 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 2005 Forums

SQL Server Administration (2005)

linked SQL Servers

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-01-18 : 23:17:39

Okay, so i have two sql servers on the same network. i want to be able to have them linked. so i went into management studio, and did the New Linked Server Wizard.

i typed in the box "Server2" (name of the server i want to link to) and selected SQL CLIENT bullet

now the sa logins are the same, and so are the windows logins for the server, so security shouldnt really be an issue.

when i run: EXEC sp_tables_ex Server2

i get the following error:
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "SQLNCLI" for linked server "Server2" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "Server2".

is this a username/password issue? the windows administrator accounts are the same, and so are the sa logins, is there something i can do to get past this?

small update i tried:

USE master;
GO
EXEC sp_addlinkedserver 
   'Server2',
   N'SQL Server'
GO

BEGIN TRY 
    EXEC sp_testlinkedserver N'Server2'; 
END TRY 
BEGIN CATCH 
    PRINT 'Linked Server not available';  
    RETURN; 
END CATCH

ang got:
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Linked Server not available

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-19 : 20:50:08

You still have to map user to remote sql login in security tab.

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 08:11:50

Does Server2 configured to allow remote connection to it?

Natalia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources