The target principal name is incorrect. Cannot gen

Question

I am struggling to get a SQL Server connection from machine A to machine B which is running the SQL Server.

I have Googled extensively and all the things I have found have not worked. Nor do they lead you step by step through the process of solving this.

We are not using Kerberos, but NTLM where configured.

The machines involved are (xx is used to obscure some of the machine name for security purposes):

• xxPRODSVR001 - Windows Server 2012 Domain Controller
• xxDEVSVR003 - Windows Server 2012 (This machine is generating the error)
• xxDEVSVR002 - Windows Server 2012 (This machine is running SQL Server 2012)

The following SPN's are registered on the DC (xxPRODSVR001). I have obscured the domain with yyy for security purposes:

Registered ServicePrincipalNames for CN=xxDEVSVR002,CN=Computers,DC=yyy,DC=local:

            MSSQLSvc/xxDEVSVR002.yyy.local:49298

            MSSQLSvc/xxDEVSVR002.yyy.local:TFS

            RestrictedKrbHost/xxDEVSVR002

            RestrictedKrbHost/xxDEVSVR002.yyy.local

            Hyper-V Replica Service/xxDEVSVR002

            Hyper-V Replica Service/xxDEVSVR002.yyy.local

            Microsoft Virtual System Migration Service/xxDEVSVR002

            Microsoft Virtual System Migration Service/xxDEVSVR002.yyy.local

            Microsoft Virtual Console Service/xxDEVSVR002

            Microsoft Virtual Console Service/xxDEVSVR002.yyy.local

            SMTPSVC/xxDEVSVR002

            SMTPSVC/xxDEVSVR002.yyy.local

            WSMAN/xxDEVSVR002

            WSMAN/xxDEVSVR002.yyy.local

            Dfsr-12F9A27C-BF97-4787-9364-D31B6C55EB04/xxDEVSVR002.yyy.local

            TERMSRV/xxDEVSVR002

            TERMSRV/xxDEVSVR002.yyy.local

            HOST/xxDEVSVR002

            HOST/xxDEVSVR002.yyy.local

Registered ServicePrincipalNames for CN=xxDEVSVR003,CN=Computers,DC=yyy,DC=local:

            MSSQLSvc/xxDEVSVR003.yyy.local:1433

            MSSQLSvc/xxDEVSVR003.yyy.local

            Hyper-V Replica Service/xxDEVSVR003

            Hyper-V Replica Service/xxDEVSVR003.yyy.local

            Microsoft Virtual System Migration Service/xxDEVSVR003

            Microsoft Virtual System Migration Service/xxDEVSVR003.yyy.local

            Microsoft Virtual Console Service/xxDEVSVR003

            Microsoft Virtual Console Service/xxDEVSVR003.yyy.local

            WSMAN/xxDEVSVR003

            WSMAN/xxDEVSVR003.yyy.local

            TERMSRV/xxDEVSVR003

            TERMSRV/xxDEVSVR003.yyy.local

            RestrictedKrbHost/xxDEVSVR003

            HOST/xxDEVSVR003

            RestrictedKrbHost/xxDEVSVR003.yyy.local

            HOST/xxDEVSVR003.yyy.local

Now if only the SQL Server error message was more descriptive and told me what principal name it was trying to connect to I might be able to diagnose this.

So can anyone step me through how to solve this one or can you see anything in what I have provided that is wrong?

I would be happy to generate more debug info, just tell me what you need.

Answer 1

Since I landed here when looking for a solution to my own problem, I'll share my solution here, in case others land here as well.

I was connecting fine to SQL Server until my machine was moved to another office on another domain. Then, after the switch, I was getting this error regarding the target principal name. What fixed it was connecting using a fully qualified name such as: server.domain.com. And actually, once I connected to the first server that way, I could connect to other servers using just the server name (without the full qualification), but your mileage may vary.

Answer 2

I had the same issue. I recently changed my windows password and my website was throwing the error. I tried to logout and login but not worked. Then I realized I configured my defaultappppol using my account in the "custom account" section and I configured the account once again using the new password. This did the magic!!! Please let me know your feedback on this solution.

Answer 3

I was getting the same error when trying through windows authentication. Sounds ludicrous but just in case it helps someone else: it was because my domain account got locked somehow while I was still logged in (!). Unlocking the account fixed it.

Answer 4

I had this problem on my sql server. I setspn -D mssqlsvc\Hostname.domainname Hostname then stoped and started my SQL server service.

I am thinking that just stopping and starting my sql service would have done it.

Answer 5

I had the same issue, but locking, and unlocking the machine worked for me. Sometimes, firewall issues will give errors.

I am not sure it will work for you or not, just sharing my experience.

Answer 6

The SSPI context error definitely indicates authentication is being attempted using kerberos.

Check the security event logs, if you are using kerberos you should see logon attempts with authentication package: Kerberos.

The NTLM authentication may be failing and so a kerberos authentication attempt is being made. You might also see an NTLM logon attempt failure in your security event log?

You can turn on kerberos event logging in dev to try to debug why the kerberos is failing, although it is very verbose.

Microsoft's Kerberos Configuration Manager for SQL Server may help you quickly diagnose and fix this issue.

Here is a good story to read: http://houseofbrick.com/microsoft-made-an-easy-button-for-spn-and-double-hop-issues/