Error 18456 Linked
Contents |
log 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 linked server error 18456 login failed for user Learn more about Stack Overflow the company Business Learn more about hiring developers or
Error 18456 State 1
posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a error 18456 sql server 2008 question and answer site for database 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:
Error 18456 State 5
Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Local login impersonation not working with a linked server up vote 5 down vote favorite 3 I have created a linked server on my server using the following: exec sp_addlinkedserver @server=N'MyOldDB', @srvproduct=N'', @provider = N'SQLNCLI', @datasrc=N'MYSERVER', @catalog=N'TheDefaultDatabase'; Now, despite the following description of how logon credentials are determined (from MSDN): microsoft sql server error 18456 A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the user credentials of the local login when connecting to the linked server on behalf of the login. This is equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server, without specifying a local user name. I am getting the following error: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. When I try to do this: select top 10 * from MyOldDB.TheDefaultDatabase.dbo.Batches Can anyone point me in the right direction here? sql-server linked-server share|improve this question asked Oct 23 '11 at 8:08 Eric Smith 218127 add a comment| 1 Answer 1 active oldest votes up vote 5 down vote accepted The error you have is unrelated to logins as such. This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security. You need "Security Account Delegation" to be configured for the loca
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On
Sqlstate 28000 Error 18456
Home » SQL Server 2008 » SQL Server 2008 - General »
Error 18456 State 38
Linked Server Setup Error...Login failed for... 14 posts,Page 1 of 212»» Linked Server Setup Error...Login failed for user error 18456 sql 2008 r2 '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, http://dba.stackexchange.com/questions/7111/local-login-impersonation-not-working-with-a-linked-server 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 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 http://www.sqlservercentral.com/Forums/Topic1337470-391-1.aspx 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: Tuesday, October 4, 2016 9:50 AM Points: 2,751, Visits: 3,649 How are you configuring the security on the linked server object? Thanks,JaredSQL Know-It-AllHow to post 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: Tuesday, October 4, 2016 9:50 AM Points: 2,751, Visits: 3,649 So, your login is domain\MSSQL? Thanks,JaredSQL Know-It-AllHow to post data/code on a forum to get the
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 http://stackoverflow.com/questions/4400195/sql-server-linked-servers-querying-one-way-is-fine-but-the-other Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server - Linked servers, querying one way is fine but the other? error 18456 up vote 1 down vote favorite 1 I have two SQL Servers which have been linked using sp_addlinkedserver 'ServerB\Instance' from ServerA and sp_addlinkedserver 'ServerA\Instance' from ServerB. If I execute the following query from ServerA then everything is okay: SELECT * FROM [ServerB\Instance].Database.dbo.Table If I execute the following query from ServerB an error occurs: SELECT * FROM [ServerA\Instance].Database.dbo.Table Error: Msg 18456, Level 14, State 1, Line 1 Login failed for user error 18456 state 'NT AUTHORITY\ANONYMOUS LOGON'. The service accounts that SQL Server runs under on ServerA and ServerB have been given elevated permissions on both servers in an attempt to solve the issue but no success. I have done research but want to avoid a convoluted process when communication one way is okay. sql-server tsql share|improve this question asked Dec 9 '10 at 15:54 youwhut 48021330 add a comment| 4 Answers 4 active oldest votes up vote 2 down vote In SQL Management Studio, view the properties for the linked server from Server B to Server A. There's a security "tab" that you can view from there. That will tell you the security context the connection from B to A is using. The error you are getting normally occurs when you don't have a valid security context set for the linked server. The reason that it can work from A to B and not B to A is that you need to set up the linked server correctly on each side. Just doing it for one is not going to work to go both ways. share|improve this answer answered Dec 9 '10 at 16:00 Charles Boyung 2,20111328 add a comment| up vote 1 down vote accepted I have solved this