Post reply

cannot generate sspi context for tcp ip

  • July 24, 2007 at 10:18 am

    #173985

    Hi,

    I get the sspi context error while making connections through sql client tools on a remote server while using tcp ip,  however if I use named pipes it is successfull.  The tcp ip is enabled and is set as a priority over named pipes.  I can ping the server and get the right ip address etc. 

    Can anyone help me figure out to see what is going on?

    thanks

    KR

  • July 24, 2007 at 1:22 pm

    #722031

    Why not just used Named Pipes?

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • July 24, 2007 at 1:56 pm

    #722043

    Ping implements ICMP protocol which is different from TCP/IP ports for SQL Server (1433 default), Kerberos (88), Named Pipes (445) and NTLM

    Please read this article

    http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

    SQL Protocols

    It says that Kerberos is used over TCP/IP if SPN (Server Principal Name) exists and NTLM is used over Named Pipes for Integrated Authentication. This article also has links to your message cause analysis:

    [6] Can not generate SSPI Context.

    This is typical Kerberos authentication failure, there are various situations that can trigger this error. see blog: http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

    http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

    The major reason is due to the Credential Cache( is used by Kerberos to store authentication information, namely the TGT and session ticked is cached so that can be used during their lifetime.)

    The most general workaround is: clean up credential cache by using "klist.exe -purge" or kerbtray.exe or just reboot machine.

    See more detail about various cause and solution in http://support.microsoft.com/kb/811889.

    Regards,Yelena Varsha

  • July 24, 2007 at 3:32 pm

    #722075

    I did go through all the articles mentioned above and then some, but somethings are not clear to me:

    All the above seem to indicate that a missing or incorrect spn may cause the error message to pop up.  Also, it seems that only Kerberos will generate this type of erros.  My understanding is that Kerberos is used if there is a valid spn, otherwise it defaults to NTLM, and so why is it not happening in this case. I take it from the error messages that Kerberos authentication is what is being used. 

     

    Specifically, in the article where the creditial cache is used by Kerberos needs to be cleared and can be done by reboot.  We have already done that and in fact the server was completly rebuilt and it still has the same problem.

     

  • July 25, 2007 at 12:20 am

    #722151

    You can check if its a NTLM or Kerberos authentication using the DMV sys.dm_exec_connections. What you say is right this is due to a SPN issue first check how default connection is mde you can forde to make kerboros authntication with windows.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 25, 2007 at 8:45 am

    #722324

    To check the sys.dm_exec_connections, I would first need to make a successful  remote connection which would have to be named pipes(NTLM).  Is the authentication when connecting locally always NTLM?, because that is what it shows when I connect through the client tools on the box? 

    I am not exactly sure what you mean by default connection.  Could you please give me some more information about that?

     

    Thanks

    KR

  • July 26, 2007 at 3:31 am

    #722505

    One of the most common reasons for getting a message about 'Unable to create SSPI context' is lack of permission to create the SPN.

    Make sure the account that is running SQL Server has the following account attributes.  This should allow the SPN to be created automatically.

    1) Account is trusted for delegation

    2) Read servicePrincipalName

    3) Write servicePrincipalName

    Alternatively a SPN can be created manually using SETSPN or ADSI Edit, but the person doing this must have be a member of the Domain Admins group.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • July 26, 2007 at 9:02 am

    #722600

    I agree with the above assumptions.  However,  here are a few interesting things:  I found that the account is used on two sql servers and one works fine with tcp-ip(server A)  and the other does not(Server B). However when I query through sys.dm_exec_connections on  server A ,  I come with NTLM,  so I am guessing that there is no spn set up for this server, so sql is using the NTLM over tcp-ip protocol in this case.  So  it would only be logical that this is not happening on server B because there is manual spn for it only incorrect?  I am not a member of the domain admin and so I am asking our network admin to let me know the status of spn on these two servers. 

    Thanks

    KR

  • July 26, 2007 at 10:08 am

    #722629

    I just heard back from out Network admin saying that spn was never manually registered on the servers.  So I am back to square one.  Why would server A work and not server B with the exact same settings.    Any other ideas of why this would happen other than the SPN issue?

    Thanks

    KR

  • July 27, 2007 at 1:27 pm

    #723032

    You need the account to have access on AD to read and write SPN at the very least...

    This will generate an SSPI error on SQL Server 2000 but on SQL Server 2005 it uses NTLM

  • July 27, 2007 at 1:34 pm

    #723034

    Bothe server A and B are on sql 2005 service pack1 -  That is why I am puzzled, one server moves to NTLM, the other just produces an error.

    KR

  • August 10, 2007 at 7:51 pm

    #726263

    I just went through this same "Cannot generate SSPI context" scenario again.  This problem has haunted me on and off for over 5 years.  Per the Microsoft article http://support.microsoft.com/kb/811889, i granted the SQL Service account SELF rights to its own attributes listed below:

    • Read servicePrincipalName
    • Write servicePrincipalName

    This was all I did... problem solved.

  • August 11, 2007 at 11:24 am

    #726290

    Yes, that would do it - I realize that if I give the account rights to register the spn it will work - through kerberos.   My question is why is it failing rathern than falling back to NTLM like the other server?  Why are two identical servers behaving differently?

    Thanks

    KR

  • August 13, 2007 at 7:19 am

    #726420

    1. How identical is identical?

    2. If you reverse the two servers completely - by transferring all content of server A to server B, and then transferring all original content of server B to server A, does it really work differently?

    I suspect that there are subtle differences between the two servers - for instance, the permissions were granted on one server but not the other?

  • August 13, 2007 at 8:32 am

    #726460

    Identical as far as the sql server set and permission go - and the network people say identical as far as the operating systems go.   Of course I realize that there might be a difference that we are not aware of, but I have not found it yet.

     

    Thanks

    KR

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply