Microsoft Sql Server Error 18456 Linked Server
Contents |
user …” ★★★★★★★★★★★★★★★ SQL Server ConnectivityAugust 10, 200678 Share 0 0 SQL Server 2005: “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON'”.
SQL Server linked server windows authentication 2000: "Login failed for user "(null)". Reason: Not associated with a trusted SQLNt Authority Anonymous Logon Sql Server 2012
Server connection". The errors listed above is very typical when deploy linked server with delegation. They actually are thrown by login failed for user nt authority anonymous logon sql server 2008 linked servers the linked server and pass by middle server to the client application. In this post, I will discuss how to properly configure SQL instances and Windows environment in most common scenario and try to
Spn Registered
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 are configured to use integrated authentication in SQL Server’s term as opposed to SQL login. linked server login failed for user 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 linked server. It requires windows domain, correct DNS name resolution, proper account setting in both Active Directory and SQL Server. To make sure Kerberos delegation [1] is correct becomes vitallog 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 Overflow
Linked Server Error 18456 Login Failed For User
the company Business Learn more about hiring developers or posting ads with us Database Administrators
Msg 18456, Level 14, State 1, Line 1 Login Failed For User 'nt Authority\anonymous Logon'.
Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who the test connection to the linked server failed 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 The best answers https://blogs.msdn.microsoft.com/sql_protocols/2006/08/10/sql-linked-server-query-failed-with-login-failed-for-user/ 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 on each of the http://dba.stackexchange.com/questions/44795/how-can-i-get-my-linked-server-working-using-windows-authentication 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 must have their own SPN registered. The servers
SERVER - Login failed for User ‘NT AUTHORITY\ANONYMOUS LOGON' - ERRORLOG June 13, 2015Pinal DaveSQL7 commentsErrors related to logins are one of the most searched in this blog. Many of the blogs that talk about these errors are the ones that get viewed often http://blog.sqlauthority.com/2015/06/13/sql-server-login-failed-for-user-nt-authorityanonymous-logon/ too. This is one of the interesting errors, which you might see in http://serverfault.com/questions/88962/sql-server-to-sql-server-linked-server-setup your environments. One of my blog reader contacted me and told that their event log and ERRORLOG is full of below the messages:Error: 18456, Severity: 14, State: 11. Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x] The very first thing which should catch your eye would be “ANONYMOUS LOGON”. It is also referred as linked server NULL session. My first ask from him was if he was seeing any SPN related errors in the SQL Server ERRORLOG?SQL SERVER – Where is ERRORLOG? Various Ways to Find its LocationHere is the message which we found in ERRORLOG2015-06-09 16:25:59.86 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN has registered login failed for for the SQL Server service. This is an informational message. No user action is required. In the above lines, SQL is trying to register automatically. But below indicates that SQL was unable to do so.2015-06-09 16:26:06.90 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:SQL2014 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.2015-06-09 16:26:06.90 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:1500 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.So, the fix of the problem would be to create SPN which are needed for this SQL Instance. We need to use SETSPN.exe and create SPNs
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 Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top SQL Server to sql server linked server setup up vote 24 down vote favorite 3 Please explain what is required to set up a SQL Server linked server. Server A is SQL 2005 windows logins only Server B is the same (SQL 2005 windows logins only) Server A runs windows XP Server B runs Windows Server 2003 Both SQL Server services are running under the same domain account. I am logged into my workstation with a domain account that has administrative rights on both SQL Servers. Note these are both SQL Server 2005 SP2 - I've had old hotfixes pointed out to me, but those are already applied. The issue I am having is this error: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)" sql-server share|improve this question edited Nov 4 '12 at 3:44 MDMarra 87k23149292 asked Aug 28 '08 at 19:13 ScottStonehouse 53941123 migrated from stackoverflow.com Nov 28 '09 at 4:15 This question came from our site for professional and enthusiast programmers. add a comment| 9 Answers 9 active oldest votes up vote 18 down vote From My understanding of this issue it's a "HOP" issue. i.e. you are trying to use server A to relay your login details (with SSPI) to Server B. In SQL Server 2005 they have added a whole load of security issues that make this harder than it should be. The words "Kerberos Authentication" will become the bain of most sys-admins/DBA's lives. It effectively is used for pass-through authentication. Here are the basics of what you need. 1) The servers (A and B) need to be set-up in Active Directory(AD) with delegation for Kerberos enabled. (this is set through your active directory admin panel) 2) The service account that your SQL Servers run under need to have delegation enabled also (this is also set through your active directory admin panel). - if they are not running under a service account, you need to create one. 3) The Servers nee