9

The Issue

I have two SQL Server 2016 Standard Edition servers on the same domain. ServerA has two linked server connections setup to ServerB.

The first linked server connection is using a dedicated remote SQL Login security context. That one always was and still is working fine.

The second connection uses the "login's current security context" which is a Windows Authentication (AD) Login on ServerA. This was also working fine up until today.

Today out of nowhere, everyone who connects to ServerA and then tries to run a query that uses the second linked server connection, ends up getting hit with this error:

OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" returned message "Cannot generate SSPI context". Msg -2146893044, Level 16, State 1, Line 0 SQL Server Network Interfaces: The logon attempt failed

Also the users are able to directly connect to both ServerA and ServerB fine currently, and even execute queries against objects on ServerB directly, but when they try to run queries referencing the same objects on ServerB via the linked server on ServerA is the only time they get the above error, currently.


Relevant Information

This is what we currently see for SPNs and logs (sorry for all the obfuscation):

ServerB Successful SPN Registration SQL Logs:

ServerA SQL Logs

ServerB Successful SPN Registration SQL Logs:

ServerB SQL Logs

  • Notice both servers are successfully registering SPNs for themselves for both port 1433 and no port specified.

ServerA Registered SPNs:

ServerA Registered SPNs

  • I'm not sure what the second "CN=" line (second to last line) is but it literally says "CN=ServerASvc", but that's not the name of the SQL Service account. The SQL Service Account is "SQLServiceAccount1".
  • I'm also not sure why the last line has an SPN for just the server name "ServerA" (which we do have a Host / CName for in our DC), but seems redundant to the one of the previous lines.

ServerB Registered SPNs:

ServerB Registered SPNs

