Re: Kerberos w/ SQL and WIN2000

From: Jasper Smith (jasper_smith9@hotmail.com)
Date: 04/17/03 

From: "Jasper Smith" <jasper_smith9@hotmail.com>
Date: Thu, 17 Apr 2003 14:21:21 +0100

Well I had confused myself by reading a few different KB's and BOL
and had set up SPN's for both the server and the service account which
was not necessary. Thus I removed all SPN's (because I was doing this
for linked servers I had set up 4) and just added one for each of the two
service accounts. I actually used ADSI edit rather than setspn as I find it
easier to use. I did get some SSPI errors when playing about with it but
removing all the SPN's are restarting the SQL Service sorted that. If you
are not using Linked Servers then you don't need to set up an SPN AFAIK
Win2000 will default to using kerberos to connect to the server anyway
when using Windows authentication. I will check this to make sure but that's
my understanding anyway. I did spend several day's banging my head
against a brick wall getting it set up right so I know your pain :-)

For my server called sqlnlb01 in domain domsql.com using a service account
of
NLBSQL01Svc the SPN setup on the service account looks like

MSSQLSvc/sqlnlb01.domsql.com:1433

Also make sure you are using TCP/IP to connect 

-- 
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"--Charles Johnson" <cjohnson@parmedpharn.com> wrote in message
news:esK13gNBDHA.2572@TK2MSFTNGP11.phx.gbl...
> Jasper,
>
> I appreciate the reply. I'm glad to know that it works for you. I've been
> through the two links you sent, but I will march on through them again
today
> on a test server. Hopefully it will work.
>
> Could you tell me what you changed in the SPN registration?
>
> --Charles
>
>
> "Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
> news:uSU0WUGBDHA.1600@TK2MSFTNGP10.phx.gbl...
> > Have a look at
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
> >
> > I've recently been doing some work with Kerberos and SQL for
> > use in Linked server security delegation and had some problems
> > but found that when I went back to a clean config and started
> > from scratch with the SPN's everything worked as advertised.
> > I found the following KB useful in troubleshooting Kerberos issues
> > in that the logging allowed me to see that the client couldn't resolve
> > the SPN for the SQL Server which made me go back and edit the
> > SPN so that it was in the correct format.
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;262177
> >
> > This was in a native mode win2000 AD domain but I don't think
> > that is a requirement. Kerberos is the default for win2000 and
> > unless you are trying to use delegation I don't think you have to set
> > anything up for it to use it. Can you confirm that the server is trusted
> > for delegation and also the SQL Service account and the command
> > you used to set the SPN for the service account. If you switch on
> > security auditing for logon in windows if it's not already configured
> > you'll be able to see if clients are using Kerberos to connect (that was
> > the problem I had, Kerberos was failing silently and authentication
> > dropped down to NTLM but since the connection still worked I didn't
> > pick it up until I enable auditing on the client and saw the Kerberos
> > errors)
> >
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "--Charles Johnson" <cjohnson@parmedpharn.com> wrote in message
> > news:uaSS3kFBDHA.2572@TK2MSFTNGP11.phx.gbl...
> > > Greetings!!
> > >
> > > One of the great selling points Microsoft used in convincing me to
move
> to
> > > Windows 2000 was that the OS uses a more secure authentication
mechanism
> > > known as Kerberos. Did I hear that wrong?
> > >
> > > It appears that in order to use Kerberos, the account running the SQL
> > > services must be Local system or Domain Admin. Failing that, you need
to
> > > use the SETSPN utility from the resource kit.
> > >
> > > Trouble is this: When I added the Service Provider Name using the
> utility,
> > > all clients (all 2kPro sp2) could no longer connect, and all received
> > > "Cannot generate SSPI context" error messages.
> > >
> > > NETDIAG shows that everything short of SPN tests are running fine, DNS
> and
> > > Domain included. Google shows many many issues with this error, most
> > > unresolved.
> > >
> > > I'm not looking for a definitive solution, but I would like to hear
from
> > > others with similar environments. Are you running Kerberos
> authentication
> > or
> > > NTLM? Did you have to setup the SPN using SETSPN or are you running
SQL
> > > under LA or DA privileges? Do I need to switch my 2k domain for Native
> to
> > > get this working?
> > >
> > > I appreciate your input!
> > >
> > > Thank you
> > >
> > > --Charles
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Kerberos Authentication to VWMare...
    ... A Kerberos Error Message was received: ... Server Realm: ... We have checked the SPN using SetSPN with -L option and see that both MOSS ...
    (microsoft.public.windows.server.security)
  • Re: SPN for SSL over common name
    ... you can't register those SPNs under the SQL Server's ... service account is the MSSQL SPN. ... That SPN should be registered under ... Lastly, since the SQL Server is not being used for delegation anywhere, ...
    (microsoft.public.inetserver.iis.security)
  • Re: Delegation: IIS Server setup in typical 3-tier scenario.
    ... doesn't already have an SPN and/or you need to change the existing SPN. ... Kerberos is being used - it just means that an API is used to determine what ... so I'm trying to set up delegation. ... Authenticated using NTLM not Kerberos on the Web Server. ...
    (microsoft.public.inetserver.iis.security)
  • Re: SPN for SSL over common name
    ... you can't register those SPNs under the SQL Server's ... That SPN should be registered under the SQL ... Server's service account and *removed* from the SQL ... Lastly, since the SQL Server is not being used for delegation anywhere, ...
    (microsoft.public.inetserver.iis.security)
  • Re: Kerberos NTLM
    ... I'll assume it was just a typo, and you do have an SPN registered for your IIS computer account as HTTP/server1.domain.com. ... you want to follow some basic Kerberos troubleshooting steps (like making sure the time is correct on both client and server). ... Joseph T. Corey MCSE, Security+ ...
    (microsoft.public.windows.server.active_directory)