Constrained Delegation and Power BI Gateway SSO or SSRS - Delegation... oooo..ooo delegation, it's what we need...

Ah, the elderly parent, the old warhorse SQL Server Reporting Services, and the young(ish) upstart Power BI and its On Premises Data Gateway

Yes, SSRS has been around since the time of the period of heavy bombardment, and no longer getting any love from Microsoft, but a lot of companies still have it. Whether its due to a lack of desire/requirement to switch to PowerBI etc or not, some folks still have to

support it for a while longer...

This applies to SSRS and Power BI On Premises Data Gateway, as if you want to use Integrated Authentication , both of the products above make use of Kerberos.

All as security requirements get tighter and tighter, so some companies allow Unconstrained delegation, others insist on Constrained Delegation ...and if you want PBI/SSO... your hand is forced.  In the case of PBI gateway, Constrained Delegation is required for single sign on with credentials passed through.

Delegation

Wait, constrained what now?

Ok, many of you will already be aware that in order to use Integrated Authentication successfully with SSRS particularly, that you have to configure Kerberos Authentication. At a very basic level, this allows the credentials of the user running the report, to be passed to the report server (hop 1) and then along to the target of the SSRS datasource (hop 2), also known as "Double hop" authentication. The delegation part of this signifies where the service (PBIG or SSRS) is allowed to pass these credentials along to.

• anywhere, ie...Unconstrained delegation, or

• to a restricted set of targets...Constrained delegation.

Kerberos Authentication

There are many resources out there already describing how to do this, however here is a very short version incase anyone is starting from scratch with this.

1. Configure PBIG/SSRS to run with a Domain service account.

SSRS

2. Set up the following SPNs for your SSRS Server. You may need the help of your friendly Domain Administrators for this, as it requires high permissions in Active Directory. If they resist, buy them cake, everyone likes cake.

Setspn -s http/<SSRS-server-name>.<domain-name> <SSRS-service-account>

3. On your SSRS server, search and open the RSReportServer.config file.

Locate the Authentication section, and add <RSWindowsNegotiate /> to this section

(or <RSWindowsKerberos />) above any <RSWindowsNTLM > line if it exists.

<Authentication>
    <AuthenticationTypes>
        <RSWindowsNegotiate />
    </AuthenticationTypes>
    <EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

Reference: Register a Service Principal Name (SPN) for a Report Server - SQL Server Reporting Services (SSRS) | Microsoft Learn

PBI Gateway

1. Create the SPN for your PBI Gateway service account.

Setspn -s gateway/<PBIG-server-name>.<domain-name> <PBIG-service-account>

Reference: Configure Kerberos-based SSO from Power BI service to on-premises data sources - Power BI | Microsoft Learn

Great, now we have the SPNs in place, then we should have a Delegation

tab on the A.D. user object for your service account.

Open that, and now you need to select 'Trust this User for delegation to specified services only', choose the authentication protocol, and then add the SPN Service type and Target of where the account may pass delegated credentials. Ie... the target servers that make up your datasources. This is Constrained Delegation.

For completeness, the middle radio button "Trust this user for delegation to any service (Kerberos only)" is Unconstrained delegation.

And that's it.

But what do you have to use for the above?

Well...the Delegation tab you see for the Service account of the service that you want to enable Constrained Delegation against, ie.. The domain service account used by SSRS, or the Power BI gateway service.

The Services you enter are the SPN details for the service account for each datasource that you which to allow Constrained Delegation to. Yes, that's each and every one that needs its SPN adding. Unfortunately, its called constrained delegation for a reason.

Lets not click, lets code.

First off, lets grab the service accounts if we don't have them already.

I'm making use of DBATools again (link), as I love it.

First, grab the service account for the supplied Target SQL datasource.