To recap my SPNs:

  • My ServerA's SQL Server Instance's Service Account (let's call it ServiceAccountA) has an SPN to trust its own server.
  • My ServerB's SQL Server Instance's Service Account (ServiceAccountB) has an SPN to trust its own server.
  • Delegation is setup for ServiceAccountA to be able to access the SPN for ServiceAccountB.

ServerA's service account is SQLServiceAccount1 and is also the same service account used on our DevSqlServer1.

ServerB's service account is SQLServiceAccount2 and is also the same service account used on our DevSqlServer2.

Running the query SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID returns TCP and KERBEROS on both ServerA and ServerB for me.

Also, different SQL Server Instance Service Accounts are running for each server. ServiceAccountA for ServerA, and ServiceAccountB for ServerB.

The night before this issue started happening, Windows updates were installed on ServerB (and a few of our other servers). Specifically this update November 10, 2020—KB4586830 (OS Build 14393.4046) which apparently states this:

After installing this update on domain controllers (DCs) and read-only domain controllers (RODCs) in your environment, you might encounter Kerberos authentication and ticket renewal issues. This is caused by an issue in how CVE-2020-17049 was addressed in these updates.

We tried rolling back the update, which invoked a restart of the server and things seemed to start working again, but then started breaking again for just our web server (a third / separate server), which hits our ServerA to query the linked server to ServerB.

Originally ServerA and ServerB were part of separate Windows Failover Clusters (ServerA1 & ServerA2, and ServerB1 & ServerB2). When that was the case, this DBA.StackExchange answer by Hannah Vernon sounded similar to what we experienced.

The only difference is we previously weren't letting the Service Accounts register / deregister the SPNs, when the issue started. Now we are letting them do so, and there haven't been any failover events, and we triple checked all SPNs are setup correctly currently. So I don't think that's our issue here.

At this point in time, both clusters are destroyed, and the secondary servers removed, yet the issue still persists with the individual servers (ServerA and ServerB).


Things We've Tried / Issue Progression

Logging out and logging back in after changing my AD password did not fix the issue, but physically restarting my machine temporarily did fix it for just me.

I ran setspn -l ServerA_SQLServiceAccountName and after I did that, I did see the SPN that was in my SQL Error Log. So I think Pinal Dave was right in my case.

Today I woke up and everyone on my domain is hosed again, myself included. I tried flushing the DNS on ServerA, and now we're receiving the error "Linked Server error: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" instead of my original error, when querying the linked server from ServerA.

It appears I am facing some sort of SPN / double-hop issue because when I RDP directly to ServerA and connect to my SQL Instance on ServerA (from within the RDP session - same account as I'm connecting to the instance when not RDP'ed in) and try querying the linked server, everything is all good. The question is, are my SPNs actually correct? (See current SPNs in the "Relevant Information" section above.)

We triple checked the SPNs, and the AD delegation properties of the Service Accounts. We're fairly confident all ducks are in order. We're fully down again: web server, SSMS, etc, no matter how we try to query the linked server, which I think is due to the default 10 hour ticket time of Kerberos lapsing, and it not correctly renewing a new instance of the same ticket. We're currently facing this error:

Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

We've been trying to troubleshoot using the Kerberos Configuration Manager and the only issue it found (this entire time) is a warning saying "Kerberos Configuration Manager reports that "TCP must be enabled to use Kerberos Authentication":

Kerberos Configuration Manager

We've checked TCP is enabled in both of our servers, so I'm not sure why it's saying this warning or how to troubleshoot it, and if it's our root issue or not.

Another thing worth noting is we've only been intermittently down for most of the time. That is, some users are able to successfully query the linked server when using our applications (web, mobile, etc) and some aren't. So if it was a true SPN issue, I would think no one would be able to query the linked server - an all or nothing situation. This definitely feels more like a random Kerberos issue (possibly related to the Windows Update mentioned earlier).

At this point, we've tried purge on our KLIST Kerberos tickets, ipconfig /flushdns both on the client machine and servers (SQL Servers, DNS Servers, and Web Server), and have tried restarting the client machines and server machines, rolling back the aforementioned Windows Update on whatever machines we were reliably sure had it, in addition to everything else previously mentioned, to no avail. At best we've gotten very intermittent mixed results with sometimes restarting the client machine sort of works, temporarily.


Current Situation

Currently everything is truly a single server for ServerA and a single server for ServerB (these were Windows Failover Clusters at the start of this issue, but have seen been removed and the clusters destroyed). We also have dev copies of ServerA (ServerADev) and ServerB (ServerBDev) which share the same SQL Service accounts.

We've since setup replication from ServerB to ServerA to side-step the issue in production for now, but we encountered the same issue on our dev environment this week as well.

I tried using xp_cmdshell to run klist purge and I cycled the SQL Services for both ServerADev and ServerBDev, but the issue still persisted.

At this point, I'm pretty sure the issue is due to the botched Windows update November 10, 2020—KB4586830 (OS Build 14393.4046) that specifically states "you might encounter Kerberos authentication and ticket renewal issues". Unfortunately this was installed on a multitude of our servers (both SQL Servers and Domain Controllers) and subsequent Windows updates that have ran, remove the botched update from the Windows Update History, so I have no reliable way to tell which servers even originally received the botched update.

1

5 Answers 5

5

You have already stated, respectively listed what the issue could be:

OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" returned message "Cannot generate SSPI context". Msg -2146893044, Level 16, State 1, Line 0 SQL Server Network Interfaces: The logon attempt failed

So what is SSPI? Microsoft describes it as:

In conjunction with its operating systems, Microsoft offers the Security Support Provider Interface (SSPI). The SSPI provides a universal, industry-standard interface for secure distributed applications.

Reference: Security Support Provider Interface (SSPI) (Microsoft Docs)

Basically it's how an application authenticates itself, the user or other components against an endpoint asking for authentication.

In the Architectural Overview there is the description that ...

A variety of SSPs and packages are available. Windows ships with the NTLM security package and the Microsoft Kerberos protocol security package. In addition, you may choose to install the Secure Socket Layer (SSL) security package, or any other SSPI-compatible SSP.

Reference: SSPI Architectural Overview (Microsoft Docs)

NTLM

When your old application starts in the other domain it will pass on the old NTLM SID value and compare it with what is stored in the SQL Server instance.

If there is a match [Domain/User] with a SQL Server Login [Domain/User] then SQL Server will let the user login to the relevant databases. SQL Server will do the same for a [Domain/User] that is assigned to a SQL Server Windows Group Login.

Kerberos

Kerberos relies on the ability of a user or service account being able to generate a token (of a request) and to compare this with a central provider to determine if a user is who he/she/them claim to be.

emphasis mine

Kerberos V5 is based on the Kerberos authentication system developed at MIT. Under Kerberos, a client (generally either a user or a service) sends a request for a ticket to the Key Distribution Center (KDC). The KDC creates a ticket-granting ticket (TGT) for the client, encrypts it using the client's password as the key, and sends the encrypted TGT back to the client. The client then attempts to decrypt the TGT, using its password. If the client successfully decrypts the TGT (i.e., if the client gave the correct password), it keeps the decrypted TGT, which indicates proof of the client's identity.

Reference 1.1 What is Kerberos and How Does it Work? (MIT Kerberos V 5.0)

Explanation Summary

So your Server A is trying to access Server B via a Kerberos Authentication but failling to succeed, because the SPN could be missing.

To achieve a successful authentication the Server(s) should have a Service Principal Name (SPN). Which is described as:

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

And a bit further down the possible root cause:

If you install multiple instances of a service on computers throughout a forest, each instance must have its own SPN. A given service instance can have multiple SPNs if there are multiple names that clients might use for authentication. For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host. For more information about SPN format and composing a unique SPN, see Name Formats for Unique SPNs.

Reference Service Principal Names (Microsoft Docs)

To Cut a Long Story Short

If either your application (host) and/or your SQL Server Instance are unable to register a Service Principal Name then your application/server will be unable to authenticate users via Kerberos and your linked server may fail.

Check the ERRORLOG of your SQL Server instance and verify that the SQL Server instance was able to register its own SPN. There will be an entry during the instance startup procedure.

The SQL Server Service Account can do this if it is a Domain Service Account and some other conditions are met. Some being:

The client and server computers must be part of the same Windows domain, or in trusted domains.

and

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it's registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn't been performed or fails, the Windows security layer can't determine the account associated with the SPN, and Kerberos authentication isn't used.

Reference: Register a Service Principal Name for Kerberos Connections (Microsoft Docs)

Output of successful SPN registration

2020-12-01 11:14:18.87 Server      
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. 
Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. 
This is an informational message. No user action is required.
...
2020-12-01 11:14:18.94 Server      
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) 
[ MSSQLSvc/SERVERNAME.TG.CH ] for the SQL Server service.

