Re: cannot generate sspi context when server in mixed authenticati

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

A domain admin will need to create an SPN for the SQL Server service
manually using SetSPN or ADSI (I think)

Local system or domain admin starting sql does this automatically...domain
user does not

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com


"DBA72" <DBA72@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F01518D8-9430-49AB-95AC-076BF1C5CF84@xxxxxxxxxxxxxxxx
It is starting with a domain user account.

"Kevin3NF" wrote:

What is SQL Server service starting as...local system, domain user or
domain
admin?

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com


"DBA72" <DBA72@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:65AFAA64-4847-4034-8745-211ED18ACB0E@xxxxxxxxxxxxxxxx
We have a situation for which I have been trying to find an explanation
for
over a week. A windows 2005 SP1 server which previously ran in Windows
authentication only suddenly caused "cannot generate sspi context"
errors
after we switched it to mixed authentication mode.

-SQL service is running with domain account not trusted for delegation
-We have checked for invalid SPNs in the domain and there are none
registered for the SQL Service on this machine
-TCP/IP protocol is enabled on the server
-Named Pipes connections work fine
-After switching back to Windows authentication and clearing the ticket
cache the problem dissapears

If I understand correctly. The default authentication protocol used
over
tcp/ip when connecting to SQL Server is Kerberos but if the client
cannot
find a valid SPN for the SQL Service on the server then it should fall
back
to NTLM. For some reason however, this is not happening as it should.






.

Relevant Pages

  • RE: Msg 15404: Could not obtain information about user
    ... not register the Service Principal Name (SPN) for the SQL Server service. ... authentication to fall back to NTLM instead of Kerberos. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL account rights
    ... SQL Server account to be a domain admin. ... configure all SQL server functions. ...
    (microsoft.public.sqlserver.security)
  • Re: 2005 Enterprise sp 2 install fails - 11009
    ... What priveleges does the login need if I can't get a full domain admin? ... Once you finished installing SQL Server 2005 SP2, ... clustered instance before you apply SQL Server 2005 service pack. ...
    (microsoft.public.sqlserver.setup)
  • RE: SQL Server Service Account
    ... domain user that is a member of the local administrators group on the SQL ... the service accounts in SQL Server, please refer to the article in Books ... >Thread-Topic: SQL Server Service Account ... Do I have to make it a domain Admin account, ...
    (microsoft.public.sqlserver.setup)
  • Re: restricting domain administrator access
    ... because a domain admin can simply add themselves to a group with access or ... The article is about clustered servers but same applies for non clustered. ... Jasper Smith (SQL Server MVP) ... "Dan D." ...
    (microsoft.public.sqlserver.security)