Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.
mssql setspn     setspn    

Fix SQL Server Login Issue Using setspn


How to troubleshoot the "Cannot generate SSPI context" error message
https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

-- --------------------------------------------------------------
the Problem: 
Simplified explanation
If you start SQL Server while logged on with the LocalSystem account, the SPN is automatically set up. 
However, if you use a domain account to start SQL Server, or when you change the account that is used to start SQL Server, 
you must run SetSPN.exe to remove expired SPNs, and then you must add a valid SPN.

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication 
interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain 
account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local 
account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up 
for the computer that is running SQL Server. If you test by using a domain administrator account as the SQL Server service 
account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN 
are present.

Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), the computer 
that is running SQL Server cannot create its own SPN. Therefore, you must manually create an SPN for your computer that is 
running SQL Server if you want to use Kerberos authentication when you connect to a computer that is running SQL Server. 
This is true if you are running SQL Server under a domain user account or under a local user account. 
The SPN you create must be assigned to the service account of the SQL Server service on that particular computer. 
The SPN cannot be assigned to the computer container unless the computer that is running SQL Server starts with the local system 
account. There must be one and only one SPN, and it must be assigned to the appropriate container. 
Typically, this is the current SQL Server service account, but this is the computer account container with the local system account.
-- ---------------------------------------------------------------
The solution:
1):The password of the account was changed. However, you never restart the SQL Server service after the password was changed. 
2): If your logon domain differs from the domain of the computer that is running SQL Server, check the trust relationship between the domains. 
3): Check whether the domain that the server belongs to and the domain account that you use to connect are in the same forest. 
This is required for SSPI to work. 
4): Use the Account is Trusted for Delegation option in Active Directory Users and Computers when you start SQL Server.
5): use setspn.exe
6): Verify that name resolution is occurring correctly. Name resolution methods may include DNS, WINS, Hosts files, and Lmhosts files.
7): Firewall issue with ActiveDirectory
 

-- ---------------------------------------------------------------


Microsoft Kerberos Configuration Manager
C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server

download software 

Microsoft Kerberos Configuration Manager

https://www.microsoft.com/en-us/download/details.aspx

https://support.microsoft.com/en-in/help/2985455

DsGetSpn function
https://msdn.microsoft.com/en-us/library/windows/desktop/ms675993(v=vs.85).aspx

Fix SQL Server Login Issue Using setspn


-- ------------

Use the Manipulate Service Principal Names for Accounts (SetSPN.exe) utility
setspn

-- need to login as a domain admin account

setspn -?

setspn -L sqlserver_machine_name
setspn -L SPICASATMDB01V
setspn -L SPICASATMDB01V.spirit.ncl.com  -- not working

setspn -A MSSQLSvc/Host:port serviceaccount
setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount

setspn -A MSSQLSvc/myserver.microsoft.com:1433 sqlsvc
setspn -A MSSQLSvc/myserver.microsoft.com:1433 MYDOMAIN\sqlsvc

setspn -D MSSQLSvc/myserver.microsoft.com:1433 sqlsvc
setspn -D MSSQLSvc/sqlserver_machine_name  sqlserver_machine_name
setspn -D MSSQLSvc/sqlserver_machine_name  sqlserver_machine_name:1433

-L: list
-A: Add
-D: Delete
-- -----------

adsiedit.msc

Adsiedit.msc

To configure the SQL Server service to create SPNs dynamically, follow these steps:

Click Start, click Run, type Adsiedit.msc, and then click OK.

In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, 
and then click Properties.

Notes
 DomainName is a placeholder for the name of the domain.
 RootDomainName is a placeholder for the name of the root domain.
 AccountName is a placeholder for the account that you specify to start the SQL Server service.
 If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that 
you use to log on to Microsoft Windows.
 If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account. 

 In the CN= AccountName Properties dialog box, click the Security tab.
    On the Security tab, click Advanced.
    In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries.

    If SELF is not listed, click Add, and then add SELF.
    Under Permission entries, click SELF, and then click Edit.
    In the Permission Entry dialog box, click the Properties tab.
    On the Properties tab, click This object only in the Apply onto list, 
    and then make sure that the check boxes for the following permissions are selected under Permissions:
        Read servicePrincipalName
        Write servicePrincipalName
    Click OK three times, and then exit the ADSI Edit snap-in. 
-- ----------


https://msdn.microsoft.com/en-us/library/ms676056.aspx
Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), 
the computer that is running SQL Server cannot create its own SPN. 
Therefore, you must manually create an SPN for your computer that is running SQL Server 
if you want to use Kerberos authentication when you connect to a computer that is running SQL Server. 
This is true if you are running SQL Server under a domain user account or under a local user account. 
The SPN you create must be assigned to the service account of the SQL Server service on that particular computer. 
The SPN cannot be assigned to the computer container unless the computer that is running SQL Server starts with the local system account. 
There must be one and only one SPN, and it must be assigned to the appropriate container. 
Typically, this is the current SQL Server service account, 
but this is the computer account container with the local system account. 
-- -----------

How to troubleshoot the "Cannot generate SSPI context" error message?

SSPI setspn -a login issue
setspn -a 
SSPI
the SPNs in Active directory

-- -----------
RESOLUTION:
1. You need to reset SPN. use the syntax "SET SPN"
2. change your sql server service account from domain account to Local account recycle sql 
   and then reset again with your domain account and recycel sql server.
-- ----------

setspn -L SPICASATMDB01V
setspn -L SPICASATMDB01V.spirit.ncl.com

MSSQLSvc/SPICASATMDB01V.spirit.ncl.com:1433
MSSQLSvc/SPICASATMDB01V.spirit.ncl.com

ASDI edit, found the server, 
right clicked it-> properties, 
and removed the "MSSQLSvc" entries from servicePrincipalName. 

Powershell Using setspn


# $env:PSMODULEPATH
# Install-Module dbatools 
# Install-Module dbatools -Scope CurrentUser 
Import-Module dbatools, sqlserver
# Test-DbaSpn -ComputerName MYDBSERVERNAME
Test-DbaSpn   -ComputerName MYDBSERVERNAME|select InstanceName,RequiredSPN,Error,Warning

get-alias ft
get-alias fl

Test-DbaSpn   -ComputerName MYDBSERVERNAME|select InstanceName,RequiredSPN,Error,Warning,InstanceServiceAccount
Test-DbaSpn   -ComputerName MYDBSERVERNAME|select InstanceName,RequiredSPN,Error,Warning,InstanceServiceAccount|ft
Test-DbaSpn   -ComputerName MYDBSERVERNAME|select InstanceName,RequiredSPN,Error,Warning

setspn -L MYDBSERVERNAME
setspn -L MYDBSERVERNAME.mydomain.com