Share via

SQL server anonymous authentication error - issue setting up constrained delgation

Luke 1 Reputation point
2021-03-09T19:57:35.393+00:00

I'm facing anonymous authorization issues that appear to be related to a delegated TGT that expired and coudn't be renewed. There is an event log entry (EventID 18) on the middle tier SQL instance (SQLInstanceA) that references an expired TGT and the TGT was unable to be renewed because unconstrained delegation was used (Microsoft released a security patch in December that requires constrained delegation to renew expired delgated TGTs). I believe configuring constrained delegation will resolve my issue but I don't understand something. Here is my scenario: SQLInstanceA has a linked server object for SQLInstanceB in SSMS. The service account used for the SQL service on SQLInstanceA is srv_sqlinstance_a. srv_sqlinstance_a has multiple SPNs associated with it: MSSQLSvc/SQLInstanceA.domainname.com/SQLInstanceA and MSSQLSvc/SQLInstanceA.domainname.com/SQLInstanceA:<port number>. Another service account is referenced in SSMS in login properties for <domain>\SQLInstanceA under User Mapping (srv_some_other_service_account). This account is also referenced in the event log error (EventID 18) not srv_sqlinstance_a (the service account used for the running sql instance). There is no SPN configured for srv_some_other_service_account. This is the standard configuration across all sql instances. I am not a DBA and I do not manage the databases so I am unsure of the reasoning behind this. Only a single SPN can be associated per instance, right? I can't have the following SPNs: setspn -a MSSQLSvc/SQLInstanceA.domainname.com/SQLInstanceA domainname\srv_sqlinstance_a setspn -a MSSQLSvc/SQLInstanceA.domainname.com/SQLInstanceA domainname\srv_some_other_service_account Am I able to configure constrained delegation on a service account (srv_some_other_service_account) that is not associated with SQLInstanceA? Is this a design problem in our environment, should UserMapping in SSMS be configured only for the account associated with the sql service running on the host (domainname\srv_sqlinstance_a)?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
Windows Server 2012
Windows Server 2012
A Microsoft server operating system that supports enterprise-level management, data storage, applications, and communications.
1,538 questions
Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
5,959 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-10T03:55:34.527+00:00

    Hi anonymous user-4940,

    > MSSQLSvc/SQLInstanceA.domainname.com/SQLInstanceA and MSSQLSvc/SQLInstanceA.domainname.com/SQLInstanceA:<port number>.

    The supported SPN formats for named and default instances are as follows.

    Named instance : MSSQLSvc/<FQDN>:<port> or MSSQLSvc/<FQDN>: <instancename>
    Default instance : MSSQLSvc/<FQDN>:<port> or MSSQLSvc/<FQDN>

    Such as in my environment as below screenshot. This is the SPN for SQL 2019 instance.

    76104-screenshot-2021-03-10-114747.jpg

    Please refer to MS document SPN Formats.

    >Only a single SPN can be associated per instance, right?

    Yes, you are right.

    > Am I able to configure constrained delegation on a service account (srv_some_other_service_account) that is not associated with SQLInstanceA?

    No. Which account that your SQL server running under? We can only using SQL server service account to register SPN. As below screenshot in my environment, contoso\administrator is the SQL server service account for SQL 2019 instance.

    76106-screenshot-2021-03-10-114545.jpg

    Suggest you read the MS document Register a Service Principal Name for Kerberos Connections to better understand SPN.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    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.

  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-11T09:50:43.503+00:00

    Hi anonymous user-4940,

    > This is the account that I'm unable to configure constrained delegation for because MSSQLSvc is not listed as an available service

    I have some confuse about your issue. What did you want to configure? Did you have Kerberos double-hop issue, so you want to configure delegation? Or you have SQL server connection issue, SQL server could not register correct SPN?

    For delegation issue, did you try to set the configuration as below screenshot. The admin in below screenshot is also the SQL server service account contoso\administrator that I mentioned.

    76712-screenshot-2021-03-11-174744.jpg

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.