1

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:

  • Do I structure the SPNs to point to the node, the cluster, both?
  • How do I structure the SPNs for each?
  • Do I need to include ports?

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

lawlesm
  • 13
  • 1
  • 5

1 Answers1

0

"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

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Thanks for the advice on DBA Stack Exchange. I downloaded the Tool for Kerberos. It looks like someone added an SPN previously. It shows a status of "Unauthorized". Any idea what that means? There is very little documentation out there on this tool... – lawlesm Mar 21 '19 at 14:47
  • @lawlesm, "Unauthorized". The delegation permissions are not granted on a computer object in Active Directory? – Alexander Volok Mar 22 '19 at 08:50
  • 1
    Delegation was granted. My Windows admin removed and added the SPN back in and it resolved... – lawlesm Mar 28 '19 at 20:16