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

The issue seems to be a windows credentials issue. I was getting the same error on my work laptop with a VPN. I am supposedly logged in as my Domain/Username, which is what I use successfully when connecting directly but as soon as I move to a VPN with another connection I receive this error. I thought it was a DNS issue as I could ping the server but it turns out I needed to run SMSS explicitly as my user from Command prompt.

e.g runas /netonly /user:YourDoman\YourUsername "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe

Answer 2

Not at all an ideal solution, I just wanted to add this for future reference for anyone seeing this page:

I was having this issue trying to connect to a remote SQL Server instance using my domain account, trying the same thing on an instance hosted on a different machine worked fine.

So if you have the option to just use a different instance it may help, but this doesn't actually address whatever the issue is.

Answer 3

Check your clock matches between the client and server.

When I had this error intermittently, none of the above answers worked, then we found the time had drifted on some of our servers, once they were synced again the error went away. Search for w32tm or NTP to see how to automatically sync the time on Windows.

Answer 4

I'll add this here as it caught me out and may help someone else. Caveat emptor, I am not a windows person, but had to look at a scenario that included SQL server.

I downloaded the developer version of the full SQL Server product and installed it on Windows 10. All good for local connections, nothing for the remote client.

Tried many of the above but it eventually dawned on me that the Windows Authentication wanted to authenticate remoteclient\myuser and there was no way in a standalone Windows world to create a mechanism to authenticate against (As I understand it kerberos). The error message being "Cannot generate SSPI context".

Using SQL Authentication didn't appear to work either.

I eventually went back to SQL Server Express which has a combined mode and I could then use SQL Authentication from the remote clients.

Answer 5

I ran into a new one for this: SQL 2012 hosted on Server 2012. Was tasked to create a cluster for SQL AlwaysOn.
Cluster was created everyone got the SSPI message.

To fix the problems ran following command:

setspn -D MSSQLSvc/SERVER_FQNName:1433 DomainNamerunningSQLService

DomainNamerunningSQLService == the domain account I set for SQL I needed a Domain Administrator to run the command. Only one server in the cluster had issues.

Then restarted SQL. To my surprise I was able to connect.

Answer 6

I ran into a variant of this issue, here were the characteristics:

• User was able to successfully connect to a named instance, for example, connections to Server\Instance were successful
• User was unable to connect to the default instance, for example, connections to Server failed with the OP's screenshot regarding SSPI
• User was unable to connect default instance with fully qualified name, for example, connections to Server.domain.com failed (timeout)
• User was unable to connect IP address without named instance, for example, connections to 192.168.1.134 failed
• Other users not on the domain (for example, users who VPN to the network) but using domain credentials were able to successfully connect to the default instance and IP address

So after many headaches of trying to figure out why this single user couldn't connect, here are the steps we took to fix the situation:

1. Take a look at the server in the SPN list using
   setspn -l Server
   a. In our case, it said Server.domain.com
2. Add an entry to the hosts file located in C:\Windows\System32\drivers\etc\hosts (run Notepad as Administrator to alter this file). The entry we added was
   Server.domain.com Server

After this, we were able to successfully connect via SSMS to the default instance.