1

I've just built 2 new Win/SQL 2022 Servers. Everything went ok, and I could connect with SSMS.

I then changed both SQL engine services to use a domain acct (my standard practice), I can connect locally, but can no longer connect remotely via SSMS with the following error.

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

When SQL runs as a local service it can register an SPN. But when it runs as a domain acct it can't. (I've repeated this several times on both servers). I've also tried running the service using a domain admin acct.

I've followed the MS instructions for both automatic and manually setting the SPN. And neither has solved the problem.

Has anyone else run into this? Any advice or suggestions would be greatly appreciated.

I have tried the Kerberos Config Mgr tool and it can't connect either remotely or locally, It returns the error:

10/01/2023 11:36:10 am Error: Connect to SQLWMI failed \root\Microsoft\SqlServer\ComputerManagement System.Management.ManagementException: Invalid namespace

I have also tried running a SQL repair.

The SQL log shows the following warnings after startup:

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.domain ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

0

3 Answers 3

2

Looks like it's something wrong with SPNs.

You should create 4 SPNs for each SQL Server service account to suit for different Windows Authentication scenarios.

  1. Fully qualified domain name of the server with instance name.
  2. Fully qualified domain name of the server with instance port number.
  3. NetBIOS name of the server with the instance name.
  4. NetBIOS name of the server with the instance port number.

The following is an example using Microsoft's command-line tool for managing Service Principal Names, setspn.exe:

setspn -S MSSQLSvc/myhost.redmond.microsoft.com:instancename domain\accountname  
setspn -S MSSQLSvc/myhost.redmond.microsoft.com:1433 domain\accountname  
setspn -S MSSQLSvc/myhost:instancename domain\accountname  
setspn -S MSSQLSvc/myhost:1433 domain\accountname

In case, above commands throw out "duplicate SPNs" error. We can use below commands.

  1. enlist all the SPNs for the DC. To find duplicates, you can search the exact SPN entry to find which user it runs under.
ldifde -r (serviceprincipalname=*) -f allSPN.txt
  1. remove the specific SPN
setspn -D MSSQLSvc/myhost:instancename domain\accoutnname
  1. Verify the SPNs.
setspn -L domain\accountname

For locked-down corporate environments, it is very likely that you'll need a Domain Administrator to execute the commands.

0
0

Isn't your issue that the new SSMS does per default use encrypted connection to your server? In cases I had that was the issue (just uncheck in the connection properties the encryption option of the SSMS tool)

Also know error is the registration of SPN goes wrong due to lack of access;

If you want to fix any SPN issues, the domain account (you use to install) or the svc-account should have the Validated write to service principle name permission set on the OU container of AD where the SQL Server resides!

Check the KerberosX64MSI.msi tool found at Microsoft.

( https://www.microsoft.com/en-US/download/details.aspx?id=39046 )

-1

None of the above worked for us. We discovered that when our servers were built that the erroring server was on a GPT partition table. The others, were on MBR, all clustered, domain\user running SQL servers. We could not use the kerberos tools as it the GPT partitioned, SQL server would only use NTLM, no matter what we tried. SO it is a combination of GPT partitions, SQL servers running on domain credential. That cannot generate/use SPN to communicate with Kerberos for authentication. EG... SSMS.

Idea of setting manually does not work in this situation.

1
  • 2
    Disk Partitioning style has nothing to do with Kerberos. For clarity, I'm using GPT partitioning on all my SQL Servers, and Kerberos authentication works perfectly.
    – Hannah Vernon
    Commented Mar 22 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.