I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
SQL2.
Both SQL Servers:
- are hosted on DC.
- use Windows authentication only.
- run under a domain user account, SQL1User and SQL2User.
Both SQL server domain user accounts:
- are "trusted for delegation" in AD.
- have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
User Admin1 can connect successfully to both SQL Servers.
User Admin1 have not "user cannot be delegated" in AD.
Now I setup a linked server from SQL1 to SQL2.
Linked server security is set to "be made using the login's current security
context"
Trying to access the linked server cause an error 18456 "Login failed for
user NT AUTHORITY/ANONIMOUS ACCESS" error.
What am I missing'
Seems Kerberos delegation does not work and fall back to NTLM which does not
support delegation, but AFAIK my Kerberos delegation setup should be fine.
Thanks,
CorradoYou need to specify the port probably. Sounds like you've pretty much got
it. I have copied and pasted my stock response below and apart from the port
looks like you are ok. Although I suggest it in my stock reply, I probably
wouldn't enable kerberos logging on a DC unless this is a test environment
(it needs a reboot for a start)
It can be a serious PITA and yes the various docs are confusing :-)
First of all I wouldn't use setspn, I tend to use ADSI Edit
It's in the 2k support tools on any 2k server CD. However if you
want to use setspn then the syntax is :
setspn -A MSSQLSvc/SQLNLB02.DOMSQL.COM:1433 NLBSQL02Svc
This is for a server called SQLNLB02 in the domain DOMSQL.COM
with a SQL Service account of DOMSQL\NLBSQL02Svc
You must also be able from the client to resolve the FQDN of the servers
involved using ping -a servername i.e. it must return
Pinging SQLNLB02.DOMSQL.COM [xxx.xxx.xxx.xxx]
and not
Pinging SQLNLB02 [xxx.xxx.xxx.xxx]
Regardless of what anything else says, you just need to set up SPN's
for the service accounts of the two SQL Servers involved. e.g.
I have 2 servers and 2 service accounts as below
Server1 : SQLNLB01
ServiceAccount : NLBSQL01Svc
Server2 : SQLNLB02
ServiceAccount : NLBSQL02Svc
Using ADSI Edit right click on the NLBSQL01Svc in the Users
container and choose Properties.In the select a property to view
listbox choose servicePrincipalName and then add a SPN like so
MSSQLSvc/SQLNLB01.DOMSQL.COM:1433
(where the FQDN of the server is the server that uses the account
I'm editing as it's SQL Service account). Do the same for the second
server and you should be up and running.
What I find really useful is enabling Kerberos logging on all the
computers involved. This will write to the event log and you'll be
able to see exactly why it's failing. .
To enable Kerberos logging look at
http://support.microsoft.com/defaul...kb;en-us;262177
If you've got AD set up then it's generally a malformed SPN or
poor name resolution (make sure you can ping -a the server IP
addresses and get back a FQDN and not just a server name)
This article also has some good stuff about Kerberos and SSPI
http://support.microsoft.com/defaul...kb;en-us;811889
and this one lists some of the kerberos errors you might see
http://support.microsoft.com/defaul...kb;EN-US;230476
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:%23Qwx%236k5DHA.2576@.TK2MSFTNGP11.phx.gbl...
quote:
> Hi to everybody,
> I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
> SQL2.
> Both SQL Servers:
> - are hosted on DC.
> - use Windows authentication only.
> - run under a domain user account, SQL1User and SQL2User.
> Both SQL server domain user accounts:
> - are "trusted for delegation" in AD.
> - have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
> User Admin1 can connect successfully to both SQL Servers.
> User Admin1 have not "user cannot be delegated" in AD.
> Now I setup a linked server from SQL1 to SQL2.
> Linked server security is set to "be made using the login's current
security
quote:
> context"
> Trying to access the linked server cause an error 18456 "Login failed for
> user NT AUTHORITY/ANONIMOUS ACCESS" error.
> What am I missing'
> Seems Kerberos delegation does not work and fall back to NTLM which does
not
quote:
> support delegation, but AFAIK my Kerberos delegation setup should be fine.
> Thanks,
> Corrado
>
No comments:
Post a Comment