# SQL Datasource Service Account
$sqlinstance='MYSQLSERVER\MYINSTANCE'
If ($sqlinstance) {
        Write-Host "Getting Service account for $SQLServer\$sqlinstance" -ForegroundColor Cyan
        $sqlservices=Get-DbaService $sqlserver | Where {($_.InstanceName -eq $sqlinstance ) -and ($_.ServiceType -eq 'Engine')}
}

else {
    Write-Host "Getting Service account for $SQLServer" -ForegroundColor Cyan
    $sqlservices=Get-DbaService $sqlserver | Where {($_.InstanceName -eq 'MSSQLSERVER' ) -and ($_.ServiceType -eq 'Engine')}
}

$sqlsvcacc=($sqlservices | Select Startname,ServiceType | Where ServiceType -eq 'Engine').Startname
}
Write-Host $sqlsvcacc -ForegroundColor Magenta

Now we need to get the SPNs that are associated with this service account, and build up the required object

# Get the SPNs for the SQL Server Service account where the Datasource points

$SPNs = Get-dbaspn -Accountname $sqlsvcacc | Select SPN
$spnlist=$null
$spnlist=@{}
$spnlist."msDS-AllowedToDelegateTo"= @()
ForEach ($spn in $Spns){ 
    $spnlist."msDS-AllowedToDelegateTo" += $spn.spn
}

If (-not $spnlist){
    Write-Host "NO SPNS associated to Service account $sqlsvcacc.  EXITING" -ForegroundColor Red
    Return
}

Write-Host "SQL Service Acc: $($sqlsvcacc)" -ForegroundColor Cyan
Write-Host "SPNs associated :"

$SPNs.SPN

Now we want to add our SPN list of the SQL Datasource Target Service account to the Service account of the Reporting software, be that the SSRS Service account, or the PowerBI Data Gateway Service account. The code below uses $ssrssvcacc as a variable name (as thats what I wrote it for originally) , but its the same for SSRS or PBIG.

$ssrssvcacc='MYDOMAIN\MYSSRSServiceAccount' # Or OPDG service account

# Check if the account exists as a domain login
try {
    $ssrssvcacc=$ssrssvcacc.Replace('MYDOMAIN\','')
    Get-ADUser -Identity $ssrssvcacc -ErrorAction Stop
    Write-Host "Account '$ssrssvcacc' is a domain login." -ForegroundColor Cyan
    Set-ADAccountControl -Identity $ssrssvcacc -TrustedForDelegation $false -TrustedToAuthForDelegation $true
    Set-ADUser $ssrssvcacc -Add $spnlist
    Write-Host "Constrained Delegation Enable SUCCESS for: $ssrsserver > $SQLServer\$sqlinstance " -Foregroundcolor Green
}

catch {
    Write-Host "Account '$ssrssvcacc' is not a domain login."  -ForegroundColor Cyan 
}

# Check if the account exists as an MSA

try {
    $ssrssvcacc=$ssrssvcacc.Replace('MYDOMAIN\','')
    Get-ADServiceAccount -Identity $ssrssvcacc -ErrorAction Stop
    Write-Host "Account '$ssrssvcacc' is an MSA."  -ForegroundColor Cyan
    Set-ADAccountControl -Identity $ssrssvcacc -TrustedForDelegation $true -TrustedToAuthForDelegation $true
    Set-ADServiceAccount -Identity $ssrssvcacc -Add $spnlist 
    Write-Host "Constrained Delegation Enable SUCCESS for: $ssrsserver > $SQLServer\$sqlinstance " -Foregroundcolor Green
}

catch {
    Write-Host "Account '$ssrssvcacc' is not an MSA."  -ForegroundColor Cyan
}

And that's it, done, dusted, new records set. Check the delegation property on your SSRS/OPDG service account to see the new targets set.

But who only has one datasource to delegate to? I certainly don't...and in CI/CD environments, these can change very quickly so you need a helping hand.

I'll add posts In future around options you can use to automate the discovery of datasources and inclusion into the Constrained Delegation lists.

As always, thanks for reading.

Rod Castle.

RIP Roy Castle OBE. Record breaking legend.