locked
Linked server: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' RRS feed

  • Question

  • Hi ALL,

    I have two SQL Server instances (s1 and s2). Both are sql server 2008r2. And they are not in same computer. I make s2 as linked server and add it to s1. And then, I want to query two tables from s1 and s2 in third computer. In third computer, I make a connection to connect s1 by SSMS. And then I write down a simple code:

                                  "SELECT CountyID, CountyName, Shape FROM [s2].MyDB.dbo.countyBoundariesTable"

    When I run it, error message says: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

    My user account is configured to map to sysadmin on s1. And it is permitted to read MyDB in s2 only.

    Any thought?

    Thanks!

    Tuesday, November 19, 2013 7:45 PM

Answers

  • I use a self-mapping is used for a specific login against a specific linked server. Also, The option "Be made using this security context" is checked.

    Besides, what do you mean "USE AN EXPLICIT SQL SERVER USER"? Thanks!

    Hi ParisLiu,

    First, please check the current SQL Server login on s1 has permission to log on SQL Server instance on s2. Regarding to “USE AN EXPLICIT SQL SERVER USER”, we can create a SQL Server login for SQL Server instance on s2, grant query permission of [s2].MyDB.dbo.countyBoundariesTable to this user, and then with linked server properties, security tab, please map the current SQL Server login on s1 to this SQL Server login on s2.

    Create Linked Servers (SQL Server Database Engine)
    http://technet.microsoft.com/en-us/library/ff772782.aspx

    sp_addlinkedsrvlogin (Transact-SQL)
    http://technet.microsoft.com/en-us/library/ms189811.aspx

    Best Regards,
    Allen Li


    Allen Li
    TechNet Community Support

    Thursday, November 21, 2013 3:20 AM

All replies

  • Check the impersonation settings on the properties dialog of the lonked server object. seems that you cannot use impersonation. This is normally the case when both servers are not in the same domain. Use an explicit SQl Server user.
    Tuesday, November 19, 2013 8:36 PM
  • I use a self-mapping is used for a specific login against a specific linked server. Also, The option "Be made using this security context" is checked.

    Besides, what do you mean "USE AN EXPLICIT SQL SERVER USER"? Thanks!

    Tuesday, November 19, 2013 10:49 PM
  • I use a self-mapping is used for a specific login against a specific linked server. Also, The option "Be made using this security context" is checked.

    Besides, what do you mean "USE AN EXPLICIT SQL SERVER USER"? Thanks!

    Hi ParisLiu,

    First, please check the current SQL Server login on s1 has permission to log on SQL Server instance on s2. Regarding to “USE AN EXPLICIT SQL SERVER USER”, we can create a SQL Server login for SQL Server instance on s2, grant query permission of [s2].MyDB.dbo.countyBoundariesTable to this user, and then with linked server properties, security tab, please map the current SQL Server login on s1 to this SQL Server login on s2.

    Create Linked Servers (SQL Server Database Engine)
    http://technet.microsoft.com/en-us/library/ff772782.aspx

    sp_addlinkedsrvlogin (Transact-SQL)
    http://technet.microsoft.com/en-us/library/ms189811.aspx

    Best Regards,
    Allen Li


    Allen Li
    TechNet Community Support

    Thursday, November 21, 2013 3:20 AM