****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:

[![Security Settings before issue][1]][1]


 - 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

[![Security Settings SQL Login][2]][2]


  [1]: https://i.sstatic.net/Cok74.png
  [2]: https://i.sstatic.net/LimUK.png

"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*