If the service can't register the SPN, then:

Note

If the server can't automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

Output of Unsuccessful SPN registration

2020-11-27 15:41:56.24 Server      
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. 
Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. 
This is an informational message. No user action is required.
...
2020-11-27 15:41:56.43 Server      
The SQL Server Network Interface library could not register the Service Principal Name (SPN) 
[ MSSQLSvc/SERVERNAME:INSTANCENAME ] for the SQL Server service. 
Windows return code: 0x200b, state: 15.
...
2020-11-27 15:41:56.43 Server      
The SQL Server Network Interface library could not register the Service Principal Name (SPN) 
[ MSSQLSvc/SERVERNAME:INSTANCEPORT ] for the SQL Server service. 
Windows return code: 0x200b, state: 15. 

Once your SQL Server instance has its SPN registered, then you should be up and running.


Solutions

Manual SPN Registration

Well you have to be a Domain Administrator or allowed to change Computer Account objects on the OU of the domain. Then you can run SETSPN with the relevant parameters:

setspn -A MSSQLSvc/host.domain.tld:1433 domain\accountname 

Reference: Manual SPN Registration (Microsoft Docs)

Once an SPN has been configured for a service account, then you should be able to start the server again and access the database.

SPN registration requires a restart of the service.

Modifiy OU Object's Permissions

In order for the SQL Server's Service Account to create a SPN automatically in Active Directory it (the service account) should have the permissions to modify its own object (OU).

When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). Suppose the account starting SQL Server doesn't have permission to register an SPN in Active Directory Domain Services. In that case, this call fails, and a warning message is logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended), or NETWORK SERVICE, or an account that has permission to register an SPN. You can register an SPN using a domain administrator account, but this is not recommended in a production environment. When SQL Server runs on the Windows 7 or Windows Server 2008 R2 operating system, you can run SQL Server using a virtual account or a managed service account (MSA). Both virtual accounts and MSA's can register an SPN. If SQL Server isn't running under one of these accounts, the SPN isn't registered at startup, and the domain administrator must register the SPN manually.

