Transcript Kerberos for SQL Server

… the easy way!
Image © Wikimedia CC
Please visit our Gold Sponsor stands,
we couldn't do it without you…




MCTS in SQL Server and SharePoint
Over a decade of Microsoft solution development and
architecture
Lately focused on SQL Server 2012 BI in SharePoint
Integrated Mode
I like dogs, especially big ones







Focus on SharePoint + SQL Server
Why Kerberos
Service Principal Names
Delegation options
Claims & Kerberos
Testing &Troubleshooting
Live Demo!
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
NTLM or
Kerberos
1st “hop”
Any protocol
SP Farm
Impersonate
user
Data Source
2nd “hop”
Kerberos only!
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?
1

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
 Service Principal Name
 <service class>/<NetBIOS>[:<port or instance>]
or/and
Host identity
 <service class>/<FQDN>[:<port or instance>]
setspn.exe -S <SPN> <AccountName>
Service identity:
 Service account as <domain\username>
or
 Host Account if running as Local System
Domain
NetBIOS:
BI-SQL
FQDN:
Hades
.Local
SQL-DB
Port: 49753
Database
service class
Database service
account identity
Host server
SETSPN -S MSSQLSVC/BI-SQL:49753 HADES\SQL-DB
OR
BI-SQL.HADES.LOCAL
NetBIOS:
BI-SQL
FQDN:
Hades.
Local
Instance:
UDM
Analysis Services
Service class
SQL-SSAS
Domain
SSAS service
account identity
Host server
SETSPN -S MSOLAPSVC.3/BI-SQL:UDM HADES\SQL-SSAS
OR
BI-SQL.HADES.LOCAL
IIS server
SP-WFE
DNS “A”
record:
OLYMPUS
Port: 80
SharePoint WFE
FQDN:
Hades.
Local
SP-PORTAL
SharePoint Portal
Application Pool identity
Host server
SETSPN -S HTTP/OLYMPUS HADES\SP-PORTAL
OR
OLYMPUS.HADES.LOCAL

Now I can see Delegation tab!
?
FQDN:
Hades.
Local
Domain
SP-XLS-SVC
Delegating account
Arbitrary string
SETSPN -S DUMMYSPN HADES\SP-XLS-SVC
Non-existing service
1





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 SQL2005
2

◦

Constrained
◦ Only if allowed
Basic (unconstrained)
To any Service
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


SSRS

MSFT.com
contoso.MSFT.com
pintoso.MSFT.com
No
Trust is OK!
Client
NTLM
SharePoint
Farm
Kerberos
Data
Source
Constrained delegation
works!
MSFT.com
contoso.MSFT.com
Client
NTLM
or Basic
Kerberos
Must have
Two Way Trust
SharePoint
Farm
pintoso.MSFT.com
Basic
Kerberos
Data
Source
2

Use Basic for
◦ SSRS (SQL Reporting Services) to connect to another domain
◦ When security is not critical

Use Constrained for
◦ Any other case!
Delegating Account
NTLM or
Kerberos
SPN Account
SP Farm
Data Source
3



Add a dummy SPN to the Delegating account to bring
up delegation tab in ADUC:
Allows trust
for constrained
delegation
Enables protocol
transition
for SharePoint




Select allowed SPNs:
Use ADUC delegation tab
Locate SPN’s account
Click to select SPNs to add
4
SPN’s account

ADSIEdit (easier):
◦ Same string as in
SETSPN statement

PowerShell:
◦ Not for wimps
◦ Active Directory Module:
 Set-ADObject
 Get-ADObject
 Set-KCD

CMD (document):
◦ ldifde
Set your SPNs (inc Dummy and Browser 2005)

Use “KerberosHelper.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

Claims to Windows Token Service (C2WTS)
◦ SharePoint protocol transition:
C2WTS
STS
NTLM
SharePoint
or
Web
Kerberos
Frontend
Claims
SharePoint
Application
Server
Windows
UPN
Token
Claim
?
Kerberos
Delegation!
Data
Source


Starts automatically
Depends on Cryptographic Service
◦ sc config c2wts depend= CryptSvc

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 allowedCaller s
◦ c2wtshost.exe.config

Use Rodney Viana's little tool c2WTSTest.exe



“NT Authority/Anonymous” is no more!
Profiler shows Your login
Test every service against every data source
SSRS




15 character limit on
Windows NetBIOS
Open Port 88 on Firewall
SPN for SQL 2005
browser/discovery services
Sensitive Client Account

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
After…
Sponsor Competition
Draws in the Exhibition
Hall 17:15
Community Events
SQL Saturday Edinburgh 7/8 June
www.sqlsaturday.com/202/
SQL Relay
17/27 June www.sqlrelay.co.uk
SQL Saturday Dublin
21/22 June www.sqlsaturday.com/229/
SQL Saturday Cambridge 27 September www.sqlsaturday.com/228/
UK User Groups
All the time www.sqlserverfaq.com

Please complete feedback

http://sqlbits.com/SQLBitsXIThursday
http://sqlbits.com/SQLBitsXIFriday
http://sqlbits.com/SQLBitsXISaturday
http://sqlbits.com/SQLBitsXI (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






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)
C2WTS: WCF service granting windows token for a UPN claim

How the Kerberos Version 5 Authentication Protocol
Works
http://technet.microsoft.com/en-us/library/cc772815(v=ws.10).aspx

Overview of Kerberos authentication for Microsoft
SharePoint 2010 Products
http://technet.microsoft.com/en-us/library/gg502594.aspx

Kerberos Guide for SharePoint 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

Kerberos using PowerShell
http://blog.msresource.net/2012/07/12/fim-service-principal-names-and-kerberos-delegation/

Troubleshooting C2WTS by Rodney Viana
http://blogs.msdn.com/b/rodneyviana/archive/2011/07/19/troubleshooting-claims-towindows-nt-token-service-c2wts-in-sharepoint-2010-may-be-difficult-if-you-don-t-know-whereto-start.aspx

Kerberos Professional Services
www.data-united.co.uk

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 msDS-AllowedToDelegateTo properties for a single account (only
computers with )
 ldifde -f c:\temp\filename.txt -d "CN=SA_SVC_C2WTS,OU=Service
Accounts,DC=contoso,DC=msft,DC=com" -l msDS-AllowedToDelegateTo
◦ List all msDS-AllowedToDelegateTo properties all accounts in an OU:
 ldifde -f c:\temp\filename.txt -d "OU=Service Accounts,
DC=contoso,DC=msft,DC=com" -l msDS-AllowedToDelegateTo