Linked server setup - 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 server setup

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 13:57:18

I am trying to set up a liked server using the UI in Management studio. In the Object Explorer I can see the server i want to link to MyServer2 and I have connected to it. I goto to the server in which i want to create the link MyServer1 and click on Server Objects then Linked Servers, New Linked Server.

In the server name I enter the server I want to link to, MyServer1. I don't know what to enter or where after that? What else do i need to set up?

When i try this select i get the following error.

select * from MyServer1.MyDB.dbo.MyTable

Error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "MyServer1" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MyServer1".

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-13 : 14:21:45

Create the same login and roles on Destination Server and Link it in GUI with using this security.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 14:34:33

I don't see where to enter the security settings from MyServer1 in the liked set up in MyServer2. Where should i be looking?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-13 : 14:37:41

Go to Server objects:
Linked Server: put servername
Security: use with this security accounts:
login
password

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 15:04:20

in log in i selected SA from the drop down
remote user and remote password i put in my user and password.

I get the same error

Also I use Windows authentication.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-13 : 15:51:14

Create SQL login in Destination Server same as Source Server.
In security tab:
Use with this security context:
Put your logins and password:
it doesn't have to be SA.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 17:08:45

In the security tab of the linked server properties if you don't select a value from the Local Login drop down you can not enter the user or password.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-13 : 17:37:53

See Down on Security Tab:
Using this security context:
put SQL login and password

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 20:01:50

I am looking in Server Objects, Linked Servers and the properties of the server I have linked. In the properties under the Security tab there is no "Using this security context" Where Should I be looking?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-16 : 00:09:37

Did you see 'be made using this security context'?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources