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.

Ula

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? – Ula

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

@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? – Ula

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

Answer 1

Try logging out and logging back in again.

I had this problem with an ASP.NET MVC app I was working on after I changed my Windows password. Relggging fixed it.

Swaim

Same when I got this error in SSMS, reboot and login again, done. – Ningpo

Answer 2

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 trusted 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/

Clubhaul

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! – Matriculation

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. – Cleopatracleopatre

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

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

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.

Waterfront

I needed to change my password. – Crichton

Answer 4

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

Deafanddumb

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

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! – Margueritamarguerite

@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. – Adley

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

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.

Heterosexual

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. – Alonzoaloof

This works. Why does this work??? – Dissolvent

@IanKemp It works because he's using a local account, rather than an Active Directory account. This error is specific to AD – Margueritamarguerite

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.

Flews

Thanks Alex, way more useful answer than "Don't use integrated security" which is highly upvoted here... – Ebro

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"

Linguist

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. – Pipkin

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

Trehala

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. – Hangup

Answer 9

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

Villainage

Ditto SQL Server 2016. – Seism

For my SQL Server 2019+Windows Server 2022 VM, I fixed it by restarting Windows, but maybe restarting just the SQL Server service would have done the trick. I did try the "Test-ComputerSecureChannel -Verbose" solution, but it returned "True", which is nominal, so I resorted to a machine reboot. – Charitacharitable

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

Midsummer

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: learn.microsoft.com/en-us/sql/database-engine/configure-windows/… More information about SPNs is available here: learn.microsoft.com/en-us/previous-versions/windows/it-pro/…. – Topeka

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 – Sherillsherilyn

Answer 11

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.

