1

I have the windows server with 4 SQL server 2012 instances This is the test server. SPNs are not registered for any of those instances.

I can successfully connect remotely to 3 of those instances using my windows account, however for one of it I got following error: The target principal name is incorrect. Cannot generate SSPI context.

SQL Server login connections are working ok.

We've tried registering/de-registering the SPNs, also created new MSA account for that instance.

From what I've read - when the SPNs are not registered - the NTLM should be used. NTLM is working correctly for remote SQL Server logins.

What else can be checked? Is there any other option forcing this instance to try to use Kerberos connection?

This is the SQL Server 2012 patched to the 11.0.7462.6.

0

This can be caused by one of three things:

  1. The service account has an expired password.
  2. The service account has been locked.
  3. The service account does not have permission to create an SPN.
  • This is the Managed Service account newly setup (wasn't working with the old one as well) – Andrzej Błoch Sep 27 '19 at 14:17
0

Changing the port number on which the instance was setup solved the issue. However it is still not working on the old port number so the root cause of the problem is still unknown.

0

Chances are high that you have a bad SPN currently registered on the domain. To find this, you can simply run the following command from the command prompt:

setspn -q MSSQLSvc/ComputerName*

Replace ComputerName with the name of the server hosting SQL Server and keep the trailing asterisk as it acts as a wildcard.

This should return any SPNs currently registered on the domain for the SQL Server service. The item to pay most attention to is the account or machine that this SPN is registered as. If it does NOT match the account currently running the SQL Server service (found via running services.msc on the server), that is your issue. To fix it, you will want to delete the bad SPN(s) and then register the SPN under the proper user, or even better, let SQL Server do it after you place the service account in a properly configured OU on the domain. Take note though, SQL Server only tries to register the correct SPN at service startup.

UPDATE:

Based on the fact that you said the command above returned no results, I suggest you run a less restrictive query as follows:

setspn -q MSSQLSvc/* > SPNList.txt

This will list all SPNs that are associated with a SQL Server service (i.e. MSSQLSvc). I'm redirecting the output to a file here as the results will likely be large, but the command should run pretty fast. Dig through that output file and see if you can find any MSSQLSvc SPNs associated with any of the following:

  • The machine this database instance is running on
  • The service account that is currently running this database instance
  • The service account(s) that previously ran this database instance
  • The IP address of this server
  • If this is a named instance, any reference to the instance name

If there's anything clearly incorrect, delete it and see if that fixes the issue.

If you're still not seeing anything based on the above criteria, I think the next step is to troubleshoot your service account. Since you're running a Managed Service Account, you can get information about it via the PowerShell command Get-ADServiceAccount, as follows:

Get-ADServiceAccount "AccountName" -Properties * 

Take note that you don't need to include the $ here for a MSA.

Specifically check if the service account has any SPNs listed under the ServicePrincipalNames property. If not, you may need to set this via the Set-ADServiceAccount cmdlet, as follows:

Set-ADServiceAccount "AccountName" -Add @{'ServicePrincipalNames'=@('MSSQLSvc/fqdn','MSSQLSvc/fqdn:port')}

In the example above, fqdn is the Fully Qualified Domain name and port would be the port number your database instance is listening on.

One other property I've seen throw off Kerberos Authentication is when the KerberosEncryptionType property is not set with either AES128, AES256 or both. If this is the case, you may also want to set this property as well using a similar convention to what I have shown above.

  • There are no SPNs registered for this server visible with setspn -q. However I am wondering if it is possible that there is a broken / incompletely unregistered SPN, not visible for me but somehow interfering the connection. – Andrzej Błoch Oct 10 '19 at 6:10
  • @AndrzejBłoch Updated my answer based on your feedback. – John Eisbrener Oct 10 '19 at 15:09
  • Run setspn -q MSSQLSvc/* , there are no entries from the machine/service accounts/ip/instance name on the list. Main problem is that I don't want/need to use the Kerberos for this instance, NTLM is enough and I can connect to the instance when it is working on 1441 and cannot when it is working on 1440. – Andrzej Błoch Oct 11 '19 at 15:56
  • I don't think you can at the service level. You can disable specific user accounts from using Kerberos preauthentication, but that's likely not what you want. If it's not a bad SPN, it's a bad service account, did you check those properties I mention in my answer? – John Eisbrener Oct 11 '19 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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