[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3033
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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