Re: Linked Servers - setspn - domain account

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 05/25/03

  • Next message: ClairHolliday: "Re: Dynamic SQL" 
    Date: Sun, 25 May 2003 13:57:14 +0100

    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
"Mike Mortensen" <mmortensen@#N#O#S#P#A#Mresolutionhealth.com> wrote in
message news:Xns9384DBCB9A4A7mi123456789900@216.168.3.30...
> Hi!
>
>     I was successful getting the "double-hop" aka kerberos delegated
> security to function.  It all works when i run SQL under LocalSystem.
> However, it fails when i try to run SQL Server using a Domain Account.
>
>     I've tried setting up the SPN (Service Principal Name) using the
> setspn utility, but i don't think i have the syntax correct.  It doesn't
> help that the BOL syntax is WRONG!  It says to use it like:
>
> setspn -A MSSQLSvc/myserver.microsoft.com:1433 MYDOMAIN\sqlsvc
>
> However, this will never work because setspn expects a computername as
> the last argument.  If i use this:
>
> setspn -A "MSSQLSvc/myserver.microsoft.com:1433 MYDOMAIN\sqlsvc" myserver
>
> the SPN is added to the list of SPNs.  But when I try to use this
> configuration for delegation, I get that lovely anonymous login failed
> message.  The workstation does not even get a kerberos ticket when
> connecting to the first server in the chain! (I checked using
> kerbtray.exe)
>
> I think the problem is with the setspn command line; i've followed the KB
> articles and SQL BOL.  All the Servers are Trusted for Delegation.
> Domain SQL Service account is Trusted for Delegation.  Domain User
> account is not restricted from delgation.  It all works except when i try
> to run SQL Server using a Domain Account, so i'm at a loss.  Any ideas?
>
> Thanks in advance!
>
> Mike
>
  • Next message: ClairHolliday: "Re: Dynamic SQL"

    Relevant Pages

    • Re: SetSPN problem
      ... The SPN is on the service account object not the server ... Jasper Smith (SQL Server MVP) ...
      (microsoft.public.sqlserver.security)
    • Re: Kerberos w/ SQL and WIN2000
      ... I'm new to LDAP in general, but where would the ADSI key be located? ... > Win2000 will default to using kerberos to connect to the server anyway ... > For my server called sqlnlb01 in domain domsql.com using a service account ... > Jasper Smith (SQL Server MVP) ...
      (microsoft.public.sqlserver.security)
    • Re: SQL2005 non-administrator Service Account
      ... I run an SQL Server 2005 with a service account that is not a member of the ... account from the administrators group, that's when the service won't start ...
      (microsoft.public.sqlserver.security)
    • Service Account access fails
      ... After rebooting a windows 2000 server the SQL Server ... Access is prevent to the Ad-Ent Service Account, ...
      (microsoft.public.sqlserver.security)
    • Re: SPN Requirement
      ... Shouldn't SQL Server 2005 automaticaly register a SPN for its service ... SQL Server starts. ... setspn -A DUMMYSvc/ServerName DomainName\SQLSrvRunAs ... On the Delegation tab I would've granted delegation to MSSQLSvc on ...
      (microsoft.public.sqlserver.security)