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.

Solution 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.

Solution 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

Solution 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.

Solution 4

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/

Solution 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.

View more solutions

Comments

[TheEdge]

We don't run an internal DNS server. But to eliminate this as a problem are you saying I should "ping -a x.x.x.x" or is there another way to determine if there are duplicates?

[Dylan Smith]

I'm no expert but I thought SPNs and SSPI was a Kerberos thing? Are you sure you're not using Kerberos?

[TheEdge]

@DylanSmith Not that I can see..... When I ran SP in SQL Server (Forget name now) it all came up as NTLM. Do you know how I check?

[Eduardo]

I know the question is old, so save time and run this tool: microsoft.com/en-us/download/…

[AlbatrossCafe]

did your answer on my client machine and SQL box plus ipconfig/release and ipconfig/renew on my client machine and it did not work for me ;(

[Dragos Durlut]

This was my problem. password changed. had my account running the app pool.

[Slogmeister Extraordinaire]

This problem only happened to me when I added a certificate to the SQL Connection. The certificate was issued to the FQDN, so when I connect to FQDN\Instance, it worked.

[Zach Smith]

Thanks for this answer - where is the .config file?

[Slogmeister Extraordinaire]

I had this same issue, but it wasn't on a cluster. I had changed the logon for the SQL Engine service to a domain account. I had to remove the MSSQLSvc/SERVER_FQNName:* SPNs from the computer account and then add them to the user account running the service.

[mcb2k3]

Oops, that wasn't quite it. SSMS did a switch on me when I wasn't looking and went back to my SQL Server account. But I finally tried switching from using a Microsoft account to log in locally to using a local account locally. That did the trick, and it seems to work now even if I log in using my PIN.

[Auspex]

Surely telling users not to login via Windows authentication because their Windows credentials aren't working is not a solution. If you have Integrated Security set true, it's probably because you want to use it (and in any case, if your database is set up for integrated security, the odds are good that you can't login without Windows credentials--my own account has no passworded logon)

[Geoff Dawdy]

How do you remove that if the connection is through SSMS?

[Auspex]

Well, clearly 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!

[Matt Shepherd]

@GeoffDawdy my answer below may help? It was due to an expired password, requiring me to change my password, log out and back in and then everything worked as normal.

[youcantryreachingme]

Similar experience, SQLServer 2016 on VM. Not sure why connections began to fail. Restart of VM fixed it without needing to restart client.

[youcantryreachingme]

Ditto SQL Server 2016.

[Hydrargyrum]

I switched an MS SQL Server instance from running using NT Service\MSSQLSSERVER to running as a Managed Service Account. After doing so, SSMS could connect to the database locally on the server, but not remotely from my laptop. Fixing the SPNs addressed the problem.

[Eduardo]

Save yourself time and run this tool: microsoft.com/en-us/download/…

[Tomas Hesse]

I had the same message. I was trying to connect with IP so I did as stackoverflow.com/users/8568873/s3minaki, i.e steps 1-6 but I enabled TCP/IP instead of Named Pipes. Also under IPALL I cleared TCP Dynamic port and set TCP Port instead. Be sure no other instance runs this port or the instance won't restart. I also needed an SQL user, Windows Authentication wont work. In SQL Manager you connect with x.x.x.x\instancename,portnr. ie 127.0.0.1\SQLEXPRESS,1433

[shary.sharath]

when I had this issue, I logged out and login back. Resolved the issue.

[BetterLateThanNever]

Similar issue. This helped my to look back at my actions. TQ

[Spivonious]

Giving the account sysadmin privileges and using SQL auth instead of Windows is not a solution.

[Erik Pearson]

I had a similar issue (iMac vpn with a Windows VM). I solved it by adding my work's DNS servers to my Mac's Wi-Fi network settings. I'm guessing there is a better way, but this got it working for me.

[Craig - MSFT]

This fixed the issue for me. SPNs were registered on the wrong user object in Active Directory. The Kerberos Configuration Manager for SQL Server fixed it two clicks!

[BenderBoy]

This is what I did after comparing setspn -L <Hostname> with a server that worked. Turned out all instances that worked had no SPN registered. I don’t really know what I’m doing, but apparently without those SPNs registered, NTLM can be used. Thanks!

[BenderBoy]

Be aware that this is not really a solution if you want to use Kerberos instead of NTLM, as you apparently should: serverfault.com/a/384721. In fact, this solution basically turns off Kerberos auth.

[Zarepheth]

I actually set mine to "TCP/IP". I don't know if the act of changing it fixed the problem or the specific setting for my network situation...

[Todd Wilcox]

Another way to fix it when it's an SPN mismatch like this is to change the SQL service accounts back to the default virtual service accounts as outlined here: docs.microsoft.com/en-us/sql/database-engine/configure-windo‌​ws/… More information about SPNs is available here: docs.microsoft.com/en-us/previous-versions/windows/it-pro/….

[Abubakar Riaz]

I whitelisted the Active directory (AD) IPs and SQL Server connectivity issue was resolved. I also verified from above mentioned command 'Test-ComputerSecureChannel -verbose' After IP whitelisting true flag was received. By Whitelisting I mean to allow IP over VPN or firewall. The repair command did not worked for me.

[Jelphy]

The issue was time skew for me. Domain controller and database server 45 minute time skew. Setting correct time fixed it.

[Jelphy]

Same problem for me. Test environment with no NTP running. Time skew produced this error message.

[Phantom]

got same issue after changing my password. You saved my hour. Thanks!

[RonJohn]

Sadly, this did not solve the problem for me.

[aag]

I can log into my domainless SQL server through Windows authentication, but ONLY from a local account of the client. If the account of the client is set to Microsoft Account (cloud account), I get the failure message referenced at the top of this thread

[Alexander Higgins]

This ended up being my issue as well. I ran the Microsoft® Kerberos Configuration Manager for SQL Server utility which detected which SPNs had been corrupted and used the tool to generate the script s to fix the issue. microsoft.com/en-us/download/details.aspx?id=39046

[Cryptc]

In my case the Domain Controller VM froze and had to be reset.

[Ajay Meda]

That's a nice way of explaining it.

[Ian Kemp]

This works. Why does this work???

[Ian Kemp]

Note that Test-ComputerSecureChannel does not exist in PowerShell Core.

[Honza P.]

No logout/login was required for me. It was enough to lock / unlock widows session and the error vanished :-).