SQL 2019 SPN Issues

Question

SQL 2019 SPN Issues

Sam Garth 26 Microsoft Employee

Hi

I have set up a SQL Server 2019 server. When it is set up to use LocalSystem I can connect from my desktop and I have KERBEROS authentication.

If I change it to use a domain account I get the following error

I checked the SPN for the account and saw the below

I then removed these and when I started SQL Server it had recreated them again.

In the SQL Server Logs I see the following

Can anyone tell me where I have gone wrong? The only difference between this and our other servers are that this is Windows and SQL 2019 and most of our estate is 2016.

Thanks

Sam

Robbie Varn 351 Reputation points

2021-07-30T18:30:43.717+00:00

When you register the spns, are you using the FQDN (fully qualified domain name)? I would expect to see in the return from the setspn -L command, something like:

MSSQLSvc/ServerName.yourcompany.com
MSSQLSvc/ServerName.yourcompany.com:xxxx (xxxx being the port number, 1433 if not a specified port number).

If you have multiple instances running on the server, you may want to ensure the Sql browser is running on the server.
Seeya Xi-MSFT 16,661 Reputation points

2021-08-04T01:46:26.08+00:00

Hi @Anonymous ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T14:54:01.703+00:00

Hi

I have tried
MSSQLSVC/servername
MSSQLSVC/servername.domain
MSSQLSVC/servername:1433
MSSQLSVC/servername.domain:1433

Every time it fails. Even If I try one of them, if I restart SQL it will add them itself.

Thanks

Sam
Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T14:54:40.797+00:00

Yes sorry been hectic so trying to test this around other issues!

Updating now

2 answers

Your answer

Robbie Varn 351 Reputation points

2021-07-30T18:30:43.717+00:00

When you register the spns, are you using the FQDN (fully qualified domain name)? I would expect to see in the return from the setspn -L command, something like:

MSSQLSvc/ServerName.yourcompany.com
MSSQLSvc/ServerName.yourcompany.com:xxxx (xxxx being the port number, 1433 if not a specified port number).

If you have multiple instances running on the server, you may want to ensure the Sql browser is running on the server.
Seeya Xi-MSFT 16,661 Reputation points

2021-08-04T01:46:26.08+00:00

Hi @Anonymous ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T14:54:01.703+00:00

Hi

I have tried
MSSQLSVC/servername
MSSQLSVC/servername.domain
MSSQLSVC/servername:1433
MSSQLSVC/servername.domain:1433

Every time it fails. Even If I try one of them, if I restart SQL it will add them itself.

Thanks

Sam
Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T14:54:40.797+00:00

Yes sorry been hectic so trying to test this around other issues!

Updating now

Answer 1

Charles Thivierge 4,171

Service Principal Names has to be set on the account where the service is running.

When you have configured your SQL Server running with Local Services, the SPN's were probably on the SQL Server Computer account in Active Directory (this is why it was working)
If you want to configure your SQL Server service to run with a service account (user account in AD or gMSA), the SPN's has to be set on the service account.

You can run the following command from the SQL Server
setspn -q MSSQLSvc/[SQLServerName]:1433

This should give you the list of the SPN's as well as the AD Account where the SPN's are currently registered (computer account / user account / gMSA).

If you want to configure your SQL Server to run with a service account, you must first remove the SPN's that are registered on the computer account and then register the SPN's to the service account.

Here is the steps.

Remove SPN from the computer account SQL01.mydomain.local
setspn -D MSSQLSvc/SQL01:1433 SQL01
setspn -D MSSQLSvc/SQL01.mydomain.local:1433 SQL01
Register the SPN to the service account SVC-SQL01
setspn -S MSSQLSvc/SQL01:1433 mydomain\SVC-SQL01
setspn -S MSSQLSvc/SQL01.mydomain.local:1433 mydomain\SVC-SQL01
For testing, you may have to purge kerberos tickets from your client computer
From your client computer, open a command prompt and execute the following command: klist purge

When completed, your client should be able to access your SQL Server using Kerberos Authentication.

For troubleshooting, you can also enable Kerberos logging on your client computer... This can be done using this simple command from an elevated cmd or powershell:
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters" /v LogLevel /T REG_DWORD /d 1 /f

This will add Kerberos errors in the System Event Log that may help you debugging.

To stop logging Kerberos errors, just change the value 1 to 0...
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters" /v LogLevel /T REG_DWORD /d 0 /f

hth

Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T14:59:00.13+00:00

I added logging and I am getting the following errors in event log

The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server svc_pdcsqlce01vm_srv. The target name used was MSSQLSvc/pdc-sqlce-01vm.DOMAIN:1433. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (DOMAIN) is different from the client domain (DOMAIN), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

A Kerberos error message was received:
on logon session
Client Time:
Server Time: 14:51:19.0000 8/4/2021 Z
Error Code: 0x29 KRB_AP_ERR_MODIFIED
Extended Error:
Client Realm:
Client Name:
Server Realm: DOMAIN
Server Name: svc_pdcsqlce01vm_srv
Target Name:
Error Text:
File: onecore\ds\security\protocols\kerberos\client2\ctxtapi.cxx
Line: 5b6
Error Data is in record data.
Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T15:00:10.553+00:00

I tried all different options, with port, without port, with domain without domain.

Thanks for your response. I would have put all this in one message but it is limited to 1600 characters!
Charles Thivierge 4,171 Reputation points