Reference: Register a Service Principal Name for Kerberos Connections (Microsoft Docs)

Assigning the Permissions

To grant permission to modify SPNs

  • Open Active Directory Users and Computers. To open Active Directory Users and Computers, click Start, click Run, type dsa.msc, and then press ENTER.
  • Click View, and verify that the Advanced Features check box is selected.
  • Click Advanced Features, if it is not selected. If the domain to which you want to allow a disjoint namespace does not appear in the console, take the following steps:
    • In the console tree, right-click Active Directory Users and Computers, and then click Connect to Domain.
    • In the Domain box, type the name of the Active Directory domain to which you want to allow the disjoint namespace, and then click OK. As an alternative, you can use the Browse button to locate the Active Directory domain.
  • In the console tree, right-click the node that represents the domain to which you want to allow a disjoint namespace, and then click Properties.
  • On Security tab, click Advanced.
  • On the Permissions tab, click Add.
  • In Enter the object name to select, type the group or user account name to which you want to delegate permission, and then click OK.
  • Configure the Apply onto box for Computer objects.
  • At the bottom of the Permissions box, select the Allow check box that corresponds to the Validated write to service principal name permissions, and then click OK on the three open dialog boxes to confirm your changes.
  • Close Active Directory Users and Computers.

Reference: To grant permission to modify SPNs (Microsoft Docs)

SPN registration requires a restart of the service. You should see a successful SPN registration in the ERRORLOG of the permissions are set correctly

Increase Kerberos Token Size

In some cases an account can belong to too many objects/AD groups/ ... which will result in a rather larger Kerberos Token. In these cases you might have to increase the max token size.... Symptoms may include:

A user who belongs to a large number of security groups has problems authenticating. When authenticating, the user may see a message such as HTTP 400 > - Bad Request (Request Header too long). The user also has problems accessing resources, and the user's Group Policy settings may not update correctly.

But

Under similar conditions, Windows NTLM authentication works as expected. You may not see the Kerberos authentication problem unless you analyze the Windows behavior. However, in such scenarios, Windows may not be able to update Group Policy settings.

Reference: Problems with Kerberos authentication when a user belongs to many groups (Microsoft | Docs)

Fix

On a domain controller that is running Windows Server 2003, Windows Server 2008, Windows Server 2008 R2, or Windows Server 2012, you can use Group Policy to add the following registry entry to multiple computers:

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
Entry: MaxTokenSize
Data type: REG_DWORD
Value: 48000

Reference: How to use Group Policy to add the MaxTokenSize registry entry to multiple computers (Microsoft | Docs)


Solution Based on Referenced Answer and Multiple Updates to Question

After reading through all the additional information in the question and other answers, I have come to the conclusion that all possible solutions have been looked at. I think this Q & A could possibly be closed as duplicate of the question which you referenced with Hannah Vernon's answer.

However I would like to provide a solution on how to apply a fix for this issue.

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection, the SPN is registered in the format MSSQLSvc/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

For other connections that support Kerberos the SPN is registered in the format MSSQLSvc// for a named instance. The format for registering the default instance is MSSQLSvc/.

Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions.

Reference: Register a Service Principal Name for Kerberos Connections (Microsoft | SQL Docs)

Interesting Bit of Information

