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

    Question

  • 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.

     

    Friday, October 26, 2007 2:28 PM

All replies

  • 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

    Saturday, October 27, 2007 10:24 PM
  • 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.

     

    Sunday, October 28, 2007 1:32 AM
  • 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.
    Sunday, October 28, 2007 7:58 AM
    Moderator
  • 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).

     

     

    Sunday, October 28, 2007 6:02 PM
  • 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.

     

    Monday, November 05, 2007 3:29 PM
  • I have been battling this same error for hours and hours in the following context.

    An Instance of SQL Server 2005 and IIS are on the same physical box.  The application uses a SQL Server login.
    The application executes a stored procedure that uses a Linked Server to upload data in an Excel file.
    The Linked Server is created using an account with SA privileges.  The security is set to "Be made without using a security context".
    The stored procedure executes without a problem using SSMS 2008 logged in as a SA.
    The application gets the error "access denied...no login-mapping exists." when executing with the application login ID.

    When I connect to the database using SSMS 2008 and the application login ID, the security for the Linked Server shows as "Not be made" although it was defined as "Be made without using a security context".  This seems like a software bug.  If I run the stored procedure connected with the application ID, it gets the "access denied...no login-mapping exists."  If I go back to the connection made with SA privileges, the security is still "Be made without using a security context". 

    I tried granting permissions to alter any linked server to the application login ID.  Could delete, but not create a Linked Server.  Did not help solve the problem.

    I created a mapping to a local SQL login with SA privileges both with and without Impersonation.  Same error. 

    Note that the error occurs running the stored procedure in SSMS when logged in with the application ID -- so it does seem to be a SQL Server problem.

    Finally, I granted the application ID SA privleges.  The stored procedure ran under SSMS and from the .net application.  This is not a good solution.

    I have been reading everything I can find and trying other solutions but this is the only one that will work for me.  Does anyone know of a better solution than granting the application login SA privileges? 

     

    Wednesday, October 07, 2009 8:06 PM
  • This may seem a bit obvious; but check out the remote login.

    Check that the remote login actually has permission to access it's default database; for example.  If you are connecting to the remote instance as UserA; and on the remote Server the Default database for UserA is Master, check that UserA has a mapping to Master or alter the default Database for the user.

    Tony C
    • Proposed as answer by jhsharp Thursday, October 22, 2009 3:30 PM
    Thursday, October 08, 2009 12:50 PM
  • I had a same issue. What I did I created new login as "System Administrator" in ServerA with default and grated access to "Master" db.
    Thursday, October 22, 2009 3:33 PM
  •  

     

    When you provide provider (@provstr) string parameter, then you need to provide value with it. the standard format will be "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;" If you leave it empty, when you reference it from other application like asp.net web application. you will get the error mentioned, even you configured a remote account using as secuirty context. hopefully it will help.

    • Proposed as answer by sanket kokane Wednesday, February 15, 2012 5:23 AM
    Monday, May 10, 2010 5:22 PM
  • I was facing the same problem ..

    Kai's post help me out .

     I provided provider string like

    Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

    Thanks Kai , my linked server is secure now, accessible for only particular users


    -Sanket

    • Proposed as answer by sanket kokane Wednesday, February 15, 2012 9:26 AM
    Wednesday, February 15, 2012 5:23 AM
  • i am using this and it works for me

    /****** Object:  LinkedServer [EovendoSystem]    Script Date: 04/26/2012 16:45:38 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'System', @provider=N'SQLNCLI', @provstr=N'Server=Primary;FailoverPartner=Secondary;Database=system; Network=dbmssocn;',@srvproduct = 'System Link Server'

     /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'System',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='psw'


    GO



    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Monday, April 30, 2012 7:57 AM
  • look at this blog post. according to this you need to mention user ID in your connection string

    http://blogs.technet.com/b/mdegre/archive/2011/03/10/access-to-the-remote-server-is-denied-because-no-login-mapping-exists.aspx

    and this works for me..... 


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Monday, April 30, 2012 9:23 AM