Solved

SQL Server Native Client 10.0 SQL Server Network Interfaces: The target principal name is incorrect

Posted on 2011-09-08
15
2,619 Views
Last Modified: 2012-05-12
From a windows 2008 R2 machine, using SSMS to connect to my sql server (2008), the sa account works but using windows authentication fails. WIndows authentication keeps returning

[SQL Server Network Interfaces: The target principal name is incorrect & Cannot generate SSPI context message

The problem is that this happens only on this one server. I tried from 2 other windows servers, and they all connect fine using windows authentication. The other windows servers are also sql servers.

I am not sure why SSMS on this server is producing the problem but I need to resolve it. This server does have sql server installed with 2 instances, but I can't see how that would affect SSMS

0
Comment
Question by:iamuser
  • 8
  • 7
15 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36503810
This is due to the integrated security, and it is a problem w/the kerberos delegation of the SPN over tcp/ip.  This is a good reference for the problem, and resolution:
http://support.microsoft.com/kb/811889
0
 

Author Comment

by:iamuser
ID: 36505681
I ran "setspn.exe -L sqladmin\domain user account" and the results show that nothing is registred with that account. But i know that i have 3 sql servers using that account to run sql server services & agent services.

Is the lack of SPN under the domain user account the problem here?
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36505869
I believe the SPN should be registered to the sql server service account:  http://technet.microsoft.com/en-us/library/bb735885.aspx
We had a very similar problem not long ago, and I resolved it after having referenced this:  http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx


>>>
To verify that Kerberos authentication is being used, you may query the sys.dm_exec_connections DMV and look under the auth_scheme column, e.g.
 select auth_scheme from sys.dm_exec_connections where session_id=@@spid
 If Kerberos is being used, then it will display “KERBEROS”.
 I should also mention that if the instance automatically registered an SPN at startup, then it will unregister it when the instance is stopped.
>>>
0
 

Author Comment

by:iamuser
ID: 36506323
Right now my sql services spn is pointed to different account and not the account that is being used as the service account for sql services. I would have to de-register that account and re-register the spn to the account that I'm using for the sql service. Am I understand this correctly?

I ran the select auth_scheme from sys.dm_exec_connections where session_id=@@spid
 on my sql server and I get NTLM and not kerebos

0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36506346
That is precisely what I receiced a couple weeks back, same problem with the kerberos resolution. Once the spn was corrected, we were good.  If I remember correctly,I did have to restart the agent.
0
 

Author Comment

by:iamuser
ID: 36506375
- So I will have to de-register the current spn on the target sql server.
- re-register the spn to the logon account I have for the sql services on the target server

- Do i have to do this on the client side as well (where ssms is ) or is this only for the target server


0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36506392
Just the server.
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:iamuser
ID: 36506401
great let me try it
0
 

Author Comment

by:iamuser
ID: 36506468
it worked but it still shows up as NTLM and not kereobs
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36506489
I am on my blackberry right now, so I can't really do much. I don't remember how long after our change that I checked that it wasn't NTLM anymore, but this effort did resolve our failure.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36506726
Does the SSPI error persist?
0
 

Author Comment

by:iamuser
ID: 36510482
the sspi error is gone but I thought it should have changed to kerebos
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 36510694
Well, I am pleased that we resolved the SSPI error.  I am uncertain about the change from NTLM to Kerberos.  I would review your logs, make sure everything is stable, no errors reporting, and then maybe just research a little more on the state of the actual connection being made, using tcp/ip and windows authentication.  Both types are made (NTLM, KERBEROS), so it may be completely acceptable that this is what you are seeing.

http://blogs.msdn.com/b/karthick_pk/archive/2009/01/23/kerberos-authentication-in-sqlserver.aspx
http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
0
 

Author Closing Comment

by:iamuser
ID: 36536216
Great answers, really resolved my problem
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36536226
Excellent!  Glad to have helped.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now