The target principal name is incorrect. Cannot generate SSPI context

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

I had this problem with an ASP.NET MVC app I was working on.

I realized I had recently changed my password, and I was able to fix it by logging out and logging back in again.

Answer 2

Try setting Integrated Security=true to remove this param from the connection string.

---

IMPORTANT: As user @Auspex commented,

Removing Integrated Security will prevent this error, because the error occurs when trying to login with your Windows credentials. Unfortunately, most of the time, you want to be able to login with your Windows credentials

Answer 3

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

Since Kerberos authentication SQL Server's Windows Authentication relies on Active Directory, which requires a thrusted relationship between your computer and your network domain controller, you should start by validating that relationship.

You can quickly check that relationship, thru the following Powershell command Test-ComputerSecureChannel.

Test-ComputerSecureChannel -Verbose

If it returns False, you must repair your computer Active Directory secure channel, since without it no domain credencials validation is possible outside your computer.

You can repair your Computer Secure Channel, thru the following Powershell command:

Test-ComputerSecureChannel -Repair -Verbose

If the above doesn't work (because your domain credentials don't work because the machine isn't trusted) you can use NETDOM RESET instead from an elevated cmd.exe (not PowerShell) prompt:

NETDOM RESET %COMPUTERNAME% /UserO:domainAdminUserName /Password0:* /SecurePasswordPrompt

_{(Yes, the command-line arguments really do have an O (Capital-"Oh", not zero 0). The /Password0:* /SecurePasswordPrompt option will use a credential popup instead of having you put your password directly in the command-line, which you must never do).}

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/

Answer 4

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 5

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 6

Just to add another potential solution to this most ambiguous of errors The target principal name is incorrect. Cannot generate SSPI context. (.Net SqlClient Data Provider) :

Verify that the IP that is resolved when pinging the SQL Server is the same as the one in the Configuration Manager. To check, open SQL Server Configuration Manager and then go to SQL Server Network Configuration > Protocols for MSSQLServer > TCP/IP.

Make sure TCP/IP is enabled and in the IP Addresses tab, make sure that the IP that the server resolves to when pinging is the same one here. That fixed this error for me.

Answer 7

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 8

I just ran into this and fixed it by doing 2 things:

1. Granting read/write servicePrincipalName permissions to the service account using ADSI Edit, as described in https://support.microsoft.com/en-us/kb/811889

2. Removing the SPNs that previously existed on the SQL Server computer account (as opposed to the service account) using

   setspn -D MSSQLSvc/HOSTNAME.domain.name.com:1234 HOSTNAME
   

   where 1234 was the port number used by the instance (mine was not a default instance).

Answer 9

Login to both your SQL Box and your client and type:

ipconfig /flushdns
nbtstat -R

If that doesn't work, renew your DHCP on your client machine... This work for 2 PCs in our office.

Answer 10

This is usually due to missing, incorrect or duplicated Service Principle Names (SPNs)

Steps to resolve:

1. Confirm what AD account SQL Server is using
2. Run the following command in Powershell or CMD in administrator mode (service account should not contain the domain)

setspn -L <ServiceAccountName> | Select-String <ServerName> | select line

3. Make sure the returned output contains an SPN which is fully qualified, no fully qualified, with a port and without a port.

   Expected Output:

   Registered ServicePrincipalNames for CN=<ServiceAccountName>,OU=CSN Service Accounts,DC=<Domain>,DC=com: 
   MSSQLSvc/<ServerName>.<domain>.com:1433
   MSSQLSvc/<ServerName>:1433                                           
   MSSQLSvc/<ServerName>.<domain>.com
   MSSQLSvc/<ServerName>
   

4. If you don't see all of the above, run the following command in PowerShell or CMD in admin mode (make sure to change the port if you don't use default 1433)

SETSPN -S  MSSQLSvc/<ServerName> <Domain>\<ServiceAccountName> 
SETSPN -S  MSSQLSvc/<ServerName>.<Domain> <Domain>\<ServiceAccountName> 
SETSPN -S  MSSQLSvc/<ServerName>:1433 <Domain>\<ServiceAccountName> 
SETSPN -S  MSSQLSvc/<ServerName>.<Domain>:1433 <Domain>\<ServiceAccountName>

5. Once above is complete it normally takes a few minutes for DNS propagation

Also, if you get a message about duplicate SPNs found, you may want to delete them and recreate them

Answer 11

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 12

In my case, restarting SQL Server 2014 (on my development server) fixed the issue.

Answer 13

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.

Answer 14

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 15

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 16

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 17

In my situation I was trying to use Integrated Security to connect from a PC to SQL Server on another PC on a network without a domain. On both PCs, I was signing in to Windows with the same Microsoft account. I switched to a local account on both PCs and SQL Server now connects successfully.

Answer 18

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 19

In my Case since I was working in my development environment, someone had shut down the Domain Controller and Windows Credentials couldn't be authenticated. After turning on the Domain Controller, the error disappeared and everything worked just fine.