2021-08-04T15:04:56.5+00:00

Ok, so you have some Kerberos errors.

Open a command prompt and identify where the SPN for SQL is currently registered.
You can have this information using this command line:
setspn -Q MSSQLSvc/pdc-sqlce-01vm.DOMAIN:1433

This would list if the SPN is in AD (which it is) but also what account is holding the SPN.

Then, on your SQL Server, open the services.msc console and check if the service account that is configured to run the SQL Engine service is the same that hold the SPN "MSSQLSvc/pdc-sqlce-01vm.DOMAIN:1433"

hth
Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T15:13:52.577+00:00

Hi

Yes they are the same.

I followed the details on the below link and it worked fine on Windows 2012/2016 and SQL 2016. Maybe there are other things I am missing for Windows and SQL 2019?

https://mssqlwiki.com/tag/the-sql-server-network-interface-library-could-not-register-the-service-principal-name-spn/

Thanks

Sam
Charles Thivierge 4,171 Reputation points

2021-08-04T15:47:35.677+00:00
AFAIK, SPN configuration for SQL 2012+ and Windows 2012+ are the same.

What you can try is to:

Restart the SQL server service

Flush the Kerberos ticket on the client computer (Open a command prompt and type klist purge)

Then, try again if it fix the issue
Sam Garth 26 Reputation points Microsoft Employee

2021-08-05T08:29:36.9+00:00

Thanks, same issue though. I have tried it from a few different servers and using different logins.

Do I need to delete and recreate the AD service account?
Charles Thivierge 4,171 Reputation points

2021-08-05T10:44:53.323+00:00

No need to recreate the account.

Please validate you don't have duplicate SPN using this command: setspn -x

Can you post the output of those command:

Setspn -l pdc-sqlce-01v
Setspn -l svc_pdcsqlce01vm_srv
Sam Garth 26 Reputation points Microsoft Employee

2021-08-05T11:43:18.763+00:00

Registered ServicePrincipalNames for CN=PDC-SQLCE-01VM,OU=PDC,OU=Servers,DC=DOMAIN
TERMSRV/PDC-SQLCE-01VM
TERMSRV/PDC-SQLCE-01VM.DOMAIN
WSMAN/PDC-SQLCE-01VM
WSMAN/PDC-SQLCE-01VM.DOMAIN
RestrictedKrbHost/PDC-SQLCE-01VM
HOST/PDC-SQLCE-01VM
RestrictedKrbHost/PDC-SQLCE-01VM.DOMAIN
HOST/PDC-SQLCE-01VM.DOMAIN

Registered ServicePrincipalNames for CN=svc_pdcsqlce01vm_srv,OU=SQL,OU=ServiceAccounts,DC=DOMAIN
MSSQLSvc/PDC-SQLCE-01VM.DOMAIN:1433
MSSQLSvc/PDC-SQLCE-01VM.DOMAIN
MSSQLSvc/PDC-SQLCE-01VM
Charles Thivierge 4,171 Reputation points

2021-08-05T13:23:49.683+00:00

Hmmm... The SPN's seems to be configured correctly and the SQL Server service is running with the service account svc_pdcsqlce01vm_srv.

If you ping the server name PDC-SQLCE-01VM, it resolve the correct IP ?
I just want to be sure that you don't have name resolution issues
Sam Garth 26 Reputation points Microsoft Employee

2021-08-05T14:40:28.603+00:00

Yes all looks OK. Even checked DNS
Charles Thivierge 4,171 Reputation points

2021-08-05T16:45:34.287+00:00

I have installed a SQL 2019 on a Windows 2019 just to see if the SPN works well and there is no issue on my side.

As i said earlier, the error "KRB_AP_ERR_MODIFIED" is most of the time caused by a SPN that is not registered on the correct AD account. If the SQL Server Engine service is configured to run with the service account DOMAIN\svc_pdcsqlce01vm_srv and the SPN's are registered on the account svc_pdcsqlce01vm_srv, there is no reason the Kerberos authentication would not work.

If the Name resolution is ok and you are not using any CNAME (DNS alias) to connect on the SQL Server, then it should work.

Sometimes, this kind of issues could be caused by a bad ticket that remains on the client session but you have purged all tickets using the klist purge command.
Sam Garth 26 Reputation points Microsoft Employee

2021-08-05T16:57:04.913+00:00

Thanks for all the help you have given. It really is appreciated.

As an update our infrastructure team think it may be caused by cis hardening but they are checking.

Thanks again for all your help.
Charles Thivierge 4,171 Reputation points

2021-08-05T17:05:11.39+00:00

Ok, let us know if you still need help after your investigation with cis hardening

Answer 2

Hi @Anonymous ，

For more information, please refer to : https://blog.sqlserveronline.com/2018/01/12/sql-server-target-principal-name-incorrect-cannot-generate-sspi-context/
Quote from this:
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.

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Sam Garth 26 Reputation points Microsoft Employee

2021-08-04T15:03:35.113+00:00

Hi

Thanks for the response, I tried that URL but I did not have an option for msDS-PrincipalName. But I did follow https://mssqlwiki.com/tag/the-sql-server-network-interface-library-could-not-register-the-service-principal-name-spn/ which has worked on every other server (mostly Windows and SQL 2016).

Thanks

Sam

Share via

SQL 2019 SPN Issues

2 answers

Your answer