Transcript Document
KERBEROS, SQL AND YOU Adam W. Saxton Microsoft - SQL Support @awsaxton [email protected] WHY YOU ARE HERE… Not to learn about the mythical dog You like when your head hurts 2 WHY DO WE NEED KERBEROS? Allows for authentication in a secure manner Both client and server prove their identity Prevention against Eavesdropping and Replay attacks Provides ability to forward (delegate) credentials 3 CHALLENGES OF KERBEROS Complicated to configure • • • • Domain Admin rights Service Principal Names (SPN) Delegation settings Understanding what Applications support Typically an anonymous error I.E. 401 or Login failed Hard to diagnose • Errors rarely point to Kerberos Time is everything • Clocks on servers need to be synchronized • 5 minute window to use the session request – Replay protection 4 ELEMENTS OF KERBEROS CONFIG Kerberos Key Distribution Center (KDC) Your Domain Controller • Active Directory Service • Account Database • Global Catalog for directing referrals Service Principal Name (SPN) • Service Identifier Accounts for 8090% of CSS Kerb issues Delegation • Forwarding of Credentials • Defined on Account in Active Directory Used for multiple service hops 5 FORWARDING USER CREDENTIALS UserA First Hop Client Second Hop Middle Server Backend Server The Classic Double Hop 6 HOW BAD CAN IT GET? Load Balanced SP Servers http://virtualsharepoint Web Service Data Source Login failed for user ‘null’ Load Balanced RS Servers http://virtualreports SharePoint Content DB RS Catalog RS Data Source Linked Server 7 THE INFAMOUS SPN… Uniquely identifies an instance of a service • Used to request a service ticket • You can only have one! Some Tools don’t prevent duplicate entries Bound to one Active Directory Object • User or Machine account • Attribute of that object servicePrincipalName 8 SPN BROKEN DOWN… <service class>/<host>:<port> Service Class Host Port • Arbitrary name created by Service • Represents a suite of service • Examples: HTTP, MSSQLSvc, MSOLAPSvc.3 • HOST is a special Service Class NetBIOS names may not be unique in a Forest • The name of the computer on which the service is running • Could be a Virtual name (i.e. Load Balanced or Cluster) • This can be a fully qualified domain name or NetBIOS name • Optional item • Used to differentiate between multiple instances 9 HTTP SERVICE CLASS HTTP works for both http and https No Port by default FQDN HTTP/passrs.pass.local NetBIOS HTTP/passrs Ports make it unique HTTP/passrs:80 10 SQL SERVICE CLASS SQL creates FQDN not NetBIOS Default Instance TCP MSSQLSvc/passsql.pass.local:1433 MSSQLSvc/passsql.pass.local:56772 Named Pipes Started with SQL 2008 Named Instance Default Instance MSSQLSvc/passsql.pass.local MSSQLSvc/passsql.pass.local:myinstance Blog: What SPN do I use and how does it get there? Named Instance 11 BUILDING OUT THE SQL SPN… Are we in a Domain? Always Uses Ports! Yes Are we a Cluster? Virtual FQDN No Machine FQDN MSSQLSvc/myserver.mydomain.com :INSTANCENAME :5555 :1433 TCP Protocol ListenAll ? Named Pipes Protocol Default Instance? No IPx (1-N) IPALL Enabled? Both can be set! Yes Dynamic and/or Static Port Yes No This is what the System Center Advisor SPN rule does! 12 WHY DO I NEED BOTH SPNS? SQL Client will resolve IP to Name. IE and IIS will not! MSSQLSvc/passsql.pass.local:1433 MSSQLSvc/passsql:1433 If DNS Name Resolution fails, we fall back to NetBIOS NetBIOS SPN is needed if NetBIOS was used If NetBIOS name resolution failed, you have other issues 13 SPN TOOLS SetSPN ADSIEdit Klist & KerbTray 14 SPN ISSUES WE SEE… Missing SPN • It was never created Misplaced SPN • SPN exists, but is tied to the wrong Account Duplicate SPN • SPN exists on more than one account 15 DELEGATION Forwarding Credential from one service to another Must know all accounts involved User Account must be allowed to be delegated • “Account is sensitive and cannot be delegated” Service Accounts must be allowed to delegate SharePoint 2010 Claims • Unconstrained – Delegate to any Service Auth may • Constrained – Delegate to specified Services onlyforce Constrained 16 DELEGATION TOOLS AD Users & Computers 17 WHAT ACTUALLY HAPPENS? KDC Steps 1 & 4: InitializeSecurityContext Steps 3 & 6: AcceptSecurityContext 1. HTTP/passrs.pass.local 4. MSSQLSvc/passsql.pass.local 3. Accepted 2. Session Key Sent Contains Service Ticket Client 6. Accepted 5. Session Key Sent Contains Service Ticket RS SQL 18 A PEAK INSIDE SQL… Server=passsql\demo;database=AdventureWorks2008;Integrated Security=SSPI; passsql demo MSSQLSvc/passsql.pass.local:59256 Client Provider builds SPN off of Connection String based on Protocol We always try to resolve the FQDN for the server entry We get the port for the Named Instance by way of SQL Browser SQL will validate the SPN and credentials. SPN must match the Server Properties 19 IS NEGOTIATE THE SAME AS KERBEROS? No! It is a decision point to determine NTLM or Kerberos Once decision is made to go Kerberos, no turning back If the SPN is wrong, Kerb Failure on Server Negotiate goes Kerb because SQL1 SPN is present We always try Kerb Single Hop Example: SSMS to SQL MSSQLSvc\SQL1:1433 KDC Misplaced Ticket for SQL2 Account SPN Client Cannot Generate SSPI Context Server: SQL1 20 A DISTRIBUTED CONFIG EXAMPLE… SharePoint WFE – Win2k8 R2 PASS\asaxton Account is Sensitive is unchecked SQL Server – Win2k8 R2 PASS\sqlservice • MSSQLSvc/passsql..local:59256 PASS\spservice • Member of IIS_IUSRS • HTTP/passsp.pass.local • Trusted for Delegation • Kernel Mode Auth Disabled • SP Site is set for Kerberos • RS set to Windows Auth Report Server – Win2k8 R2 PASS\rsservice • Member of IIS_IUSRS • HTTP/passrs.pass.local • Trusted for Delegation • RSWindowsNegotiate 21 KERBEROS EVENT LOGGING May not see error if item was cached This was actually a Duplicate SPN http://support.microsoft.com/kb/262177 22 NETWORK MONITOR Interested in Kerb Traffic Only HTTP SPN Request May not see error if item was cached Failed SQL SPN Request 23 [email protected] @awsaxton http://blogs.msdn.com/psssql 24 © 2012 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.