Kerberos for SQL Server and Sharepoint the easy

Kerberos for SQL Server and Sharepoint … the easy way! Image © Wikimedia CC

Platinum Sponsor

Gold Sponsors Please visit our Gold Sponsor stands, we couldn't do it without you…

About me � MCTS in SQL Server and Share. Point � Over a decade of Microsoft solution development and architecture � Lately focused on SQL Server 2012 BI in Share. Point Integrated Mode � I like dogs, especially big ones

What’s it going to be? � Focus on Share. Point + SQL Server � Why Kerberos � Service Principal Names � Delegation options � 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 ◦ Data driven security ◦ 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 Data Source 2 nd “hop” Kerberos only!

This is all it takes: Identify your data sources �Service Principle Names Decide on your delegation �Constrained or not? Set delegation type Allow data sources to be delegated to 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? 1 �Service Principal Name �<service class>/<Net. BIOS>[: <port or instance>] or/and 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

Set. Spn 1: SQL server Database Engine Domain 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

Set. Spn 2: SQL Analysis Services Net. BIOS: BI-SQL FQDN: Hades. Local Instance: UDM Analysis Service class SQL-SSAS Domain SSAS service account identity Host server SETSPN -S MSOLAPSVC. 3/BI-SQL: UDM HADESSQL-SSAS OR BI-SQL. HADES. LOCAL

Set. Spn 3: Share. Point Portal Site IIS server SP-WFE DNS “A” record: OLYMPUS Port: 80 Share. Point WFE FQDN: Hades. Local SP-PORTAL Share. Point Portal Application Pool identity Host server SETSPN -S HTTP/OLYMPUS HADESSP-PORTAL OR OLYMPUS. HADES. LOCAL

Set. Spn 4: Arbitrary SPN � Now I can see Delegation tab! ? Arbitrary string FQDN: Hades. Local Domain SP-XLS-SVC Delegating account SETSPN -S DUMMYSPN HADESSP-XLS-SVC Non-existing service

SPN: lessons learned � Identifies the target � Stored against target’s identity � Instance name for Analysis Services � Arbitrary SPN to show delegation tab � Don’t forget discovery services for SQL 2005 1

2 Delegation options � Basic ◦ � Constrained ◦ Only if allowed (unconstrained) To any Service

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

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

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

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

Setting Up Delegation Delegating Account NTLM or Kerberos SPN Account SP Farm Data Source

Constrained Delegation � Add 3 a dummy SPN to the Delegating account to bring up delegation tab in ADUC: � Allows trust for constrained delegation � Enables protocol transition for Share. Point

Add Allowed services (2008 AD) � Select allowed SPNs: � Use ADUC delegation tab � Locate SPN’s account � Click to select SPNs to add 4 SPN’s account

Add Allowed services (pre 2008 AD) � ADSIEdit (easier): ◦ Same string as in SETSPN statement � Power. Shell: ◦ Not for wimps ◦ Active Directory Module: �Set-ADObject �Get-ADObject �Set-KCD � CMD (document): ◦ ldifde

So, What Do I Do Again? Set your SPNs (inc Dummy and Browser 2005) � Use “Kerberos. Helper. xslx” from www. data-united. co. uk Decide: Basic or Constrained? Set delegation type Add Allowed SPNs (for constrained) Test working, Sit back and relax! Let me know if it doesn't work www. data-united. co. uk

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

C 2 WTS checklist � Starts automatically � Depends on Cryptographic Service ◦ 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 � Claims-aware services are allowed. Caller s ◦ c 2 wtshost. exe. config � Use Rodney Viana's little tool c 2 WTSTest. exe

Testing Kerberos � “NT Authority/Anonymous” is no more! � Profiler shows Your login � 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 � Sensitive Client Account

Troubleshooting Kerberos � Enable Kerberos logging (don’t forget about it!) �Registry hack http: //support. microsoft. com/kb/262177 �Check Kerberos errors in Event log on SP App server and client � ULS log (SP App server with Verbose) � Use Event log, Kerbtray and Kerberos helper tools to check for common errors � Use Klist –purge to re-test Kerberos � Use dcdiag to check SPNs

Demo time!

After… Sponsor Competition Draws in the Exhibition Hall 17: 15

Community Events www. sqlsaturday. com/202 SQL Saturday Edinburgh 7/8 June / SQL Relay 17/27 June www. sqlrelay. co. uk www. sqlsaturday. com/229 SQL Saturday Dublin 21/22 June / www. sqlsaturday. com/228 SQL Saturday Cambridge 27 September / UK User Groups All the time www. sqlserverfaq. com

Feedback � Please complete feedback � http: //sqlbits. com/SQLBits. XIThursday � http: //sqlbits. com/SQLBits. XIFriday � http: //sqlbits. com/SQLBits. XISaturday � http: //sqlbits. com/SQLBits. XI (General feedback)

We hope you had a great conference day! Keep checking www. sqlbits. com for slides, videos and news of the next conference #SQLBIT S

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 � 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 Guide for Share. Point 2013 http: //blog. blksthl. com/2012/09/26/the-first-kerberos-guide-for-sharepoint-2013 -technicians/ � 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-towindows-nt-token-service-c 2 wts-in-sharepoint-2010 -may-be-difficult-if-you-don-t-know-whereto-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) �Klist ◦ 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 ) �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