Cannot generate SSPI context : SQL service account

Pernah mengalami pesan seperti ini :

Image result for cannot-generate-sspi-context-after-changing-sql-service-account

“The target principal name is incorrect. Cannot generate SSPI context.”

Ketika ingin login domain untuk service sql server dan hasilnya gagal login.....dan berikut artikel dari Microsoft untuk menjelaskan kejadian tersebut.

The explanation, as given by Microsoft in this KB article

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.

Berikut langkah aman memperbaikinya di windows server :

· Run Adsiedit.msc

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

· In the CN= AccountName Properties dialog box, click the Security tab.

· On the Security tab, click Advanced.

· In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row

· Click Edit, Open Permission Entry dialog box.

· Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:

o Read servicePrincipalName

o Write servicePrincipalName

Click OK to apply all changes and exit the ADSI Edit snap-in

Akhirnya….restart SQL Service(s) yang digunakan oleh account tersebut dan pesan tersebut tidak akan muncul lagi.