Delegated authentication (or double-hop authentication as it’s sometimes called) is a common requirement with SQL servers that are using Kerberos (ie Windows/Active Directory accounts). Report server instances (SSRS or PowerBI RS) often use it to enable queries to databases that are on separate SQL instances.
If the Report Server service doesn’t have permission to delegate to the SQL Server, it will try to connect anonymously (step 4 in the diagram above). Which results in this login error:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: <Client IP Address>]
Historically report server and SQL server services, that needed the ability to delegate authentication to other servers, were configured to run using an Active Directory user account. Enabling delegation on these accounts was simply a matter of setting the Trust level on the Delegation tab of the account’s properties (with Active Directory Users & Computers).
However, more recent systems (with Windows/SQL/Report Server version 2012 and newer) should now be running their services using standalone or group Managed Service Accounts (sMSA or gMSA). For these types of accounts, there is no Delegation tab in their AD properties info. Instead, the delegation information is set using the msDS-AllowedToDelegateTo and userAccountControl attributes.
The msDS-AllowedToDelegateTo attribute is used to specify the Service Principal Name (SPN) of the server that the MSA is allowed to forward client credentials to. The SPN information is added to the MSA’s attribute in this format:
MSSQLSvc/<FQDN> MSSQLSvc/<FQDN>:1433- MSSQLSvc specifies that the SQL service is being delegated to.
- FQDN is the Fully Qualified Domain Name of the server that is being delegated to.
- :1433 specifies the port that the SQL Server Service is using
The userAccountControl attribute determines the status of the account. Note: the value specified is a sum of all of the attribute values selected. To only allow Kerberos delegation for an MSA, the value is 4096 (WORKSTATION_TRUST_ACCOUNT). For unconstrained delegation, the value is 528384 (WORKSTATION_TRUST_ACCOUNT + TRUSTED_FOR_DELEGATION)
Note: After all of the MSA attributes have been set, the Report server may need to be rebooted for the changes to take effect.
Hello,
Very nice article, just a question about named instance.
If the SQL server is a named instance, I assume we only have to add one more entry as:
MSSQLSvc/
MSSQLSvc/:1433
MSSQLSvc/:INSTANCE
Or I’m wrong and it’s not the same as SPN ?
Stay safe, have a nice day.
Regards,
Alban
Alban –
You are correct. MSSQLSvc/:INSTANCE
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?redirectedfrom=MSDN&view=sql-server-ver15
-J
This helped me a lot. Thank you.