question

SamGarth avatar image
0 Votes"
SamGarth asked cthivierge commented

SQL 2019 SPN Issues

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

119399-image.png

I checked the SPN for the account and saw the below

119458-image.png


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

In the SQL Server Logs I see the following

119482-image.png


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

sql-server-general
image.png (14.0 KiB)
image.png (68.2 KiB)
image.png (16.5 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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

0 Votes 0 ·

Hi @SamGarth ,

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.

0 Votes 0 ·

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

Updating now

0 Votes 0 ·
cthivierge avatar image
0 Votes"
cthivierge answered cthivierge commented

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.

  1. Remove SPN from the computer account SQL01.mydomain.local
    setspn -D MSSQLSvc/SQL01:1433 SQL01
    setspn -D MSSQLSvc/SQL01.mydomain.local:1433 SQL01

  2. 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

  3. 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

· 13
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.









0 Votes 0 ·

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

0 Votes 0 ·

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

0 Votes 0 ·
Show more comments

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!

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SamGarth commented

Hi @SamGarth

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·