Linked Server using domain accounts suddenly stopped working (…for a while)

Question

**Issue

• Last week (Monday, afternoon), we ran an Alter Procedure to change dbo.mySpToRead and it worked.

• Last week (Tuesday, late morning), we ran an Alter Procedure again to change the same dbo.mySpToRead and it returns the following error:

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

• Today (late in the morning), we ran again the Alter Procedure and it was successful.

Can anyone suggest me where/how investigate the reasons for last week’s Stop and for today’s “restart” ?

I guess some AD/Systems maintenance has been made, but Domain Admin Team members confirmed to me that no activities were made on the SQL Server Nodes, nor on the SPN/Delegation settings.

Thanks for any ideas.

Here are some more details

All the Alter were executed using a SSMS from my laptop with a domain account with sa privileges on both SQL instances. The account is NOT part of the domain admins group.

**Settings:

• Sql Server 2019 Enterprise Edition ON Windows Server 2019
• 2 Node SQL Cluster: SQL_Prod_2 and SQL_Prod_4
• 6 DBs in Availability Group (AG_PROD) and some other DBs on each node
• the DB NbSmP is included in AG_PROD
• the DB DwhProd is stored on SQL_Prod_4 (this DB is not included in AG_PROD)
• on SQL_Prod_2 exists a Linked Server to SQL_Prod_4 named “SQL_Prod_4” with the following security settings:

• in NbSmP exists a synonym “myRemoteTable” that uses the LinkedServer to access a table from DwhProd:

  CREATE SYNONYM [dbo].[myRemoteTable] FOR [DwhProd].[myDwhDb].[dbo].[myTable]

• in NbSmP exists a stored procedure named "[dbo].[mySpToRead]" that uses the synonym to read data

   SELECT  [ID_P] ,[N_P] ,[N_R]   from [dbo].[myRemoteTable] 
  

**test/check made in the last week (when Linked Server connection was “broken”)

1. using SSMS, connected to on SQL_Prod_2, left click on the synonym to create the script

   --> Test failed reporting the same error.

2. using SSMS, , connected to on SQL_Prod_4, left click on the synonym to create the script

   --> Test succeeded; script generated.

3. using SSMS, connected to on SQL_Prod_2, create a new Linked Server with the FQN “SQL_Prod_4.myDomain.lan” and using the same security settings

   --> Test failed reporting the same error

4. using SSMS, connected to on SQL_Prod_2, change the Linked Server security settings to allow a specific security context with remote login

"aTestUser" has Read permission on the DB DwhProd

--> Test succeeded with both the Linked Servers.

5. using SSMS, connected to on SQL_Prod_2, using the Linked Server with the new security settings (SQL Login), execute the Alter Procedure

--> Test succeeded.

6. using SSMS, connected to on SQL_Prod_2, execute the “select …” statement contained in the stored procedure

--> Test Failed with “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'” error.

7. using the Test/Dev environment (same configuration, different objects names, SQL 2019 Developer instance for both DEV Nodes), replicate test 1 to 6

--> error reported, same as per production environment

**test/check made today

1. using SSMS, connected to on SQL_Prod_2, change back the Linked Server security settings to “Be made using the login’s current security context.”

--> Test succeeded

2. using SSMS, connected to on SQL_Prod_2, left click on the synonym to create the script

--> Test succeeded.

3. using SSMS, connected to on SQL_Prod_2, execute the “select …” statement contained in the stored procedure

--> Test succeeded