Re: Linked server and delegation

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 01/29/04

  • Next message: anonymous_at_discussions.microsoft.com: "why can't I connect to server from a remote machie?" 
    Date: Thu, 29 Jan 2004 20:51:49 -0000

    You 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/default.aspx?scid=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/default.aspx?scid=kb;en-us;811889

    and this one lists some of the kerberos errors you might see
    http://support.microsoft.com/default.aspx?scid=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...
> 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
> 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,
>     Corrado
>
>
  • Next message: anonymous_at_discussions.microsoft.com: "why can't I connect to server from a remote machie?"

    Relevant Pages