MSSQLSERVER| Linked server can not been connected

server info:  SQLD01  --configured linked server(SQLP01\WSQLP01), and the connection is okay                   SQLP01\WSQLP01  --target instance of the linked server                   SQLQ01  --used to test the remotely linked server connection
background: customer complains the linked server on SQLD01 can not connected though remotely server SQLQ01, shows the error as follow
TITLE: Microsoft SQL Server Management Studio
------------------------------ The test connection to the linked server failed. ------------------------------
ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

but we did test that the connection are successful locally(connected on SQLD01), its very strange
This explains a perfect double hop scenario. There are 2 prerequisites for a double hop scenario.
Where we log in to SQLQ01(the remote server) with our credentials and it needs to be passed by SQLD01 to SQLP01\WSQLP01. For this to happen, delegation needs to be enabled for the service account under which the middle tier server (SQLD01) is running,
Then we confirmed that the Delegation has been enabled for this service account on the domain controller– Trust this account for any service (Kerberos only),  which is the 2nd option in the Active Directory.
 
There is one more requirement for double hop scenario. The middle tier server (SQLD01) and destination server (SQLP01\WSQLP01), should be enabled for Kerberos. We check this by executing a query on the server –
 
select auth_scheme from sys.dm_exec_connections
 
This query will list all the different authentication protocols in use right now. We should see at least one Kerberos in the list. We could not see that for SQLD01 in the beginning. So we thought there could be a problem of the SPN’s. If the SPN’s aren’t set perfectly under the correct service account, Kerberos will not work and thereby double hop scenario is not possible.
So we checked the SPN’s for the service account VT\sqladmin, we saw that the SPNs are registered fine. Command: setspn -l VT\sqladmin, and we can see the SPN registerred.
 
We then checked for duplicate SPNs. Command: setspn -x SQLD01
 
We got to know that the SPNs for SQLD01 were registered under 2 accounts, under VT\sqladmin as well as under the machine account - SQLD01.
We went ahead and deleted the SPNs from the machine account using the commands:
 
setspn -d MSSQLSvc/SQLD01.corp.vectort.com:1433 SQLD01
setspn -d MSSQLSvc/SQLD01.corp.vectort.com SQLD01
 
It took some time to do the changes as we have more than one Domain Controller, and it takes time for the changes to reflect on all of them.
Then we restarted SSMS and tried double hop scenario and it worked.
Summary:  We did registerred the SPNs before, because the linked server even not works the the local server itself(SQLD01), after we registerred, it works.  Later, we tried to start the sqlserver sercice under localsystem account, then it should be the rootcause that there are dumplicate SPNs exist.
Important:
If the SQL Server service is running under the LocalSystem account, we do not have to manually configure an SPN for SQL Server.
The SPN is created automatically when the SQL Server service starts. If the SQL Server service runs under a domain user account, you must manually configure an SPN.
To do so, follow these steps. The commands we did add the SPNs before
Setspn -A MSSQLSvc/SQLP01.corp.vectort.com:62240 VT\sqladmin
Setspn -A MSSQLSvc/SQLP01.corp.vectort.com:WSQLP01 VT\sqladmin Setspn -A MSSQLSvc/WSQLD01.corp.vectort.com:1433 VT\sqladmin
Setspn -A MSSQLSvc/WSQLD01.corp.vectort.com VT\sqladmin