Can someone please give me step by step instructions for
setting up two SQL servers to use security account
delegation. Please leave out nothing. I've been working
on this for a week and still get "Error 18456: Login
failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." I've
setup SPN for both SQL accounts (i.e. setspn -A
MSSQLSvc/SQLServer.domain.com:1433 SQLserviceaccount).

One thing I'm not entirely clear on is what should I have
on the delegation tabs for the service account and the
computer accounts. Please also let me know about aby
gotchas I may have overlooked.

I'm running a Windows 2003 domain (Forest and domain are
2003)

I see some gotchas here --

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

Seeing this, you might want to check the Windows user account and make sure
Account is sensitive and cannot be delegated. Also, you might want to head
over to Services in Administrative Tools, and make sure MSSQLServer is set
to a domain user account to run under. That account must have Account is
trusted for delegation according to that article. I'm not sure what elevated
privleges for Local System is.


--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.

This posting is provided "as is" with no warranties
and confers no rights.

*******************************************************************
[quoted text, click to view]

Please check the following configuration steps

1. User trying to connect to SQL Server is not sensitive and can be
delegated (By default all users are not sensitive, so you do not need to set
anything).
2. MDAC version on the client machine should be 2.6 or more.
3. You should setup SPNs for both the SQL Servers.
4. Use sp_addlinkedsrvlogin on the first linked server to impersonate the
clients.
5. Check whether the Service account of the first SQL Server is trusted for
delegation to the Linked Server service. Since your domain is rised to 2003
level, you can use Constrained delegation. If you have the SPN setup for the
account, then you will see a delegation tab in the user account properties
in AD. To start with, set "Trusted for delegation" to all servers and then
tighten it using constrained delegation.

If you configure above steps, you should be fine. If you still face problems
then, check whether Kerberos protocol is woking on both the hops
independently. If you can use Kerberos protocol, then the problem is with
delegation. Then check whether first SQL Server is configured to impersonate
the clients and then check the delegation attributes in AD.

Thanks,
Bala Neerumalla.

--
This posting is provided "AS IS" with no warranties, and confers no
rights.
[quoted text, click to view]

One thing people often get confused on is that the SQL Server SPN has to
belong to the account used to start SQL Server, not to the machine name
(unless SQL Server is started as local system).

There are some good tips in this article:
811889 HOW TO: Troubleshoot the "Cannot Generate SSPI Context" Error Message
http://support.microsoft.com/?id=811889

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.