Solved

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

Posted on 2011-09-08
15
2,599 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
- 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
Comment Utility
Just the server.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:iamuser
Comment Utility
great let me try it
0
 

Author Comment

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

Expert Comment

by:dbaSQL
Comment Utility
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
Comment Utility
Does the SSPI error persist?
0
 

Author Comment

by:iamuser
Comment Utility
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
Comment Utility
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
Comment Utility
Great answers, really resolved my problem
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
Excellent!  Glad to have helped.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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

10 Experts available now in Live!

Get 1:1 Help Now