SQL Server / The target principal name is incorrect. Cannot generate SSPI context

One of our old SQL servers was running under the local system context. Then we decided to change the account that the SQL service runs under, and we created domain service account with basic domain user permissions.

Eventually, we end up with following error trying to access our SQL Server remotely.

SQL Server SPN Creation

To run SQL Server service you can use Local System account, local user account or a domain user account. If you are using Local System account to run your SQL Service the SPN will be automatically registered.  Nevertheless, if you are using domain account to run SQL Server Service and you have domain user with basic user permissions (In our case) the computer will not be able to create its own SPN.

In case you are using domain administrator account, you will not have any problems.
SPN will be successfully created since domain account you are using to run SQL Server Service will have domain administrator-level credentials.

There are a couple of solutions for this problem.

• You can elevate permissions and use domain admin account for your SQL Server Service (Not recommended).
• You can manually create an SPN for your computer that is running SQL Server and assigned that SPN to the service account of the SQL Server service on that machine.
• You can use basic domain user account with elevated permissions (Write all properties, Write msDS-PrincipalName)

Pay attention that you can have only one SPN and must be assigned to the appropriate domain/local account (current SQL Server service account)

Manually create SPN

There is really good article by Microsoft how to configure SPN for SQL Servers

How to Configure an SPN for SQL Server Site Database Servers

• Open cmd and list your current SPNs

setspn -l servername

SPN for the NetBIOS name of the SQL Server will look like: MSSQLSvc/SQLServerName:1433

SPN for the FQDN of the SQL Server will look like: MSSQLSvc/SQLServerFQDName:1433

In my case I have just default instance, So I need to change just those with 1433 port number. If you have named instance port number depends on previous SQL Server configuration.

• To change the SQL Server service account from local system to a domain user account remove current SPN from MSSQLSvc/SQLServerName:1433 computer account and add to the domain account.

setspn -D MSSQLSvc/SQLServerName:1433 SQLServerName

setspn -A MSSQLSvc/SQLServerName:1433 Domain\Account

• You can verify domain user SPN is registered correctly with the following command

setspn –L Domain\Account

Write all properties permissions, Write msDS-PrincipalName

Another option is to elevate permissions for domain user you are using to run SQL Server Service. Of course, you will need AD access to accomplish this.

• Active Directory Users and Computers (With Advanced Features Enabled)
• Select User and choose properties
• Select Security TAB

• Select Advanced settings

• Add new permission entry

• Choose “Read all properties” permission

• Select “Write msDS-PrincipalName” properties

Those permissions should be enough to allow that domain user to create SPN.

Similar Posts:

• SQL Server / Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000]
• Azure / SQL Server / Azure DB / Add Logins, Users, Permissions
• Azure SQL Managed Instance / Database Migration Service (DMS) / Online Data Migration / Migrate your database to Azure SQL Managed Instance
• SQL Server / Instant File Initialization
• Powershell / Backup SQL Server DBs to Azure Storage with Powershell script