cannot generate sspi context for tcp ip

Question

Post reply

cannot generate sspi context for tcp ip

Karthika Raman

SSCrazy

Points: 2520
More actions
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

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

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

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

Why not just used Named Pipes?

Cheers,

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

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

Karthika Raman SSCrazy Points: 2520 More actions · Answer 3

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.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

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

Karthika Raman SSCrazy Points: 2520 More actions · Answer 5

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

EdVassie SSC Guru Points: 60479 More actions · Answer 6

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

Karthika Raman SSCrazy Points: 2520 More actions · Answer 7

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

Karthika Raman SSCrazy Points: 2520 More actions · Answer 8

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

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 9

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

Karthika Raman SSCrazy Points: 2520 More actions · Answer 10

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

jseashore-85696 SSC-Addicted Points: 444 More actions · Answer 11

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.

Karthika Raman SSCrazy Points: 2520 More actions · Answer 12

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 13

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?

Karthika Raman SSCrazy Points: 2520 More actions · Answer 14

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