sql-serverreporting-servicesactive-directorykerberosspn

Enabling Kerberos for Microsoft SQL Server Reporting Services

I am trying to enable Kerberos for MS SQL Server Reporting Services. I am fairly familiar with Windows security and how it works. However, I am new to Scale Out Deployment and Clustering of Windows Servers. I know I need to add SPNs that direct towards both SSRS web service (using a domain account) and to the database engine. I am a little confused on exactly how the SPNs should be structured. I have looked in Stack Overflow and other resources and most reference a standard deployment without scale-out/clustering.

My questions are:

Here is what I think I need to add:

setspn -s http/DEV-CLUSTER.clearcaptions.com ssrsuser
setspn -s MSSQLSvc/DEV-CLUSTER1.clearwd.com DEV-CLUSTER1$
setspn -s MSSQLSvc/DEV-CLUSTER1 DEV-CLUSTER1$
setspn -s MSSQLSvc/DEV-CLUSTER1.clearwd.com:1433 DEV-CLUSTER1$
setspn -s MSSQLSvc/DEV-CLUSTER1:1433 DEV-CLUSTER1$

Here are the details of my setup:

Domain: clearwd (not my actual domain)

Server OS: Windows Server 2016

Cluster: DEV-CLUSTER

Node 1: DEV-SQL1

Node 2: DEV-SQL2

Role: DEV-CLUSTER1 (SQL Server / MSSQLSERVER)

SQL Server Version: 2016 Enterprise

SQL Server Name: DEV-CLUSTER1

SQL Server Port: 1433

SSRS Service Account: ssrsuser.clearwd.com OR clearwd\ssrsuser

SSRS Mode: Native

SSRS Report Server Web Service URL: http://DEV-SQL01:80/ReportServer

SSRS Web Portal URL: http://DEV-SQL01:80/Reports

Let me know if any other information is needed.

References:

https://www.itprotoday.com/sql-server/implement-kerberos-delegation-ssrs-0

https://learn.microsoft.com/en-us/sql/reporting-services/report-server/register-a-service-principal-name-spn-for-a-report-server?view=sql-server-2017

https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/configure-report-server-urls-ssrs-configuration-manager?view=sql-server-2017

Solution

  • "Microsoft Kerberos Configuration Manager for SQL Server" can be used on every involved server to get the overview on a configuration and required SPNs. So far I remember, it supports clustered installations also:

    The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions:

    • Gather information on OS, Microsoft SQL Server instances and Always On Availability Group Listeners installed on a server.
    • Report on all SPN and delegation configurations on the server.
    • Identify potential problems in SPNs and delegations. Fix potential SPN problems.

    p.s. I think that this kind of questions will have better engagement on a: dba.stackexchange.com