Microsoft Sql Server Linked Server Error 18456
Contents |
user …” ★★★★★★★★★★★★★★★ SQL Server ConnectivityAugust 10, 200678 Share 0 0 SQL Server 2005: “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON'”.
SQL Server 2000: "Login failed for user "(null)". Reason:Nt Authority Anonymous Logon Sql Server 2012
Not associated with a trusted SQL Server connection". The errors listed above is very typical when deploy linked
Login Failed For User Nt Authority Anonymous Logon Sql Server 2008 Linked Servers
server with delegation. They actually are thrown by the linked server and pass by middle server to the client application. In this post, I will discuss how to properly configure SQL instances
Linked Server Login Failed
and Windows environment in most common scenario and try to make configuration steps as explicit as possible.
By using delegation in distributed query, such as linked server query, the SQL instance obtains impersonated token of the user logon credential to gain access to resources of another SQL instance, the linked server. In delegation setting, the client connection and linked server object spn registered are configured to use integrated authentication in SQL Server’s term as opposed to SQL login. Some time integrated authentication also referred as trusted connection or Windows authentication. Linked server login can also use SQL login, but it is not discussed here. To simplify the discussion, let’s assume two SQL Server instances are installed on machine A and B respectively. Also, let’s assume A is the middle server that has a linked server object configured which points to a SQL instance on machine B. If the client is on machine C different from A, we call it double-hop setting; if the client is collocated with middle server machine A, we call it single-hop setting. In single-hop setting, it is relatively straightforward to configure linked server to work. Believe or not, double-hop setting requires more careful configurations as you will see. This is because in single-hop setting, windows NTLM authentication, which is available in most common setting if all machines are windows, is sufficient for delegation; while in double-hop setting, Kerberos authentication is mandate for flowing user’s credential through machine boundaries from the client to the linklog in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack linked server error 18456 login failed for user Overflow the company Business Learn more about hiring developers or posting ads with us msg 18456, level 14, state 1, line 1 login failed for user 'nt authority\anonymous logon'. Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database the test connection to the linked server failed professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer https://blogs.msdn.microsoft.com/sql_protocols/2006/08/10/sql-linked-server-query-failed-with-login-failed-for-user/ The best answers are voted up and rise to the top How can I get my linked server working using Windows authentication? up vote 8 down vote favorite I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server http://dba.stackexchange.com/questions/44795/how-can-i-get-my-linked-server-working-using-windows-authentication on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server? sql-server sql-server-2008 linked-server authentication kerberos share|improve this question asked Jun 18 '13 at 21:29 Christopher Garcia 2593510 add a comment| 1 Answer 1 active oldest votes up vote 9 down vote Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation. The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem. Your Windows Account must have access to both ServerA and ServerB. You must not have the setting "Account is sensitive and cannot be delegated." Both ServerA and ServerB m
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » SQL Server 2008 - General » Linked Server Setup Error...Login failed for... http://www.sqlservercentral.com/Forums/Topic1337470-391-1.aspx 14 posts,Page 1 of 212»» Linked Server Setup Error...Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' Rate Topic Display Mode Topic Options Author Message logicinside22logicinside22 Posted Monday, July 30, 2012 12:46 PM Old Hand Group: General Forum Members Last Login: Tuesday, August 9, 2016 6:58 AM Points: 322, Visits: 1,383 Hi guys I am trying to setup linked server connection and i am getting error message shown below. Can anyone help me out to identify linked server the root cause please?An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)Thanks. Aim to inspire rather than to teach. SQL Server DBA Post #1337470 SQLKnowItAllSQLKnowItAll Posted Monday, July 30, 2012 12:53 PM SSCrazy Group: General Forum Members Last Login: Today @ 9:11 AM Points: 2,756, Visits: 3,658 How are you configuring the security on the linked server object? Thanks,JaredSQL Know-It-AllHow to post login failed for data/code on a forum to get the best help - Jeff Moden Post #1337476 logicinside22logicinside22 Posted Monday, July 30, 2012 12:58 PM Old Hand Group: General Forum Members Last Login: Tuesday, August 9, 2016 6:58 AM Points: 322, Visits: 1,383 I am Configuring with my Login as i am sysadmin on Both server...Local Login - domain\MSSQLImpersonate - yeswhen i am selecting any of the option from below 4 options i am getting error. Aim to inspire rather than to teach. SQL Server DBA Post #1337481 SQLKnowItAllSQLKnowItAll Posted Monday, July 30, 2012 1:07 PM SSCrazy Group: General Forum Members Last Login: Today @ 9:11 AM Points: 2,756, Visits: 3,658 So, your login is domain\MSSQL? Thanks,JaredSQL Know-It-AllHow to post data/code on a forum to get the best help - Jeff Moden Post #1337485 logicinside22logicinside22 Posted Monday, July 30, 2012 1:10 PM Old Hand Group: General Forum Members Last Login: Tuesday, August 9, 2016 6:58 AM Points: 322, Visits: 1,383 Yes and its windows Authentication...My login : domain_name\logicinside Aim to inspire rather than to teach. SQL Server DBA Post #1337488 SQLKnowItAllSQLKnowItAll Posted Monday, July 30, 2012 1:13 PM SSCrazy Group: General Forum Members Last Login: Today @ 9:11 AM Points: 2,756, Visits: 3,658 I'm confused... What are you using to log into SQL Server and what are you using for the linked server creds? Also