Re: sql2005/linked server+imperonate

Christoph Muthmann wrote:
Juergen KLOIMSTEIN wrote:
[Probleme mit Verbindungsserver]

Hallo Juergen,
ohne Gewähr und von mir noch nicht ausprobiert ein Posting von http://sqlservercentral.com:

This sounds like a "double-hop" issue. When a client goes from the workstation to the first
SQL Server, that's the first hop. When you go from the first SQL Server to the second SQL
Server, that's the second hop. Under NT4.0, the only authentication method allowed is
NTLM. NTLM did NOT allow double-hops. We used to see these issues all the time before folks
started converting to Active Directory.

Active Directory attempts to use Kerberos as the authentication method first. Failing that
it drops back to NTLM to support legacy clients. However, unless you specifically make
some configuration settings, Kerberos does NOT allow double-hops, either. Kerberos does do
so through delegation, but you have to do some setup. Here are the basic steps, but
before going through them, make sure everyone involved has read the appropriate documentation
on Kerberos delegation. Kerberos delegation adds risk to your security posture and should
not be done haphazardly.

1) You have to configure the first SQL Server as being allowed to delegate. This needs to
be done by your directory (domain) admin. In Windows 2000 Active Directory it's simply a
checkbox. In Windows 2003 Active Directory there's the concept of constrained delegation
and so there a whole lot more settings.

2) You have to configure the service account on the first SQL Server to be allowed to
delegate. You also need this done by your directory (domain) admin. There is an option
under the Account tab saying "Account is trusted for delegation" that must be checked.

3) The SPNs on the SQL Servers must be set properly. Here's a knowledgebase article that
details how to do it. Again, directory (domain) admin help needed.

http://support.microsoft.com/?id=319723
How to use Kerberos authentication in SQL Server (319723)

There are additional articles that may provide some help:

http://support.microsoft.com/kb/811889/en-us

How to troubleshoot the "Cannot generate SSPI context" error message (811889)


http://support.microsoft.com/kb/909801/en-us
How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 (909801)

Oh, and one more thing... kerbtray is your friend when troubleshooting.


Lass von Dir hören, was draus geworden ist!
Gruß Christoph
--
(Please post ALL replies to the newsgroup only unless indicated otherwise)

.

Relevant Pages

  • Re: Cant get Impersonation / delegation to work
    ... the service needs to be trusted for delegation with "any protocol" ... app to Kerberos when you need to delegate to the back end. ... Make sure you have the proper SPN set on the account running the service ... allow connection to a remote SQL Server. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: View Type of Protocol
    ... Thanks a lot for the feedback John. ... information about the type of authentication being used. ... some reason Kerberos is not available. ... they are not authenticated and the SQL Server login fails. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: PROBLEM: ASP on IIS 5 secured via "Windows Integrated Authentication" accessing "
    ... uses NT group based permissons on the SQL Server, ... > transfered to the IIS box and IIS does a local logon. ... > delegation for all accounts. ...
    (microsoft.public.inetserver.iis.security)
  • Re: T-Sql & Active Directory Roles, Identification of original login of impersonated IDs an
    ... We want to be able to control access to data within a table based on a ... role within Active directory. ... All the sites are held in a single database table. ... can grant permissions in SQL Server to Windows groups. ...
    (comp.databases.ms-sqlserver)
  • Kerberos and ASP NET application
    ... First time poster to this group,and this is my first experience ... looking into the intricacies of Kerberos. ... NTLM and the SQL server won't let them in (ERROR message ... client is IE7 on Win XP SP2 - and enable integrated authentication ...
    (microsoft.public.inetserver.iis.security)