The target principal name is incorrect. Cannot generate SSPI context(Set SPN)

 

If the domain user that you set for SQL Server Service Account does not have the following rights on the active directory, you will get this error. Open your SQL Server Log file to make sure that your error is related to this.

If you see that you received an error such as the following in your log file, you are in the correct article.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ ] for the SQL Server service. Windows return code: 0x2098, state: 15.

It gives an error as follows when you want to connect to the corresponding server via SSMS.

There are three ways to solve this problem.

  1. Change the SQL Server service account to Local System or Network Service. Once you have established the connection, you can change it back to the old service account.
  2. Make the SQL Server service account Domain Admin. Solves your problem, but it is a ridiculous method. 🙂
  3. If you give the following privileges to your sql server service account on Active Directory, the problem will be resolved.

“Read servicePrincipalName”

“Write servicePrincipalName”

Regardless of this error, you must give log on as a service to the sql server service account. To achive this, read the following article.

Security Policy for SQL Server(secpol.msc)

If you are going to use the sql server’s always on availability group feature, you must also give the privilege “Create Computer Object” to the sql server service account on the active directory.( in the Organization Unit where sql server servers are located)

I recommend you read the article “SQL Server Service Account’s Permissions on Windows“.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *