-- ------------
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.
|