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 15:06:41 +0100

Seems that you do require an SPN in order for a client to
connect to SQL via Kerberos, I've just checked this and
without the SPN the client uses NTLM and you see these
errors in the system log of the client (with Kerberos logging
enabled). Are these the kind of errors you see on your clients ?

Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 4
Date: 17/04/2003
Time: 15:01:57
User: N/A
Computer: JASTEST
Description:
The function InitializeSecurityContext received a Kerberos Error Message:
         on logon session
 Client Time:
 Server Time: 14:0:42.0000 4/17/2003 (null)
 Error Code: 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
 Client Realm:
 Client Name:
 Server Realm: DOMSQL.COM
 Server Name: krbtgt/DOMSQL.COM
 Target Name: MSSQLSvc/SQLNLB01.DOMSQL.COM:1433@DOMSQL.COM
 Error Text:
 File:
 Line:
 Error Data is in record data. 

-- 
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 w/ SQL and WIN2000
    ... Could you tell me what you changed in the SPN registration? ... > I found the following KB useful in troubleshooting Kerberos issues ... > the SPN for the SQL Server which made me go back and edit the ... > Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Re: Delegation problems
    ... duplicate SPN so I didn't think that was it, but it seemed like one of the ... When the SQL server was initially setup (by a FORMER ... administrator) he used his account as the service account for SQL ... What I am seeing in is that when a Kerberos TGS-REG is submitted I am ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: kerberos logon to IP address
    ... So we are going to create SPN and enable kerberos for the alias. ... the Vista client not even asks for TGT - once again as observed by using Wireshark ...
    (microsoft.public.windows.vista.security)
  • Re: Delegation problems
    ... What I am seeing in is that when a Kerberos TGS-REG is submitted I am ... The service name in the request is the same as is defined for the SPN ... negotiate NTLM authentication which can't be passed to the SQL Sever. ... What is the SPN you have on the service account? ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • RE: Windows Authentication in a NT domain vs in an Active Director
    ... Kerberos to SQL Server if you're using Windows Authentication. ... the client will use NTLM. ...
    (microsoft.public.sqlserver.security)
Loading