Bidet

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 ;( – Sumerology

Answer 12

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.

Lentz

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

Answer 13

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.

Pontus

Answer 14

In case anyone is wondering, I untangled the MS terminology:

Target = (active directory) target

Active directory target = target server running the domain controller

Domain controller = server that verifies your login information

Principal name = your windows username

SSPI = security support provider interface

Security support provider interface = software interface that manages "authenticated 
communications" and allows SSPs like TLS to allow SSL, among others

SSP = security support provider (SSPI implementation)

TLS/SSL = you should already know this
 

= Can't verify your password.

Swaim

That's a nice way of explaining it. – Ishmaelite

Answer 15

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.

Gambit

Answer 16

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.

Drat

Answer 17

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.

Solid

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. – Furl

Answer 18

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.

Boethius

Answer 19

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

Ballerina

Answer 20

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.

Transformation

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

Answer 21

I ran into this today and wanted to share my fix, since this one is simply overlooked and easy to fix.

We manage our own rDNS and recently redid our server naming scheme. As part of that, we should have updated our rDNS and forgot to do this.

A ping turned up the correct hostname, but a ping -a returned the wrong hostname.

Easy fix: change the rDNS, do an ipconfig /flushdns, wait 30 seconds (just something I do), do another ping -a , see it resolving the correct hostname, connect ... profit.

Belsen

Answer 22

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.

Transgression

Adding a host entry solve my problem. Not sure why... – Pixilated

Answer 23

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.

Chickenlivered

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. – Furl

Answer 24

I was trying to connect to a VM running SQL Server 2015 from my laptop in a Visual Studio 2015 Console App. I run my app the night before and it is fine. In the morning I try to debug the app and I get this error. I tried ipconfig/flush and release + renew and a a bunch of other garbage, but in the end...

Restart your VM and restart the client. That fixed it for me. I should have known, restart works every time.

Sumerology

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

Answer 25

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.

Tupi

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! – Gratianna

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. – Gratianna

Answer 26

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.

Thing

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... – Diaphragm

Answer 27

In my case, the problem was setting up DNS on the wifi. I removed the settings, and left them empty, and worked.

Divulsion

Answer 28

Make sure that "Named Pipes" are enabled from "SQL Server Configuration Manager". This worked for me.

1. Open "SQL Server Configuration Manager".
2. Expand "SQL Server Network Configuration", from the list on the left.
3. Select "Protocols for [Your Instance Name]".
4. Right click on "Named Pipes", from the list on the right.
5. Select "Enable"
6. Restart your Instance service.

Argive

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 – Volleyball

Answer 29

Another niche to this issue caused by network connections. I connect via windows VPN client and this issue popped up when I switched from Wifi to a wired connection. The fix for my situation was to manually adjust the adapter metric.

In powershell use Get-NetIPInterface to see all of the metric values. The lower numbers are lower cost and so they are preferred by windows. I switched the ethernet and VPN and the credentials got where they needed to be for SSMS to be happy.

To configure the Automatic Metric feature: In Control Panel, double-click Network Connections. Right-click a network interface, and then select Properties. Click Internet Protocol (TCP/IP), and then select Properties. On the General tab, select Advanced. To specify a metric, on the IP Settings tab, select to clear the Automatic metric check box, and then enter the metric that you want in the Interface Metric field.

Source: https://learn.microsoft.com/en-us/troubleshoot/windows-server/networking/automatic-metric-for-ipv4-routes

Pumpkin

Answer 30

I had the same problem while using face recognition to logon to Windows 10. The workaround is to hold shift and right-click on the SSMS and use Run As Different User and provide username with password.

Kip

Answer 31

I have been unable to solve this in a satisfactory manner. If I log in from a client as a local Windows account, Windows authentication works without a glitch. If I however attempt to log in from a Microsoft account (which is preferable for me because of the synchronization features), I get the "incorrect principal" message. No record of the failed login appears in the MS-SQL logs, implying that the failure occurs very early.

Postnatal

Answer 32

I too had this problem on SQL Server 2014 while logging with windows Authentication, to resolve the issue i have Restarted my server once and then try to login, it worked for me.

Understructure

Answer 33

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.

Scriptwriter

Answer 34

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

Supervisor

Answer 35

Seems to be issue is related to DNS Server. To resolve this issue change the IP Address to ComputerName.

Example: Change the value "10.0.0.10\TestDB" to "YourcomputerName\TestDB"

Timbered

Answer 36

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.

Byzantine

Answer 37

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.

Codicodices

Answer 38

Had only one user who got this error on only one SQL Server. Turns out he had stored an old password in the Control Panel - Credential Manager under Windows Credentials for the server name. Removed the stored credential and it worked.

Bin

Answer 39

I had this problem when trying to connect to my SQL Server 2017 instance via L2TP VPN on a domain-joined Windows 10 machine.

The problem ended up being in my VPN settings. In the security settings, in Authentication, using EAP-MSCHAPv2 and in the Properties dialog, I had selected Automatically use my Windows logon name and password (and domain if any).

I turned this off and then re-connected my VPN and then I was able to connect to SQL Server successfully.

I believe this was causing my SQL login (with Windows account security) to use Kerberos instead of NTLM, causing the SSPI error.

Roberto

Answer 40

My issue turned out to be so strange and simple:

• SQL Server Windows Service on ServerA (configured to run using DOMAIN\svcAccountA)
• SQL Server Windows Service on ServerB (configured to run using DOMAIN\svcAccountB)

Both DOMAIN\svcAccountA and DOMAIN\svcAccountB are service accounts in our Active Directory domain.

Even though all permissions were setup properly for DOMAIN\svcAccountA to connect to ServerB, a C# CLR (running as DOMAIN\svcAccountA) on ServerA could no longer connect to ServerB using a SqlConnection (same strange uninformative error message: The target principal name is incorrect. Cannot generate SSPI context).

The simple part? After rebooting ServerA, the SQL Server Windows Service would no longer start automatically! That was the clue to discovering that someone had changed the password for DOMAIN\svcAccountA and I had to correct the SQL Server Windows Service configuration here:

After correcting the password, the SQL Server Windows Service on ServerA started fine, and the C# CLR (running as DOMAIN\svcAccountA) on ServerA could now connect to ServerB using a SqlConnection.

Berchtesgaden

Answer 41

I had this problem and it grabbed my around 4-5 hours to find the root cause of this error message. Only difference is, i was getting this error while connecting DB from Visual Studio only, not direct via SQL Server Management Studio.

Reason behind error is, my application was hosted on local IIS and I had changed my system password one day ago, but didn't update it to IIS Application Pool of my hosted application.

I went to IIS, clicked on application pools, in pool list right click on appropriate app pool, go to "Advanced Settings", select "Identity" and changed its value (Domain\AccountName & New Password) under "Custom Account" and it solved my problem.

Thanks

Hardcore

Answer 42

In your SQL Server Firewall, open ports 1433, 4022, 135, 1434 for inbound and outbound. Works like magic for me.

Equiponderate

Answer 43

I had this issue when I changed SQL Server service user. When it happened on a main instance, following point 1 and two below fixed the problem, due to SPN not being updated.

I also had this issue when I changed a named instance service user. This new user was a domain account already in use by the main instance. I am not aware of what went wrong, but I fixed it this way:

1. I followed the above advice (see previous threads) to run the Microsoft® Kerberos Configuration Manager for SQL Server®
2. The tool discovered some issues and fixed them for me
3. The tool suggested that dynamic ports enabled on the named instance were not a good idea and I therefore ran my sql server configuration manager and:
4. configured the named instance to use a static port (number is not important as long as it's available). Path to configuration: Protocols for named instance, right click on the TCP/IP, properties, IP Addresses, clear all TCP dynamic ports content, set the port number of your choice to all TCP port properties.
5. Created an alias: sql native client configuration -> aliases -> new alias. The server is your database server name, the port is the one mentioned above, the alias name I chose was the same as the instance name without the server name (eg server1\sqlsrv2017: server = server1, alias = sqlsrv2017)
6. Restarted the instance service, as promped

Needless to say the the port must be cleared using the firewall, if this is enabled

Underneath

Answer 44

I had the same problem for SQL Server 2014 and all I had to do was to run the application as an administrador.

Smetana

Answer 45

Database in the connection string didn't exist. I thought it did but it hadn't been created yet.

Igenia

Answer 46

I found it, Michael Hotek answered it here in this MSDN post.

If the SQL Server is configured for Windows authentication only, then the only way to connect to it is by using your domain credentials. Since you don't have domain credentials on your laptop, you won't be able to connect. If you want to connect using a SQL Server login, then you need to change the security model for the instance to Windows and SQL Server authentication. Then you will be able to utilize a standard SQL Server login instead of your domain credentials. If that isn't possible, then a really simple work-around is to VPN into the office, then RDP into a machine where you have SQL Server tools installed. You are then logged into the domain, using your credentials, and can use the tools straight off the machine you RDP'd into.

It turned out that you have to use Mixed mode Auth if you aren't on a domain.

Claim

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 – Postnatal

Answer 47

I understand that there can be various different variety of reasons for this issue, in my case, my Active Directory user account was locked out (whilst I was logged in to that host VM), logging out then unlocking my AD account resolved this problem. Hope this helps others.

Scleroprotein

Answer 48

After reading all the answers posted and tried most of them I still couldn't solve this problem (not saying that they are not useful, but I didn't manage to make it work).

The solution that worked for me was to use the SQL authentication instead of the Windows one. In the server created as 'host' create a SQL user with log in access (there is a default one called 'sa', that you can change its password after a first log-in with Windows authentication) and then use this same user's credentials to log in from the others computers, that should make the connection.

Hope it helps someone!!

Discord

Answer 49

In my internal Blazor application, I appended the TrustCertificate property to the connection string:

data source=MyDbServer;initial catalog=Coasters;Integrated Security=true;TrustServerCertificate=True

As I said, this was an internal application, so I didn't have any security concerns about using this method.

Levee

Answer 50

I had this error when logging even though I had access to SSMS in Azure: Error: The target principal name is incorrect. Cannot generate SSPI context. (Framework Microsoft SqlClient Data Provider) Fix: I changed my login from Windows Authentication to "Azure Active Direct - Universal with MFA" when connecting to SSMS(from the dropdown). Give the Server name as well database name in options (Options -> connection properties(tab) -> connect to database(field) outcome: I was able to login successfully.

Prelude

Answer 51

Though problem mentioned is old but same problem would produce again if your try to use Mirroring/Replication using SSMS version >= 19 to 19.1 till today. Solution to this is to downgrade to SSMS version 18. Microsoft will always give surprise in every upgrade :)

Brisket

Answer 52

The easiest way to resolve this problem is to download and install the Kerberos Configuration Manager from Microsoft. The important part is that you have to run it from the server you are having problems connecting (not the target).

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/cannot-generate-sspi-context-error

Haymes

Answer 53

In my case, a different window credential was added for the server. I fixed the issue by removing the window credential for the server from the Credential Manager.

Steps:

• Search for Credential Manager in the search bar
• Goto CredentialManager/Windows Credentials Delete Check if there are any Windows credentials added for the server you are trying to connect
• Delete window credential if any exists for that server

Germaine

Answer 54

I ran into this issue today; In my case I had to change my windows domain password since it was expired, and I did it through our vpn app (sonicwall netextender) which resulted to this issue. our admin mentioned to always change the domain password using ctl+alt+del. and that solved this issue. hope this helps.

Gnni

Answer 55

I had the issue connected to an Amazon Hosted SQL server (known as an RDS) when I tried to connect just using an IP address.

I instead connected to it by name by first putting the address and name into my (Windows) hosts file.

I assume its something to with SSL wanting a name not just a number

Hyoscyamus

Answer 56

In my case, it turned out to be the account on the windows client machine. Windows has forced me to use a microsoft account to login to windows. Since the registered MSSQL server was using 'Windows Authentication' with the old local account that existed on both my windows client, and on the development server, it was now trying to use the new microsoft account, that did not exist on the server. Entering the 'sa' credentials instead, fixed the connectivity issue. Hope this helps someone.

Compassionate

Answer 57

I will add to the list of possible solutions if you log out and log back in it fixed it for me :)

Neckcloth

This worked for me, when nothing else did. – Meridel

Answer 58

Please check the permissions for mentioned login name in SQL server management studio make it sysadmin checkbox tick and then make Integrated Security=False in the .config file.

Fire 2 commands on the client machine

1. ipconfig /flushdns

2. klist purge.

install kerbarose configuration manager on the client computer.

Finally, Restart client machine and main SQL server services. Run the application on the client machine. This Works 100% correctly.

Robertroberta

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

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

Answer 59

Integrated Security=false

Make this flag as false in webconfig connection string. It will work!

Indictable

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) – Margueritamarguerite