• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3143
  • Last Modified:

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

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
iamuser
Asked:
iamuser
  • 8
  • 7
1 Solution
 
dbaSQLCommented:
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
 
iamuserAuthor Commented:
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
 
dbaSQLCommented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
iamuserAuthor Commented:
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
 
dbaSQLCommented:
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
 
iamuserAuthor Commented:
- 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
 
dbaSQLCommented:
Just the server.
0
 
iamuserAuthor Commented:
great let me try it
0
 
iamuserAuthor Commented:
it worked but it still shows up as NTLM and not kereobs
0
 
dbaSQLCommented:
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
 
dbaSQLCommented:
Does the SSPI error persist?
0
 
iamuserAuthor Commented:
the sspi error is gone but I thought it should have changed to kerebos
0
 
dbaSQLCommented:
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
 
iamuserAuthor Commented:
Great answers, really resolved my problem
0
 
dbaSQLCommented:
Excellent!  Glad to have helped.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now