June 15, 2010 at 11:23 am
I have 2 servers I am dealing with. I will call the first 'test' and the second 'main'. 'Test' uses SQL Server authentication (username,password) and 'main' uses Windows authentication. Our company is all on Active Directory domains. I can access both of these from databases from SSMS fine. I am more or less in charge of 'test', but not 'main'. I only have view access on 'main' (can't change anything).
Anyway, I set up a linked server on 'test' to link to 'main':
Execute sp_addlinkedserver main
Then I tried to run a query and got the following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'test'.
I have read that I need to add a linked server login. Something about 'multiple hops' here. I guess that since I am running it from a client machine, 'test' has to login to 'main' or something like that. And I read about impersonation.
What I want to know, is, if I create a linked server login, what am I logging into from what. Here is the syntax, I think:
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'main'
, @locallogin = 'test'
, @useself = 'True'
Is that correct? Or do I somehow have to incorporate my Windows Logon username and password? I would add a thought that I created a linked server on my local machine using SQLExpress, and the linked servers worked fine, though I did have to create a linked server login ONLY for 'test', but not for 'main'. I guess I was going just from my machine to each server. But now I am executing the query on my machine which runs the query on test which links to 'main', another step. I know I am wordy here, but I wanted to try to clarify what I am trying to do. I don't have permission on 'test' to create the linked server login so I want to be correct when I ask for it. Thanks for any help.
June 16, 2010 at 7:34 am
try in this format
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'servername',@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='password'
GO
June 18, 2010 at 6:53 am
Thank you. I'll give it a try. But I don't have the proper rights to execute that yet.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply