Kerberos for SQL Server and Share Point the

Kerberos for SQL Server and Share. Point … the easy way

About me • SQL Server BI and Share. Point professional (MCSE, MCTS, etc. ) • Nearly two decades of working with data • Lately focused on Microsoft BI solution design and architecture • I like dogs, especially big ones, hence Kerberos • • • Dmytro Andriychenko: Dmytro@data-united. co. uk +44 775 878 1262

What’s it going to be? • Focus on Share. Point -> SQL Server delegation • Why Kerberos • Service Principal Names • Delegation • Claims & Kerberos • Testing &Troubleshooting • Live Demo! DON’T PANIC

Kerberos: why bother? • More secure, Less DC load, interoperability. . . • Enables Delegation! • Unified Security at data source level • Facilitates compliance with GDPR • Facilitates security attestation • Data driven security • Facilitates personalised reports NTLM or Kerberos SP Farm or DB server Kerberos Delegation Data Source

Kerberos delegation NTLM or Kerberos 1 st “hop” Any protocol SP Farm Impersonate user 2 nd “hop” Kerberos only! Data Source

This is all it takes: (after all but Kerberos is already working ) Identify your data source services • Service Principle Names Configure delegation in AD Grant local privileges (for delegation) Ensure Claims to Windows transition (C 2 WTS) Easy, right?

SPN: where is my service? • Service Principal Name • What (Service) and • Where (Computer or “Principal”) to connect to • Identifies the target • Not the delegating service • Certainly not the client • The Data Source Service! 1

So, how you gonna do it? • Service Principal Name • • <service class>/<Net. BIOS>[: <port or instance>] or Host identity <service class>/<FQDN>[: <port or instance>] setspn. exe -S <SPN> <Account. Name> Service identity: �Service account as <domainusername> or �Host Account if running as Local System 1

Set. Spn 1: SQL server Database Engine Domain Name: Hades. Local Net. BIOS: BI-SQL FQDN: Hades. Local SQL-DB Port: 49753 Database service class Database service account identity Host server SETSPN -S MSSQLSVC/BI-SQL: 49753 HADESSQL-DB OR BI-SQL. HADES. LOCAL 1

I want it easy! • Let your domain admin install your SQL Server • Use Active Directory. Managed Service Accounts • Use Kerberos Configuration Manager : 1

Set. Spn 5: Arbitrary SPN Domain Name: Hades. Local Where is that Delegation tab? ! ? FQDN: Hades. Local Domain SP-XLS-SVC Delegating account Arbitrary string SETSPN -S DUMMY/SPN HADESSP-XLS-SVC Non-existing service 1

Set. SPN for HTTP 1 • Required to authenticate using Kerberos to WFE • Different in IIS 6 and IIS 7 • Kernel Mode Authentication ?

Set. Spn 3: Share. Point Portal Site IIS 6 or IIS 7 non-kernel mode (default) Domain Name: Hades. Local IIS server SP-WFE FQDN: Hades. Local DNS “A” record: OLYMPUS Port: 80 Share. Point WFE SP-PORTAL Share. Point Portal Application Pool identity Site Name SETSPN -S HTTP/OLYMPUS HADESSP-PORTAL OR OLYMPUS. HADES. LOCAL 1

Kernel-Mode authentication • IIS 7 Default 1 • Done by HTTP. sys under Local System • SPNs are set only for the IIS computer • Easier, faster: best practice • Application Pool identity is not normally used • … unless use. App. Pool. Credentials="true" in applicationhost. config

Set. Spn 4: Share. Point Portal Site: IIS 7 kernel mode Domain Name: Hades. Local IIS server SP-WFE FQDN: Hades. Local DNS “A” record: OLYMPUS Port: 80 Share. Point WFE IIS Host Site Name SP-PORTAL Share. Point Portal Application Pool identity SETSPN -S HTTP/OLYMPUS HADESSP-WFE AND OLYMPUS. HADES. LOCAL 1

SPN: lessons learned • Identifies the target • Stored against target’s identity • Instance name for Analysis Services (instead of port) • Don’t use App Pool Identity in Kernel Mode • Arbitrary SPN to show delegation tab 1

Delegation Delegating Account NTLM or Kerberos SP Farm 2 SPN Account Data Source

Configure Delegation: Active Directory (AD) • Add a dummy SPN to the Delegating account to bring up delegation tab in AD Users & Computers (ADUC): • Allow trust for constrained delegation • Enable protocol transition for Share. Point 2

Add “Allowed Services” in ADUC • Select allowed SPNs: • Use ADUC delegation tab • Locate SPN’s account • Click to select SPNs to add SPN’s account 2

Other ways of Adding Allowed services • • • ADSIEdit (easier): • • Edit attribute “ms. DS-Allowed. To. Delegate. To” Same string as in SETSPN statement Power. Shell: • • Active Directory Module: • • Get/Set-ADObject, Set-KCD Requires AD Feature installed System. Directory. Services – ready! CMD (document/verify only): • ldifde 2

Configure Delegation: local privileges 3 • For windows AD service accounts (not Local System), use secpol. msc to set • • • Act as operating system Impersonate a client after authentication Log On as a Service • Delegating account needs permissions to the databases in Share. Point web applications: • • $w = Get-SPWeb. Application -Identity http: //SP 2016 $w. Grant. Access. To. Process. Identity("ADsvc-sp-ssrs") • Allow Kerberos Authentication (Negotiate): Share. Point Site Address Delegating Service Identity

. . and don’t just blame Kerberos • Claims to Windows Token Service (C 2 WTS) • NTLM or Kerberos Share. Point protocol transition: STS Share. Point Web Frontend Claims Share. Point Application Server C 2 WTS Windows UPN Token Claim ? Kerberos Delegation! Data Source 4

• Starts automatically (default) • Depends on Cryptographic Service • C 2 WTS checklist sc config c 2 wts depend= Crypt. Svc • Service Identity is trusted for delegation • • Local System by default (and should stay that way) If changed to Windows Identity, must be a local admin and have additional local policy rights: • • • Act as operating system Impersonate a client after authentication Log On as a Service • Delegating services are allowed. Callers • 4 c 2 wtshost. exe. config • To test, use Rodney Viana's tool c 2 WTSTest. exe

So, What Do I Do Again? Set your SPNs (inc arbitrary and Browser 2005) • Use Microsoft Kerberos Configuration Manager Configure Delegation in Active Directory Grant Local Privileges (Delegating service) Claims to Windows Token Service Test working, Sit back and relax! Let us know if it doesn't work

Testing Kerberos • “NT Authority/Anonymous” is no more! • Profiler shows Your login (SSAS) • Test every service against every data source SSRS

Gotcha! • 15 character limit on Windows Net. BIOS • Open Port 88 on Firewall • SPN for SQL 2005 browser/discovery services • IIS 7 Kernel mode • Sensitive Client Account

Troubleshooting Kerberos • Enable Kerberos logging (don’t forget about it!) • • Registry hack http: //support. microsoft. com/kb/262177: no reboot required! Check Kerberos errors in Event log on SP App server and client • Microsoft® Kerberos Configuration Manager • Generate & Test SPNs & delegation settings • Microsoft Message Analyser (fka Net Monitor) or Wire. Shark • Filter Kerberos packets (Kerberos. V 5) and look at the actual SPNs requested • ULS log (SP App server with Verbose) for Ct. WTS • Event log, Kerbtray, dcdiag and Kerberos helper: check errors

Demo time!

Demo setup SQLServer 2016 Host Name: Tabular instance: default Share. Point Server: SP 2016 SSRS 2016 OLAP Cube instance name: DIM Svc-sp-ssrs Office Online Server (fka OWA): OOS Domain Name: Excel Services Svc-sp-xls DW DB: default AD. Data. United. co. uk Hypervisor: Hyper-V

