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

This Microsoft Tool is like Magic. Run it, connect it to the SQL server, and click Fix

The old version linked here worked on SQL server 2017.

Kerberos Configuration Manager for SQL Server https://www.microsoft.com/en-us/download/details.aspx?id=39046

Answer 2

I was testing out IPv6 on a cluster of PC's in an isolated network and ran into this issue when I reverted back yo IPv4. I had been play in the active directory, DNS and DHCP so have no idea what I prodded to break the Kerberos setup.

I retested the connection outside of my software with this useful tip to connect remote connectivity I found.

https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/

then after a brief search found this on the Microsoft website https://support.microsoft.com/en-gb/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message.

run the tool on the SQL server see if there are any issue if the status says error then hit the fix button that appears.

This resolved the problem for me.

Answer 3

I was logging into Windows 10 with a PIN instead of a password. I logged out and logged back in with my password instead and was able to get in to SQL Server via Management Studio.

Answer 4

I have tried all the solutions here and none of them have worked yet. A workaround that is working is to Click Connect, enter the server name, select Options, Connection Properties tab. Set the "Network protocol" to "Named Pipes". This allows users to remote connect using their network credentials. I'll post an update when I get a fix.

Answer 5

I'm running a Mickey Mouse testing system based on SQL.COM.

I ran setspn -T sql -F -Q */Servername (in this case SQL01) on both the machine I couldn't connect to and a machine I could. I then simply removed the additional entries in the problem machine and it all worked, e.g. setspn -D MSSQLSvc/SQL01.SQL.COM:1433 SQL01

Answer 6

I had this problem when accessing the web application. It might be due to i have changed a windows password recently.

This issue got resolved when i have updated the password for the app pool where i have hosted the web application.