One thing that should be noted is granting these rights is not recommended (see http://support.microsoft.com/kb/319723) if SQL Server is clustered or if you have multiple domain controllers as latency in Active Directory replication can cause connectivity issues with your SQL Server instance.

Reference: Register a SPN for SQL Server Authentication with Kerberos (MSSQLTips.com)

So the solution would be to actdually revoke the Service Account the ability to modify its own object, which would deny it the possibility of removing an SPN during failover.

  • Start Active Directory Computers and Users and make sure you are looking at the Advanced View version.

  • Open up the Service Accounts properties and switch to the Security tab.

  • Select the Self item from the Group- or Username section and look at the Permissions for SELF.

  • Remove the Full Access and/or Write and/or Write servicePrincipalName and/or Write Public Information permission if they are selected.

  • The Write servicePrincipalName might be located in the Advanced section of the Permissions (click on the button Advanced).

This should prevent further issues when the cluster does a failover because instances are restarted, which would result in the SPN being de-registered.

0
2

As a DBA I've experienced more SPN issues than I'd care to imagine. For us, the SQL account we are allowed to use to install SQL Server do not have permissions to write the SPNs to Active Directory. So, SQL uses the NTLM authentication instead. And from there some SPN issues do rise to the surface.

Having said all that, your question seems to arise suspiciously around the time Microsoft just released updates causing authentication issues with whole host of online services including exchange.

Option 1: Roll Back the Latest Microsoft Patches

Before you get too SPN crazy, and I've been there, try rolling back the Microsoft patches on the aforementioned boxes where you are having the issue. Even if it's just for Windows Server.

Option 2: Analyze your SPNs with KCM and generate the missing ones

If that fails, I would analyze your SPNs in Microsoft Kerberos Configuration Manager. Running it we realized that some SPNs were missing that were needed on some of our boxes. The tool allowed us to generate the scripts so our Windows Server guys could run them with the right permissions to get the SPNs into Active Directory. It's a GUI tool that really added value for us when our last SPN issue occurred.

I realize there's all kinds of advice and suggestions, I just thought I'd add mine to the mix.

0
2
+500

From your client (e.g. PC), when you log into ServerB, and run the following, what do you get?

SELECT net_transport, auth_scheme 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID

Ideally, you will see something like TCP and Kerberos. If you see Shared memory, you need to run this again from a remote client (as that only shows up with connections made locally from the server). If you see NTLM, then ServerB does not have a valid SPN registered for it. If it says Kerberos, we still need to dig further into Active Directory SPNs to see what's going on.

Instead of using setspn -l as you've already tried, use setspn -q, as follows:

setspn -q MSSQLSvc/ServerB*

The asterisk is important as it denotes a wildcard search looking for any SPN adhering to that format. setspn -l, is garbage, in my opinion, as it only tells you a part of the story you really care about: it lists SPNs associated with the computer or user account you specify, and it won't list SPNs for this particular service under a different service or machine account (which is what you really want to find when troubleshooting).

I suspect you'll find at least one extraneous (e.g. a second SPN for ServerB) or misconfigured entry (e.g. your SPN shows up under an incorrect account or with an incorrect port). If an SPN listed is not associated with the user account running the SQL Server service on ServerB, this is the cause for your SSPI context issue. To fix this, you will need to delete the bad SPN via setspn -d and then register the proper one associated with the account running the SQL Server service on ServerB, via setspn -a.

If you don't see any bad SPNs, the only other option I can think of (based upon your note about it working only after you restarted your PC) is you've got some bad DNS cache entries floating out there. The only way I know to fix this from the client-side, aside from your already discovered approach of a PC restart or to let the entry age out of cache is to flush it by running the following via command prompt (or PowerShell):

ipconfig -flushdns

You could try running that command on ServerA as it is acting as the client to ServerB when trying to connect remotely.

If neither of those options work, please update your question as there are some other (and more obscure) possibilities that could cause this behavior.

As an aside, it also sounds like you're running delegation, and if you're like a gross majority of organizations you're running unconstrained delegation. I encourage you to look into traditional constrained or resource-based constrained delegation configurations as they tend to be more secure when it comes to unauthorized access elevation. More information on this can be found in another of my answers, here.

Active Troubleshooting Instructions

Note: Feel free to obfuscate the results, but make sure they're consistent within your answers and in relation to your question.

Can you provide the output of the following:

  1. What SPN does ServerB attempt to register at startup (found within the ERRORLOG)?
  2. What does setspn -q MSSQLSvc/ServerB* return?
  3. What service account is the ServerB SQL Service running under?
  4. Did you run the first query in my answer against both ServerA and ServerB? If so, did Kerberos appear in the results for both servers?

Ok, so to me you have some improperly registered SPNs on the network, but it sounds like your real issue had to do with delegation no longer functioning (which in part depends on accurate SPNs being registered). In this response, I'll talk about your SPNs and how they should look, but regarding delegation, I'll once again point you to my answer in a separate thread regarding that topic. In that answer, I list what the types of delegation are, how to configure them, and most importantly at the end, I provide a link to a PowerShell script that shows you what servers are configured with what type of delegation so you know if it's configured properly or not. Fair warning here, it's a long answer, but delegation is a complicated topic so I encourage you to read through all of it.

Now, for the SPNs... Before I know which ones are accurate, you need to answer the following question. What account is running your SQL Server service? Is it NETWORK SERVICE (or one of the other local accounts such as LOCAL SERVICE or LOCAL SYSTEM) or a proper domain account?

From the pic showing your SPNs registered for ServerA, it looks like you have a computer object listed as well as a service account, which I find suspicious:

enter image description here

Normally, I would expect to see the MSSQL service running under a machine/computer account if the service is running as one of the built in accounts (e.g. NETWORK SERVICE, LOCAL SERVICE, LOCAL SYSTEM) OR a service account (e.g. Domain\UserName); NEVER both. The message in your ERRORLOG could also be a bit of a red herring here. If SQL Server already sees a SPN for the service (regardless if it's correct or not), it will show the following message in the ERRORLOG:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com:1433 ] for the SQL Server service.

Which is what you're showing in your screenshot. What you should look for is if the SPN is being successfully deregistered, as that will confirm if the service account has sufficient Active Directory permissions to manage its own SPNs. That message would look as follows:

The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com ] for the SQL Server service.
The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com:1433 ] for the SQL Server service.

