July 30, 2012 at 12:46 pm
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.
July 30, 2012 at 12:53 pm
How are you configuring the security on the linked server object?
Jared
CE - Microsoft
July 30, 2012 at 12:58 pm
I am Configuring with my Login as i am sysadmin on Both server...
Local Login - domain\MSSQL
Impersonate - yes
when i am selecting any of the option from below 4 options i am getting error.
July 30, 2012 at 1:07 pm
So, your login is domain\MSSQL?
Jared
CE - Microsoft
July 30, 2012 at 1:10 pm
Yes and its windows Authentication...
My login : domain_name\logicinside
July 30, 2012 at 1:13 pm
I'm confused... What are you using to log into SQL Server and what are you using for the linked server creds? Also, see this: http://technet.microsoft.com/en-us/library/ms189580%28v=sql.105%29.aspx You need these requirements met for impersonation.
Jared
CE - Microsoft
July 30, 2012 at 3:00 pm
Hi
I am using xyz\logicinside ( i can't write domain name because of privacy) using same credential for linked server creds. The same name shows in drop down list Local Login in Security tab in Linked Server.
and it is windows authenticated.
July 30, 2012 at 3:04 pm
1. read the link I posted in the previous post.
2. I don't assume anything, so both machines are on xyz domain and your user is sysadmin on both instances?
My guess is that there are some problems with delegation, so focus on the article I posted.
Jared
CE - Microsoft
July 30, 2012 at 3:09 pm
If you remote into serverA and use SSMS there, do you get the same error? My guess is no.
Jared
CE - Microsoft
July 30, 2012 at 3:23 pm
No .. i can connect easily..
July 30, 2012 at 3:25 pm
So the error is the "double hop." I don't think you can use the impersonate for remote connections to the server that is housing the linked server. Best to give it a local user on the second server.
Jared
CE - Microsoft
July 30, 2012 at 3:28 pm
So you mean to user with SQL authentication?
July 30, 2012 at 4:44 pm
Yes.
Jared
CE - Microsoft
July 30, 2012 at 8:24 pm
SQLKnowItAll (7/30/2012)
So the error is the "double hop." I don't think you can use the impersonate for remote connections to the server that is housing the linked server. Best to give it a local user on the second server.
It is definitely easier to use a sql login for the linked server. However, it's not terribly difficult to troubleshoot windows authentication/delegation/kerberos issues.
The link posted by SQLKnowItAll hits the high points, but misses some stumbling blocks.
Scenario:
Connect as myDomain\Usr to SqlServer1.myDomain.local (running as myDomain\SqlSrv1Svc) then access a linked server using windows authentication to SqlServer2.myDomain.local (running as myDomain\SqlSrv2Svc).
1. Verify that you can authenticate to SqlServer1 using Kerberos instead of NTLM.
SELECT auth_scheme FROM sys.dm_exec_connections
WHERE session_id = @@spid
If it's showing NTLM, then check the following: http://msdn.microsoft.com/en-us/library/ms191153.aspx
Also, this seems to be a good reference as well: http://technet.microsoft.com/library/ee191523.aspx
One additional gotcha is that you have to use DNS "A" records, not CNAMEs for the Servers. (Kerberos doesn't work with CNAMEs)
2. Verify that you can authenticate directly to SqlServer2 using Kerberos instead of NTLM.
3. Verify that myDomain\SqlSrv1Svc is trusted for delegation to MSSQLSvc:SqlServer2.myDomain.local
Piece of Cake!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply