Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect. Cannot generate SSPI context. “) fun!!

Recently had an issue where an engineer had decided to change an existing SQL Server from using dynamic ports to a static port.

The server was a named instance.

servername\instancename

Having made the change using SQL Server Management Studio (SSMS) on the actual server he did not notice that access to the server instance via SSMS remotely had stopped working.

Trying to connect remotely to the server resulted in a

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

error dialog appearing.

after a bit of investigation it was clear this was a problem with the Service Principal Name (SPN) that had been created. There are a number of troubleshooting articles about this issue and I list them in the references below.

One of the solutions is to remove and recreate the SPN. This involves using the setSPN.exe, however you need to know structure the URLs. However whilst looking at this I cam across this

Kerberos Configuration Manager for SQL server

This tool actual checks your SPNs and will generate a script to fix it, or you can just click fix and it will run the script.

In the end this is all I used to fix my issue

 

 

References

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

https://support.microsoft.com/en-in/help/2985455/kerberos-configuration-manager-for-sql-server-is-available

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/

https://social.technet.microsoft.com/Forums/systemcenter/en-US/b879b1a7-c996-4a3f-8e9d-a70ebdffca44/spns-for-named-sql-instances?forum=operationsmanagergeneral

https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65bcf7a3-ae42-43ae-958e-11dccb123e80/setspn-kerberos-and-named-instances?forum=sqlsecurity

https://social.technet.microsoft.com/wiki/contents/articles/18996.active-directory-powershell-script-to-list-all-spns-used.aspx

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect.  Cannot generate SSPI context. “) fun!!