Small IT things to remember

Reporting Services normally uses NTML as authentication when you install it but you can set it up for Kerberos Authentication with some work. The account that SSRS will use, can be one of the following three.

• Virtual Account
• Network Service
• Domain Account

Of these three the Virtual Account will not share resources with other applications. The Network Service Account will be shared with other resources. The Domain Account can share but that is up to the admin to decide. So the best options is to choose between Virtual Account and Domain Account

To see how to setup Kerberos Authentication for SSRS a new lab was created with a Domain Controller, two SQL servers, two SSRS servers, two clients one Windows 8 and one Windows 10. Two service accounts where created RS1 and RS2.
SQL Server was installed with SQL 2019 and Reporting Services was 2019 stand alone installation.
In the domain a group policy was created to have webpages that uses the fully qualified domain name aka *.my.lab

SSRS1

Reporting Services was setup with a virtual account for the service and a domain account to connect to the Reporting database. The domain account RS1 was used due to the fact that SSRS recommends that you don’t use a virtual account, aka computer account, to connect to the SQL server. If you use the virtual account to connect the report database it can lead to problems.

On the SSRS1 servers computer account the delegation was set to “Trust this computer for delegation to specified services only” and it was delegated to SQL1 that holds the database that we will run the report against.

No SPN record was created since it will use the HOST SPN

SSRS2

SSRS2 server was setup with a domain account RS2 for the Reporting Server service and the same account was also used to connect to the SQL Server for the reporting database.

For the domain account RS2 you need to create SPN record. By the documentation at Microsoft Docs SPN records was created of type HTTP/ComputerName and HTTP/ComputerName.domain. The SPN records where created by

SETSPN -S HTTP/ssrs2.my.lab rs2
SETSPN -S HTTP/ssrs2 rs2

The SPN records needs to be created before you can access the delegation tab for the user. The delegation was setup as the same way as for SSRS1 computer account in the image above. If there isn’t a delegation tab for the user account then the SPN records hasn’t been created correctly or you haven’t reloaded the domain.

For both SSRS servers the config file rsreportserver.config was modified. In the <Authentication> section <RSWindowsNegotiate /> was added so it looks like the code section below

<AuthenticationTypes>
	<RSWindowsNegotiate />
	<RSWindowsNTLM/>
</AuthenticationTypes>

After the change to the config file the servers was restarted and client was restarted.

In order to see that we are connecting with Kerberos or NTML you can use the following query

SELECT  s.session_id
	, c.connect_time
	, s.login_time
	, s.login_name
	, c.protocol_type
	, c.auth_scheme
	, s.HOST_NAME
	, s.program_name
FROM sys.dm_exec_sessions s
  JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id

Edge Chromium

HTTP://ssrs1/reports can access the report server and access the report that was created that queries SQL1.

HTTP://ssrs1.my.lab/reports can access the report server and access the report that was created that queries SQL1.

HTTP://ssrs2/reports can access the report server and access the report that was created that queries SQL1.

HTTP://ssrs2.my.lab/reports can access the report server and access the report that was created that queries SQL1.

The result of the above TSQL query was that all Edge Chromium was done over Kerberos authentication

IE 11

HTTP://ssrs1/reports can access the report server and access the report that was created that queries SQL1.

HTTP://ssrs1.my.lab/reports can access the report server and access the report that was created that queries SQL1.

HTTP://ssrs2/reports can access the report server and access the report that was created that queries SQL1.

HTTP://ssrs2.my.lab/reports can access the report server and access the report that was created that queries SQL1.

The result of the above TSQL query was that all Edge Chromium was done over Kerberos authentication

All looks good and we are done, well that is until someone needs to do some remote administration to SSRS2 like adding the server to Server Mananger

The above errors are due to the fact that we have said that HTTP is used by the RS1 account and not by the Computer Account.
To solve this is to remove the SPN we just created. But when we remove the SPN Kerberos double hop will stop working.

SETSPN -D HTTP/ssrs2 rs2
SETSPN -D HTTP/ssrs2 rs2

According to SPN documentation you could add port to the SPN, just using http in the test

SETSPN -S HTTP/ssrs2.my.lab:80 rs2
SETSPN -S HTTP/ssrs2:80 rs2

After adding the above SPN restart the server and the client used in the test. This time when accessing http://ssrs2/reports and http://ssrs2.my.lab/reports a login prompt is displayed. So even if the SPN documentation says you can use ports it will not work with SSRS at least. Though this solution solved the PowerShell remote problem.

CNAME

Another solution that could work is to use a CNAME to access the SSRS. After the CNAME report2 is created for ssrs2 the SPN should be created

SETSPN -S HTTP/cname.my.lab rs2
SETSPN -S HTTP/cname rs2

After a reboot to server and client (just to make sure) the test in Edge Chromium results in that we again gets a login prompt. Looking in the clients event log System shows an error for Source-Kerberos

A solution for this error when using CNAME I haven’t found so far.

A-Record

Instead of using a CNAME an alternative is to use a different A-record. Using an A-Record means that you have to use static IP addressing to the server either by do a static DHCP record or by using static IP on the server.

After the A-Record in the DNS server is created, the SPN record needs to be created on the service account

SETSPN -S HTTP/arecord.my.lab rs2
SETSPN -S HTTP/arecord rs2

Accessing the report server now by using http://arecord.my.lab/reports and http://arecord/reports works without any problem.
Accessing the report server by its hostname will result in a login prompt that will not go away.

Conclusions

So far my testing of SSRS and double hop is that using the virtual account with Kerberos delegation on the computer account is the easiest way to achieve the double hop to the source from the SSRS server. If domain account must be used for the service account then an other A-record is needed to not break remote administration.