In recent times there has been a lot of security issues and considerations around Kerberos. A lot of organizations are still using unconstrained delegation with protocol transition which allows hackers to exploit vulnerabilities and impersonate any account in the domain, rendering authentication almost useless in certain cases.
Let’s go back to SQL.
Let’s review teh below scenario:
ForestA DomainA: Server network
ForestB DomainB: Employee network
two-way trust between ForestA and ForestB
ForestA SQLServerA: Reporting
ForestA SQLServerB: Customer Data
In order to access customer data in the reporting sql server, the employee needs to authenticate from DomainB to DomainA across forests, then login to SQLServerA and access data in SQLServerB. This creates a situation where you need to support Kerberos in double-hop scenario. According to various resources and Microsoft official blog articles, the way to do it is by using unconstrained delegation and/or protocol transition. Either unconstrained delegation, or constrained delegation with protocol transition is to be used:
The correct resolution for the specific problem mentioned in the Microsoft article is to trust the account for delegation as selecting „trust this computer for delegation to specific services only“ and „use kerberos only“ does not set the flag in the account, making it impossible to renew tickets. Easiest way is by using Powershell:
Set-ADUser -Identity SQLAccount -TrustedForDelegation $true
There are two huge disadvanteges to both unconstrained delegation and protocol transition:
A. They leave massive security holes. For example, protocol transition allows the service to impersonate any account, without proving that they are allowed to. They can impersonate domain admins, sensitive accounts, anything.
B. They don’t work across forests. Neither unconstrained delegation or protocol transition are supported across forests. And rightly so.
So what is the solution if you don’t want unconstrained delegation or protocol transition and still need double hop across forests?
Resource-Based Constrained Delegation (RBCD)
Step one is to register the SPNs as you would normally do for each server, or if correct permissions are set, it would happen automatically as well. Then, you need to add any SPNs that would be allowed in the SQL Server service account:
Once all needed SPNs are listed, it is best to clear all cached kerberos tickets on the servers and restart the SQL services, preferably the servers themselves.
One very important aspect here is to trust the SQL Service account for delegation. Even though „Trust this user for delegation to specific services only“ is selected, the account is still missing the delegation flag so it needs to be set manually:
Set-ADUser -Identity SQLAccount -TrustedForDelegation $true
Without this, once the 10 hour service ticket time expires, the SQL service would not be able to renew it.
Now, there is still one more consideration – maximum ticket lifetime. By default service ticket lifetime is 10 hours, maximum ticket renewal time is 7 days. This means after 7 days you must get a new ticket, you can’t renew. If you have application that is sitting idle for over 7 days without reconnecting to the SQL, it will fail double hop since the ticket would have expired and the SQL Server is not allowed to obtain new ticket on behalf of the client. Extensive testing had shown that SQL server has no problem obtaining tickets using it’s own service account, meaning internal processes would not fail. Applications like IIS have much shorter session lifetime so their sessions are not sitting nearly long enough for this to be a problem. However, if you have custom applications that have infinite idle timeout, it would be best to kill sessions that are sitting idle for longer than, say, 6 days, if you have 7 days maximum ticket lifetime limit set in Kerberos. This can be done using a job in SQL Server, killing an idle session does not cause any errors unless query is actually running at the time. Doing this our of active hours is feasible solution, then any new session would re-authenticate automatically. Before doing all this, make sure you actually have sessions sitting for that long – in most scenarios, there should be no sessions running that long. Check session duration using the below query and observe the login_time column:
SELECT *
FROM sys.dm_exec_sessions
where nt_domain = 'ANYCORP'
order by login_time ascChange ANYCORP to your domain to filter out only domain-authenticated sessions. Keep in mind there are always sessions from SQLAgent which are active since the SQL Server has been started, they are not renewed automatically even when Unconstrained Delegation is used, it is fine to have these sessions past the maximum ticket lifetime, the Job Agent is not going to be affected by that. The sessions are named SQLAgent – Email Logger and SQLAgent – Generic Refresher.
Bonus implementation: Accessing Network Shares in double-hop with RBCD
Now, with all of the above in place, you should have no problems accessing SQL resultsets with double-hop, but what about reading csv using openrowset in double-hop through linked server?
Example: select * from openquery ([SQLServerB\SQLEXPRESS],'SELECT * FROM OPENROWSET(BULK ''\\share-fs-01\Share\test_sample.csv'', SINGLE_CLOB) AS DATA;')
By default, the above would result in access denied error, because the SQL Service account is not allowed to access that service and cannot obtain session for it due to the SMB protocol which passes the authentication through the SQL Server computer object and not the actual service account. In order to get access, we need to list all computer objects of SQL servers that would be accessing the network share through double-hop. Here is an example PowerShell to do that:
#Define the network share server computer object
$FileShare = Get-ADComputer -Identity net-share-serv
#List all SQL Servers that are going to need access to that network share via double-hop
$DBServer01 = Get-ADComputer -Identity DBServer01
$DBServer02 = Get-ADComputer -Identity DBServer02
$DBServer03 = Get-ADComputer -Identity DBServer03
#Define Array with the above objects
$SQLServers = @(
$DBServer01,
$DBServer02,
$DBServer03
)
#Set the computer object of the network share server to allow access for the SQL Servers
Set-ADComputer -Identity $FileShare -PrincipalsAllowedToDelegateToAccount $SQLServersFinal Words
Based on the above, you should have everything needed to succesfully run SQL Server with double-hop without needing unconstrained delegation or protocol transition! Contratulations on securing your environment and allowing double-hop across forests! 🙂