October 7, 2022 at 8:50 pm
Hello experts,
I've been dealing with a maddening issue. I am getting the familiar error "The target principal name is incorrect. Cannot generate SSPI context" when I try to connect to a SQL 2019 instance from a remote SSMS session.
Connecting on the server itself via RDP is fine. But that does not use Kerberos.
On the other hand, if I set the SQL Server service account to Local System, everything works fine. Remote connection succeeds and the connection is using Kerberos.
This issue is not appearing when we use the same exact service account on one of our existing SQL 2016 instances. The reason I mention that is that a lot of the references I have checked include a permissions fix for the service account involving Adsiedit.msc. But if this is an issue with the account itself, would we not see this error wherever it is used?
Additionally, I ran the Kerberos Configuration Manager, and the SPN shows up as "GOOD" with a green check mark.
I also checked the SQL Server error log, and it has an entry the seems to indicate the SPN registration worked.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/sqldbserver.xyz.abcde.com:9999 ] for the SQL Server service.
But it apparently did not work because I get the SSPI error mentioned above.
Thanks for any help - this is driving me crazy!
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 8, 2022 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 13, 2022 at 7:57 am
This was removed by the editor as SPAM
October 17, 2022 at 2:37 pm
Back in the old days before Microsoft fixed all security issues in Windows (: there was a thing called Unconstrained Delegation. This was the default setting and resulted in an account being to use Kerberos authentication when connecting to SQL Server running under a service account with no further work being required. Unfortunately it was also found to be a security hole by pesky researchers and malware writers and the use of Unconstrained Delegation was disabled by a security patch sometime in 2018. The Unconstrained Delegation option still exists in the GUI where you can set up a SPN, but if you are on W2012 or above and have as a minimum patches that were issued in 2018 then Unconstrined Delegation does nothing. Nowadays you have to use Constrained Delegation.
To use Constrained Delegation you have to mark the service account as being able to delegate Kerberos Authentication to the SPN. This is done by adding the SPN to the msDS-AllowedToDelegateTo attribute on the Service Account. It is not unusual to have a list of SPNs on this delegate list, partly because of the various syntax variations in SPNs, and partly as some service accounts need rights to login to other services, such as SSAS needing to get to SQL to refresh cubes or tables.
Personally I do not give the Service Account the rights needed to automatically create an SPN, but instead set up the SPNs manually. As part of doing the setup I always add the delegation. Without the delegation you will get the error you are seeing. With delegation, and assuming there is not a further problem, then you should be able to use Kerberos authentication when connecting to SQL Server.
I also use Group Managed Service accounts with SQL and anything else that will accept them. gMSA accounts have a long and strong machine-managed password that gets automatically changed every 30-ish days without impacting on the use of the service. If you use a gMSA account then there is some other setup also needed, see https://github.com/SQL-FineBuild/Common/wiki/SQL-Service-Accounts for details.
I tend to always use $ for the last character of a gMSA account name. This is not needed for SQL2014 upwards, but if you are setting up security for any SQL Server capability that needs IIS, then the GUI (and underlying code) that sets the IIS account and password will accept a gMSA that ends with $ but demands a password if it does not. There may be other places that only accept a gMSA if it ends with $.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply