Configure Kerberos for SSRS Kathi Kellenberger auntkathi Can
Configure Kerberos for SSRS Kathi Kellenberger @auntkathi
Can this mild-mannered DBA configure Kerberos delegation in 75 minutes? • Why care about Kerberos? • Three tasks • Configure SPNs • App configuration • Delegation property in AD • SSRS Native and Power BI Report Server
Why? ? Kerberos not configured
Why? ? Kerberos not configured The Double Hop
Why? ? Stored credentials Report Data User and password
Why? ? Report Data
Why? ? Report Data
Common Errors The target principal name is incorrect Probably due to SPN registered to the wrong service account, especially after the service account has been changed Login failed for ‘NT AuthorityAnonymous Logon’ Using NTLM because Kerberos is not configured Repeatedly prompted for credentials SPNs misconfigured for SSRS service
DEMO
1 1 Steps to Configure Kerberos Delegation 1. Gather information 2. Add Service Principal Names (SPNs) 3. Configure application – SSRS 4. Restart SSRS service 5. Configure delegation 6. Maybe – reboot servers
Gather Information Server Type Account Port Service Name SQL 1. mydomain. local SQL Server SQLService 1 1433 MSSQLSvc SSRS 1. mydomain. local http: //ssrs 1 SSRSService 1 HTTP SQL 1. mydomain. local Tabular Olap. Service 1 MSOLAPSvc. 3 SSRS 1. mydomain. local (Named instance) http: //ssrs 1: 81 Power BI RS SSRSBIService 1 HTTP SQL 1. mydomain. local OLAP Browser Local service MSOLAPDisco. 3
Configure SPNs Service Principal Name Property of the Service Account Use the host instead if local service account is used SETSPN utility L – given an account, list the registrations Q – given a service, get the account and list the registrations S – register an SPN D – delete an SPN
SQL Server Default Instance Server Type Account Port Service Name SQL 1. mydomain. local SQL Server SQLService 1 1433 MSSQLSvc SETSPN –S MSSQLSvc/SQL 1. mydomain. local: 1433 SETSPN –S MSSQLSvc/SQL 1. mydomain. local SQLService 1
SQL Server Named Instance Server Type Account Port Service Name SQL 1/Inst 2 SQL Server SQLService 1 1434 MSSQLSvc SETSPN –S MSSQLSvc/SQL 1. mydomain. local: 1434 SQLService 1 SETSPN –S MSSQLSvc/SQL 1. mydomain. local: Inst 2 SQLService 1
1 6 SQL Server Local Service Account Server Type Account Port Service Name SQL 2 SQL Server Local 1433 MSSQLSvc SETSPN –S MSSQLSvc/SQL 2. mydomain. local: 1433 SETSPN –S MSSQLSvc/SQL 2. mydomain. local SQL 2
1 7 SSRS with service account Server Type Account SSRS 1. mydomain. local http: //ssrs 1 SSRSService 1 SETSPN –S HTTP/ssrs 1. mydomain. local SETSPN –S HTTP/ssrs 1 SSRSService 1 Port Service Name HTTP
1 8 SSRS with Local Account Server Type Account SSRS 2. mydomain. local http: //ssrs 1 SSRS Network service SETSPN –S HTTP/SSRS 2. mydomain. local SETSPN –S HTTP/SSRS 2 Port Service Name HTTP
1 9 Searching Server Type Account Port Service Name SQL 1. mydomain. local SQL Server SQLService 1 1433 MSSQLSvc SETSPN –L SQLService 1 SETSPN –Q MSSQLSvc/SQL 1. mydomain. local: 1434
DEMO: SPNs
Configure the Application RSReport. Server. config <Authentication. Types> <RSWindows. NTLM /> </Authentication. Types> Restart the service <Authentication. Types> <RSWindows. Negotiate /> <RSWindows. NTLM /> </Authentication. Types>
DEMO: SSRS Config
Set Delegation Property AD property of the SSRS service account Set on the service that delegates! Set on the computer if a local service account is used Client SSRS SQL
DEMO: Delegation Prop
Troubleshooting Time synchronization Wait for domain replication Restart servers Tools KLIST purge Kerberos Configuration Manager for SQL Server SQL Error Log Kerberos Verbose Logging [HKEY_LOCAL_MACHINESYSTEMCurrent. Control. SetControlLsaKerberosParameters] "Log. Level"=dword: 00000001 Be sure to turn off when done!!!!
2 6 Configure Power BI Report Server
Tabular Database Server Type Account SQL 1. mydomain. local Tabular Olap. Service 1 SETSPN –S MSOLAPSvc. 3/SQL 1. mydomain. local SETSPN –S MSOLAPSvc. 3/SQL 1 Olap. Service 1 Port Service Name MSOLAPSvc. 3
Power BI Report Server Type Account Port Service Name SSRS 1. mydomain. local (Named instance) http: //ssrs 1: 81 Power BI RS SSRSBIService 1 81 HTTP SETSPN –S HTTP/ssrs 1. mydomain. local: 81 SETSPN –S HTTP/ssrs 1: 81 SSRSBIService 1
OLAP Browser Service Server Type Account SQL 1. mydomain. local OLAP Browser Local service SETSPN –S MSOLAPDisco. 3/sql 1. mydomain. local SETSPN -S MSOLAPDisco. 3/sql 1 Port sql 1 Service Name MSOLAPDisco. 3
Configure the Application RSReport. Server. config <Authentication. Types> <RSWindows. NTLM /> </Authentication. Types> Restart the service <Authentication. Types> <RSWindows. Negotiate /> <RSWindows. NTLM /> </Authentication. Types>
Configure Power BI Report Server Constrained Delegation Protocol transitioning Set on SSRS service account Locate OLAP service account Locate server for browser
DEMO: Power BI RS
- Slides: 32