Trusted for delegation
Enable Kerberos for Microsoft SQL Server instances that make a jump using a Linked Server.
Trusted for delegation is a Windows Server policy setting. Client and server applications like Nodinite use delegation of authentication when they have multiple tiers. For this configuration and to use the Kerberos security protocol, the client and the server must run under accounts trusted for delegation.
Security account delegation allows the user to connect to multiple servers, and each server change retains the original client's authentication credentials.
Only administrators with the "Enable computer and user accounts to be trusted for delegation" credentials can set up the delegation. Domain admins and Enterprise admins have this credential. The procedure for allowing a user to be trusted for delegation depends on the domain's functionality level.
A common error for problems related to not having the "trusted for delegation" option adequately configured.
Tip
Any settings described on this page often require a reboot. Make sure to plan ahead and perform the changes during a maintenance window.
Before you read this article, please read the 'Understanding Kerberos Double Hop' first. Then, review the 'How to configure the server to be trusted for delegation' user guide for additional information.
There is a general page with recommendations and valuable comments about using SQL Server with Nodinite; make sure to read this as well.
The Kerberos security protocol is used if you use multiple SQL Server database instances for Nodinite and/or a remote (distributed) installation of Microsoft BizTalk Server for Logging.
When logging from BizTalk Server is enabled, the Logging Service initiates a process that may span multiple Log Databases, including some BizTalk Server Databases. The account running the Logging Service authenticates with SQL Server; Kerberos must be appropriately configured. Please also review the Log Database prerequisites user guide.
As seen in the diagram above, the Kerberos protocol is required when jumping from one SQL Server instance to another using a linked server.
The Kerberos protocol requires that the following Windows features are properly configured:
| Windows Feature/Setting | Comment |
|---|---|
| Trusted for delegation on the Service level | All SQL Server instances with a linked server that makes a jump to some other remote SQL Server instance |
| Account for SQL Server instance(s) | Should ALL run as a Windows Active Directory account to restrict delegation on the Service level. Also, the SPN is more likely to be registered properly in the Active Directory in this way |
| SPN | All SQL Server node names, cluster names, and listener names must be appropriately configured. Verify with the Microsoft Kerberos Configuration Manager tool |
| Firewall(s) | TCP and UDP ports used for RPC, DTC, Kerberos, DNS, and SQL Server must be allowed |
| Authentication and Authorization | The account running the Logging Service must have the proper access rights on the target databases |
Note
The domain functional level must be 2003 (or later)
Important
The account must NOT have the following option checked: 'Account is sensitive and cannot be delegated'.
Logon Server
Make the changes on the Active Directory server sponsoring the login process. The Logon Server can be found if you type Set from the command prompt:
Note
If you are performing the changes on some other Active Directory Server, then you must either wait or force a synchronization of the replication information
Make sure to run the following command to make sure your server gets the new configuration (or allow some time for your Domain Controllers to replicate the new setting first)
gpupdate /force
Tip
Use the "Microsoft Kerberos Configuration Manager tool" to validate the SPNs and ensure the proper settings to enable the "trusted for delegation" right.
Make sure to connect locally to the Nodinite SQL Server instance (then you do not need to provide any connection details).
C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server\
Above is the default install path for the "Microsoft Kerberos Configuration Manager tool".
If issues are detected, the tool provides Powershell scripts that a Domain Admin can use and execute to resolve the problem(s).
SPN
The Nodinite SQL Servers making the hop should all be listed in the SPN tab, and the status should be OK.
Delegation
In the Delegation tab, no issues should be reported.
You can enable the Trusted for delegation right in two different ways:
- Service level (Constrained delegation - recommended)
- Server level (General delegation)
1. Service level
Note
The Settings apply on the User object.
In the Active Directory, you can enable the option to grant the SQL Server account used for the Nodinite SQL Server instance the right to be 'Trusted for delegation' to the selected target services (SQL Server Services). The SQL Server instance must run with an Active Directory service account (not a local/computer account) to do so.
| Account | Comment |
|---|---|
| Nodinite SQL Server |
For this SQL Server service account, make sure to select target node names, cluster names and listener names as appropriate.
Select the service account (an Active Directory domain account) for the SQL Server instance to be trusted for delegation.
Important
Do NOT select the computer account.
Here's an example of a SQL Account with the "trusted for delegation" right set on the Service Level on selected targets.
Repeat for the target SQL Server instances as appropriate:
- SQL node name(s) (stand-alone and for any of the following combinations)
- Cluster name(s) (Failover cluster)
- Listener name(s) (Always On)
2. Server level
Note
The settings apply to the Server object
The "Trusted for delegation" option can be set in Active Directory on the server level (listener name, cluster name, node name).
Repeat as appropriate:
- SQL node name(s) (Stand-alone and for any of the following combinations)
- Cluster name(s) (Fail-over cluster)
- Listener name(s) (Always On)
Delegation on Server Level
Kerberos ticket
The steps outlined in the graph explain how the Logging Service process gets the Kerberos ticket working against the SQL Server instance for BizTalk Server: