?
Solved

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

Posted on 2011-09-08
15
Medium Priority
?
2,828 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

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
 

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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

770 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