Linked Server Security using Windows Login

Question

Linked Server Security using Windows Login

tnk

SSCrazy

Points: 2613
More actions
April 27, 2010 at 12:42 pm

#232112

The error I get is:
"Access to the remote server is denied because no login-mapping exists."
This of course led me to believe I need to set up a login mapping using sp_addlinkedsrvlogin. However when I read the documentation on that I begin to think I should not have to.
I am using the same Windows Login on both instances. Login mapping is for mapping one sql server login on your local instance to the remote instance unless I am misunderstanding something.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I have always setup login mapping but I can not tell you now if I do that as part of a relex action or if it is becuase I have given the process much thought. I will say though that even though you are using the same login on each instance I would not think that would translate across without login mapping. The linked server does not know to my knowledge what login you are using without being told. In 2005 and later though there is an option in the linked server object properties screen that allows you to select the current security context when a mapping is not defined. depending on the options set in this section a connection may not be possible without a login mapping. That atleast is my beleif.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

tnk SSCrazy Points: 2613 More actions · Answer 2

This is what I ran and it's not changing a thing. It says it runs successfully but has no effect on my problem.

sp_addlinkedsrvlogin @rmtsrvname = 'REMOTESVRNAME'

, @useself = 'TRUE'

, @locallogin = 'DOMAIN\myUser'

, @rmtuser = 'DOMAIN\myUser'

, @rmtpassword = 'myDomainUsersPassword'

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

Is this a pre 2005 instance?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

tnk SSCrazy Points: 2613 More actions · Answer 4

One is a SQL Server 2008 Enterprise x64 and the other is SQL Server 2005 SP2 x64. They each must have links to each other.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 5

I would look at the linked server object in management studio. that will allow you to look at all the information in one window and might give you an indication of where the problem is. you can also veiw all the security mappings for the linked server in the security tab.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

tnk SSCrazy Points: 2613 More actions · Answer 6

That's what I started with and out of frustration and much head banging on the desk I looked up the stored procedures involved in setting up a linked server in order to try to gain more insight (often the UI can be limiting and not show all possibilities or make them confusing).

Dan.Humphries SSChampion Points: 12125 More actions · Answer 7

Here is a link to another forum where they apear to have atleast a similiar issue. Hope this is helpful.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/6543310d-1b44-4b28-8bb6-caf959c1249b

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.