Linked Server: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

[ScottStonehouse]

I'm trying to setup a linked server. The issue I am having is this error:
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,
Error: 18456)"

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.

[Linchi Shea]

How did you setup the security properties of your linked server?

Linchi

Post by ScottStonehouse
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,
Error: 18456)"
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.

[ScottStonehouse]

Every possible way. No matter which security properties I choose, I get an
error.

I think the correct way for my environment would be "Be made using the
login's current security context". That's the one that gives me this
particular error message.

Post by Linchi Shea
How did you setup the security properties of your linked server?
Linchi

Post by ScottStonehouse
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,
Error: 18456)"
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.

[Linchi Shea]

That configuration may entail security delegation. Did you try the option of
'Be made using this security context' and specify an explicit SQL login just
to see if it worked?

Linchi

Post by ScottStonehouse
Every possible way. No matter which security properties I choose, I get an
error.
I think the correct way for my environment would be "Be made using the
login's current security context". That's the one that gives me this
particular error message.

Post by Linchi Shea
How did you setup the security properties of your linked server?
Linchi

Post by ScottStonehouse
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,
Error: 18456)"
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.

[ScottStonehouse]

No I didn't - the server doesn't allow SQL logins and I don't really want to
go that route.

I setup SQL 2005 express on my workstation. I have no problems setting up
linked servers through express - I can link to both servers. So this gives
me a workaround for the moment. I still want to know why it doesn't work
connecting the servers to each other.

Post by Linchi Shea
That configuration may entail security delegation. Did you try the option of
'Be made using this security context' and specify an explicit SQL login just
to see if it worked?
Linchi

Post by ScottStonehouse
Every possible way. No matter which security properties I choose, I get an
error.
I think the correct way for my environment would be "Be made using the
login's current security context". That's the one that gives me this
particular error message.

Post by Linchi Shea
How did you setup the security properties of your linked server?
Linchi

Post by ScottStonehouse
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,
Error: 18456)"
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.

[Molly Keung]

Hi all, i've the problem! A message "Login failed for user ''NT
AUTHORITY\ANONYMOUS LOGON'" is occurred.

My setting is, there is a SQL Server 2005 set up on client's PC (Windows
XP). It has a linked server to another SQL Server 2005 on the server (Windows
Server 2000) using Sql Native Client. Our client will run a program on the
PC. When the program being executed, a sql for querying the database on
linked server will be called. The error message is shown.

There is no problem on accessing the linked server on SQL server 2000 before
upgrade.
The security setting on linked server is "Be made using the login's current
security context" as we want to use Window authentication. Mix Authentication
is set on both machine.

Also, there is another SQL server 2005 setup on other server (Windows Server
2000) call the same server for querying using linked server approach. I've
checked that there is no difference between this server and my server on
client's PC.

I takes me a long of times for solving this problem...I've tried many
solutions on the web such as using addlinksrvlogin/addlinksrv, but it can't
help.
Can anyone help me to solve it? Thanks a lot.

Molly

P.s.
Server 1 ----------OK----------> Target Server <----------Failed----- Server 2
(Windows Server) (Windows Server)
(Windows XP)
*All are SQL Server 2005

[GPage]

Run this:
select auth_scheme from sys.dm_exec_connections where session_id=@@spid

if it says NTLM that's your problem. NTLM won't allow more than one hop
where KERBEROS will allow several and the hop from your client to the server
counts. A linked server setup to use the security context of the calling
login will work if Kerberos is setup.

Post by ScottStonehouse
No I didn't - the server doesn't allow SQL logins and I don't really want to
go that route.
I setup SQL 2005 express on my workstation. I have no problems setting up
linked servers through express - I can link to both servers. So this gives
me a workaround for the moment. I still want to know why it doesn't work
connecting the servers to each other.

Post by Linchi Shea
That configuration may entail security delegation. Did you try the option of
'Be made using this security context' and specify an explicit SQL login just
to see if it worked?
Linchi

Post by ScottStonehouse
Every possible way. No matter which security properties I choose, I get an
error.
I think the correct way for my environment would be "Be made using the
login's current security context". That's the one that gives me this
particular error message.

Post by Linchi Shea
How did you setup the security properties of your linked server?
Linchi

Post by ScottStonehouse
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server,
Error: 18456)"
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.

[Molly Keung]

I've run this and obtain "NTLM" as result.

My question is why my connection is double hop?
There is a client on server A tried to connect to server B. It seems a
single hop connection.

Also, would you kindly tell me how to setup Kerberos on my server? Actually,
I found some solutions about this, but, even I do as the guideline, it wasn't
work.

I really appreciate for all your kindly help.