You can quickly check for this by running the following command which searches the last ERRORLOG for any records with SPN in them:

EXEC xp_readerrorlog 1, 1, N'SPN'

If you see a message that says it "could not deregister the SPN" that further enforces my suspicion your SPNs are incorrect.

With correctly registered SPNs, what you should see is something as follows:

C:\>setspn -q MSSQLSvc/servername*
Checking domain DC=domain_name,DC=com
CN=serviceAccountName,CN=Service Accounts,DC=domain_name,DC=com
        MSSQLSvc/servername.domain_name.com
        MSSQLSvc/servername.domain_name.com:1433
        MSSQLSvc/servername:1433
Existing SPN found!

What this shows is that the SPNs for the MSSQL Service are registered to a Service Account, not a server. If your services are running as domain accounts this will be the results format you want to see, but since this isn't what you're showing, you should recreate your SPNs under the proper Service Account. If you need formatting for this operation, let me know, but it's basically the setspn -d setspn -a dance.

Finally, as John K. N. pointed out in another answer on this thread, you can configure the server to register its own SPNs automatically. However, I've not worked with a Network Admin that is yet comfortable with the instructions provided by Microsoft to automate this as they over-grant permissions to the objects for this functionality. Thankfully though there is a way to configure an OU on the domain to allow for self-registration of SPNs without over-granting permissions. Those instructions can be found on this additional dba.se question, here.

SPNs are not easy to wrap your head around. They are integral in a service establishing trust on your network, so even slight misconfigurations can spell disaster. Again, there is a lot of information to digest in my answer, but a combination of the correct SPNs and the proper AD objects being configured will allow you to configure delegation in a fail-proof and security-conscious manner.

0
1

This error usually comes when the Windows user account is expired and he is already logged in with old password. Just ask the user to restart his machine and check if the password is expired or he has changed the password. Hope this helps!!!!!

1
  • 1
    Regarding passwords expiring soon: I can't prove it, but I've seen applications that aren't able to handle a response from the domain controller of "The password is good, but it is about to expire. Give them a little warning if you can." It's like the application treats "good" as a success, and anything else as a failure. The applications that I saw (mis)behave this way were not SQL Server, but it has taught me that almost-expired passwords can cause trouble.
    – Doug Deden
    Mar 18, 2021 at 22:51
1

Do check all the client/server times carefully, including the timezone offsets. as @MBuschi notes, lack of time sync can also cause the SSPI context to fail.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.