Link server not working thru Windows authentication - "Login failed

I've two SQL server 2005 servers running under the same domain and the
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql