add linked server fails - 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)

add linked server fails

Author

Topic

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-10 : 17:08:48

hi I have two SQL 2005 servers, one on w2k3 and another on w2k8. I am trying to add a Linked Server with format "sqlprod\sqlprod" to a sql 2005 running on w2k8. Default instance succeeds in adding but I cannot add servername\instancename. I tried with GUI, script and stored procedure. Please help, what should I look out for?

Stored procedure is:

EXEC sp_addlinkedserver
@server='SQLPROD',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='SQLPROD\SQLPROD'

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 00:34:20

What is the error you are getting? Try giving the @Server Name variable another name like "SQLProdNamedInstance".

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-11 : 09:45:33

sql network interfaces message with login timeout expired and under default settings, sql doesn't allow remote connections. Not too informative. SQLPROD is a named instance.

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-11 : 09:49:17

I'm able to add a Linked Server but when I right click it and try to test connection, it fails to connect.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 10:15:57

"sql doesn't allow remote connections" ...

Indicates that your instance SQLProd does not allow remote connections.

Right click on Server go to Properties
Connections
Check to make sure "allow remote connections" is checked off.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

swekik
Posting Yak Master

126 Posts

Posted - 2009-03-11 : 10:41:20

I am creating a linked server and coming to the security i want to use Windows account to impersonate .
I have to add Windows login to the local login and have to select the impersonate .
For a login not defined in the list above connections will :
Not be made

am i doing it right ?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 10:50:46

That is correct; what that is saying is if someone else try to access the linked server who is not defined in the list above how do you want the linked server to treat it? You can for example use a generic account to give people read access while giving a mapped list above to give them their normal access on the other server.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-11 : 10:54:54

that setting was on. we recently had a network move, I will try to single it out as the culprit. I can add this server as linked server to a second instance on this server but I cannot add this named instance to a remote server. I can add the default instance to a remote server, however.

Thanks.

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-11 : 11:30:49

it's getting even more interesting.. I was able to add this instance while passing sql login to another remote server.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 12:04:54

Okay .. I am going to take a step back sorry getting a bit confused.

You have two servers..

Server 1:
- Default Instance
- Named Instance

Server 2:
- Default Instance

You are trying to attach both instance on Server 1 as linked server on Server 2? Correct?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-11 : 13:07:55

yes correct, the problem is that default instance from server 1 attaches with no problems. Named instance doesn't attach. This same named instance attaches to Server 3 with SA Login. This same SQL login used on Server 2 doesn't work when I try to attach with remote login option.

swekik
Posting Yak Master

126 Posts

Posted - 2009-03-11 : 13:55:09

When i try to create the linked server ,iam getting a login failure for NTAuthourity/anonymous log on .I am doing anything wrong ?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 14:03:12

swekik: you are not mapping a domain or SQL account so it is trying to authenticate using Network Service account.

dbist: You got me more confused LOL. So we have 3 servers .. hmm when attaching the named instance to server 2 is the default instance attached already?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

swekik
Posting Yak Master

126 Posts

Posted - 2009-03-11 : 14:10:36

I am doing these .
1.Giving the servername
2.Server Type-SQL Server
3.Security -Add the server login(I am giving a service account which has read access to the databases on both the servers) & clcik impersonate.
Under that For a login not defined on the list above connections will
be made using the current security context
4.RPC -ON
5.RPCOUT -ON

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 14:53:00

Sorry Swekik I misunderstood your error :). You are getting that error because of double-hop issue; the server are not setup for delegation.

What you can do is:

Server B: Create a SQL Server login; with strong password and access to resources you need.
Server A: Create a Link between your account (or whoever) and map it to the login created on Server B.

If you wish to get delegation working then you'll have to get Active Directory guys involved and have an SPN set up to allow for delegation.

Ref: http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx

Thanks..

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

swekik
Posting Yak Master

126 Posts

Posted - 2009-03-11 : 15:16:41

I want to setup using other service account which is an AD account.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 15:28:18

You will have to get SPN setup for the server you want to link to; if it is not allowed in AD it will not work because of two-hops. Please look at the referenced link. Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-11 : 17:18:36

uhhh. 1st server has a default instance and named instance.
2nd server has a default instance. This is the instance I am trying to add a linked named instance from the first server to. this server is running windows 2008.

There is a 3rd server where I was successfully able to add the named instance from the first server to by using sql login (this was for testing purposes only) just to eliminate the fact that it's something I am doing wrong. This same step on server #2 did not work, which leads me to believe that server #2 (windows 2008) is not configured properly.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 17:26:48

But you were able to connect to Server 2 from Server 1: Default Instance no? When you try to link what was the error again?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-12 : 09:26:34

yes I was able to connect the default instance from Server 1 to Server 2. Error is generic, login timeout and under default settings, remote sessions are not allowed yada yada. I tried to trace the stored procedure but nothing noteworthy was found.

dbist
Yak Posting Veteran

52 Posts

Posted - 2009-03-12 : 10:15:14

I just realized, this may be important. The server #2 where adding linked server doesn't work is SQL 2005 SP3 with Windows 2008.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources