Apply Service Pack 2 on ALL SQL Server computers, including the clients. If the SP2 is not installed, Kerberos authentication may not be established.
Scenario
Domain:VIP.COM
Domain controller: QUEEN
The default SQL server instance runs under Local System account. The SPN is automatically registered.
setspn -l queenRegistered ServicePrincipalNames for CN=QUEEN,OU=Domain Controllers,DC=VIP,DC=COM: MSSQLSvc/queen.VIP.COM:1433
Workstation:XP with client components installed. After establishing a connection to QUEEN by specifying TCP:QUEEN, run the statement:
Select net_transport,auth_scheme from sys.dm_exec_connections
However, if you connect to the SQL Server instance from the QUEEN computer itself, even you specify the TCP:QUEEN, the auth_scheme is still NTLM.
If the service account is a domain account, e.g. VIP\sqlservice, you must manually register the SPN for it. SetSPN -A MSSQLSvc/Queen.VIP.COM:1433 VIP\sqlservice. You must raise your domain function level to Windows server 2003 level. In Active Directory Users and Computers, set the DELEGATION property to be trusted.
To force the connection to use Named Pipe, specify np:queen when connecting to SQL Server.
