SNMSDN


I am trying to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid or invalid, I get the error.

Anyone else seen this There is an amazing lack of any discussion about the error when I search for it.

If I do this it works fine,

EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase'
EXEC sp_addlinkedsrvlogin 'linkedname', 'true', 'AppUser'

But as soon as I add the @provstr parameter, then I get the error if I try to use linkedserver,

EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase', @provstr='Failover Partner=otherservername'
EXEC sp_addlinkedsrvlogin @rmtsrvname='linkedname', @useself='true', @locallogin='AppUser'

It doesn't even make any difference what I put in the @provstr parameter - the sp_addlinkedserver statement always executes without an error, but running a query that uses the linked server generates the error.




Re: Error 7416 - Access to the remote server is denied because no login-mapping exists

Ekrem Önsoy


I don't know you have seen BOL, however I wanted to share the following info with you.

From BOL:

When the linked server is created against the SQL Native Client OLE DB provider, the instance can be specified by using the SERVER keyword as SERVER=servername\instancename to specify a specific instance of SQL Server. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific instance of SQL Server to which the user will be connected.

Note:

To access a mirrored database, a connection string must contain the database name. This name is necessary to enable failover attempts by the data access provider. The database can be specified in the @provstr or @catalog parameter. Optionally, the connection string can also supply a failover partner name.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx







Re: Error 7416 - Access to the remote server is denied because no login-mapping exists

SNMSDN

I certainly have looked at that in BOL, and if you look at my code, I'm doing it exactly the way BOL says to do it - and it doesn't work, hence my post.






Re: Error 7416 - Access to the remote server is denied because no login-mapping exists

Vidhya Sagar

Username and password used in sp_addlinkedsrvlogin should be remote servers login.

eg.) If you are configuring linked server to serverB from serverA, then the login used in serverA (remote login)linked server should be the login from serverB.





Re: Error 7416 - Access to the remote server is denied because no login-mapping exists

SNMSDN

That's the way that I have it - and it works fine if I don't specify anything for @provstr, but as soon as I put something (anything) in @provstr then I get the error even though the logins are exactly the same (ie. the error makes no sense).





Re: Error 7416 - Access to the remote server is denied because no login-mapping exists

GoranP

Exactly the same problem I had, and your solution solved that problem.

When I removed Provider String in sp_addlinkedserver I was able to query tables in linked server with restricted user on the local server. Both users - local and remote are restricted.

This looks to me as some kind of bug OR SQL engine does not use remote user and password when there is anything in Provider String. Maybe it expects username and password in the string itself (Server=server;username=user;password=user_password)

Anyway I hardly wait to read some logical explanation for this behavior.