Demo SPN setup Domain Name: AD. Data. United. co. uk Tabular instance: default Net. BIOS: OLAP Cube instance name: DIM DB Port: 1433 (default) Host server FQDN: AD. Data. Uni ted. co. uk svc. SQL Domain One account for all services Analysis Service class Cube: SETSPN -S MSOLAPSVC. 3/OLAP: DIM ADsvc. SQL Tabular: SETSPN -S MSOLAPSVC. 3/OLAP ADsvc. SQL DW DB: SETSPN -S MSSQLSVC/OLAP ADsvc. SQL

Glossary • Kerberos: authentication protocol • Principal – a computer in the Kerberos protocol, usually the target • UPN: user principal name • FQDN: Fully Qualified Domain Name • WCF: Windows Communication Foundation (. NET) • C 2 WTS: WCF service granting windows token for a UPN claim

Links: getting started • Kerberos Guide for Share. Point 2013 http: //blog. blksthl. com/2012/09/26/the-first-kerberos-guide-for-sharepoint-2013 -technicians/ • Kerberos for SSRS in Share. Point Integrated mode https: //blogs. technet. microsoft. com/sharepoint_-_inside_the_lines/2013/05/28/sharepoint-2013 -with-ssrs-2012 -and-constraineddelegation/ • Setting up Excel Services in OOS 2016 including C 2 WTS and Kerberos https: //whitepages. unlimitedviz. com/2016/01/adding-excel-services-capabilities-to-a-sharepoint-2016 -farm/ • How the Kerberos Version 5 Authentication Protocol Works http: //technet. microsoft. com/en-us/library/cc 772815(v=ws. 10). aspx • Overview of Kerberos authentication for Microsoft Share. Point 2010 Products http: //technet. microsoft. com/en-us/library/gg 502594. aspx • Kerberos Blog and Resources www. data-united. co. uk

Links: serious business • Kerberos using Power. Shell http: //blog. msresource. net/2012/07/12/fim-service-principal-names-and-kerberos-delegation/ • Troubleshooting C 2 WTS by Rodney Viana http: //blogs. msdn. com/b/rodneyviana/archive/2011/07/19/troubleshooting-claims-to-windows-nt-token-servicec 2 wts-in-sharepoint-2010 -may-be-difficult-if-you-don-t-know-where-to-start. aspx • Kerberos Professional Services www. data-united. co. uk

Scripting tips: Command Prompt • Command Prompt • • • List all Kerberos Tickets on the principal (a ticket must be present for the URL, otherwise NTLM is used) • Purge Kerberos Tickets (run on all principals to avoid reboot/wait) • Klist –purge List all ms. DS-Allowed. To. Delegate. To properties for a single account (only computers with ) • • Klist ldifde -f c: tempfilename. txt -d "CN=SA_SVC_C 2 WTS, OU=Service Accounts, DC=contoso, DC=msft, DC=com" -l ms. DS-Allowed. To. Delegate. To List all ms. DS-Allowed. To. Delegate. To properties all accounts in an OU: • ldifde -f c: tempfilename. txt -d "OU=Service Accounts, DC=contoso, DC=msft, DC=com" -l ms. DS-Allowed. To. Delegate. To

Delegation options • Basic (unconstrained) • To any service and across domains • Constrained • Only if allowed and within a domain 2

Constrained or not (basic)? � Basic (uncostrained) • Delegates to any service � • Constrained Cross-domain delegation service can use • ◦ Any No protocol transition require • ◦ Most Can precede constrained ◦ More secure ◦ Only delegates if � Constrained allowed! ◦ Only within a domain SSRS 2

. . . speaking of domain boundaries MSFT. com Domain boundary contoso. MSFT. com sintoso. MSFT. com No Trust is OK! Client NTLM Share. Point Farm Kerberos Constrained delegation works! Data Source 2

Trust is a must: 2 MSFT. com contoso. MSFT. com sintoso. MSFT. com Must have Two Way Trust Client NTLM or Basic Kerberos Share. Point Farm Basic Kerberos ONLY Data Source

So, which one then? �Use Basic for • SSRS (SQL Reporting Services) to connect to another domain • When security is not that critical • Use Constrained for • Any other case! 2