Configuring Kerberos Authentication in the SQL Server Environment

Configuring Kerberos Authentication in the SQL Server Environment (Linked Servers, Reporting Services and Web Applications)

About PASS The PASS community encompasses everyone who uses the Microsoft SQL Server or Business Intelligence Platforms. This includes database administrators, application developers, Business Intelligence professionals, information technology professionals, executives, consultants, educators, instructors, and students. The Professional Association for SQL Server (PASS) is the only independent, user-run, not-for-profit association dedicated to the SQL Server community helping its members Connect, Share, and Learn by: – Facilitating member networking and exchange of information through our websites, Chapters, annual Summits, regional and local events, and virtual communities – Working with Microsoft to influence the evolution of SQL Server products and services – Delivering high quality, up-to-date, technical content, presentations, and other educational material for professional development

Review Kerberos manager for SSRS!!!! – http: //blogs. msdn. com/b/psssql/archive/2013/ 11/22/kerberos-configuration-managerupdated-for-reporting-services. aspx

What does Authentication need to Answer? • • Who are you? Do you belong here? What rights do you have? How can I be sure that you are who you say you are?

What is Kerberos Authentication? Basics • Kerberos is a network authentication protocol – Like the earlier NTLM protocol but additional benefits of efficiency, security and compatibility • Efficiency – Single Sign on • • Authenticate Once, Trusted by System No need to re-authenticate to Everything – Delegation of Authentication • • A Service that runs on one server can impersonate an authenticated client (and pass their credentials) in order to access a resource hosted on a different server. The User does not have to authenticate to each resource/service. Security – Encrypted end-to-end communications – Mutual Authentication (between the client and the server) • • • The identity of BOTH ends of a communication are authenticated Protects against man-in the middle and replay attacks Compatibility – Based on the Kerberos 5. 0 open standard – Other operating systems and devices can use this standard to authenticate to a windows network • How implemented? A Ticketing System with 3 Components and a Shared Secret

Mechanism Ticket that Grants access To the park (TGT) Tickets to access Specific rides/services (ST)

Three Components Key Distribution Center (KDC) -Vouches for users identity Authentication Service -Authenticates us -Provides access to network Ticket Granting Service -Ticket provides access to network resources

Mechanism Authentication Step 1: User sends a logon request to the authentication service (ie logon to the domain). The request is encrypted using the users password hash and includes the local machines date/time. Step 2: The KDC (the domain controller) decrypts the request using the clients password hash and verifies the date and time (within 5 minute offset).

Mechanism Authentication Step 3: If successful the KDC issues the user: A Ticket Granting Ticket (TGT). Which includes the client name, IP, timestamp and validity period. and A Ticket Granting Service (TGS) Session key used for communication between client and the Ticket Granting Service (THE KDC). Is encrypted with user password hash

Mechanism 1. Request access to resource on server. Client sends a copy of the TGT, the name of the application server and the timestamp. The KDC (Ticket Granting Service) issues a Session Ticket to the client 2. The Client presents the Service Ticket to the Application Server 3. If enabled, the App Server may send back the service session ticket (+timestamp) for the client to verify. 4. The Client and App server begin a session

Service Principals • • • When the client makes a request to the Domain Controller for access to a remote server/service it checks the Active directory for a matching Service Principal Name (SPN). Kerberos requires appropriate SPN to identify Resources and User Accounts associated with those resources Stored as an attribute in Active Directory. Is a User or Computer Property. Users have a User Principal name (UPN ) formed from the 2 fields of the logon plus a dollar sign (i. e. SPacheco@SFPCorp. com$) Computers have SPN’s – Net. BIOS (i. e. HOST/SERVERNAME – FQDN (i. e. HOST/SERVERNAME. SFPCorp. com) • SPN’s are registered by Domain Admins or Domain Users that have been granted: – Read service. Principal. Name – Write service. Principal. Name – Either way you’ll need to talk to a domain admin

Service Principals • SPN’s will automatically be registered for a service if running under: – – A Domain Admin account An account with the read/write Service. Principal. Name rights “Local System local account “Network Service” local account • Examples: – SQL Server » MSSQLSvc/SERVERNAME. SFPCorp. com: 1433 – Windows Remote Management » WINRM/SERVERNAME. SFPCorp. com: 1433 – NOTE: After making change must restart the service • SPN’s must manually be registered when a Service is associated with a User Account (a Service Account) – SQL Server Service running under account: SVC_SQLServer. SFPCorp. com (MSSQLSvc/SERVERNAME. SFPCorp. com: 1433/SFPCorpSVC_SQLServer)

Common SQL Service Principal Names to be registered – – SQL Relational DB Service Principal Names • MSSql. Svc/<hostname> • MSSql. Svc/<fqdn> • MSSql. Svc/<hostname>: <port> • MSSql. Svc/<fqdn>: <port> • MSSql. Svc/<hostname>: <port>/<instance> • MSSql. Svc/<fqdn>: <port>/<instance> SQL Browser • MSOLAPDisco. 3/server. Hostname • MSOLAPDisco. 3/server. Host. Name. Fully_Qualified_Domain. Name SQL Analysis Service Principal Name • MSOLAPSvc. 3/<hostname> • MSOLAPSvc. 3/<fqdn> • MSOLAPSvc. 3/<hostname>: instancename • MSOLAPSvc. 3/<fqdn>: instancename SQL Browser • MSOLAPDisco. 3/server. Host. Name. Fully_Qualified_Domain. Name

Service Principals • Utilities to ViewEdit SPN’s – SETSPN • • SETSPN -L <servername> (to view) SETSPN –S (to check for duplicates) Service/FQDN/DomainAccount – Example » setspn –s MSSQLSvc/myhost. redmond. microsoft. com DOMAINSQLService. Account » setspn –s MSSQLSvc/myhost. redmond. microsoft. com: instancename DOMAINSQLService. Account – ADSIEdit. msc MMC • Must be from DC or have Admin Tools installed – Active Directory Users and Computers • (Select User or Computer) Properties Attribute Editor Service Principal Names (to view, NOT recommended to edit) – Microsoft Kerberos Configuration Manager • • • Recommend Check Firewall SETSPN, Check Status and DELEGATION status If local, just click “Connect” Must be a Domain Admin or have the Service. Principal. Name rights to Edit/Add/DELETE a SPN

SQL Error Log

Event log

All this is great but how does it effect me in a SQL Server Environment? Ever see any of these errors?

Kerberos Double Hop • Multi-Tiered enterprise applications will frequently require the user to perform a double hop in order to access a resource. – – • • • Client Web. Server Database Server Client Reporting Services Server Database Server Client SQL Server Linked SQL Server Client Excel Services Sharepoint Server How do you pass your credentials from the middle-tier server to the backend server (with out having to login separately to each resource). Need an account to impersonate the user and pass their credentials to the destination server/service Delegation!

Delegation • Multi-tier Applications require Delegation – Linked Servers and Distributed Queries – Webserver Front End to SQL – Reporting Services Front End to SQL • Delegation is configured by your Domain Admin as a property in Active Directory – You can however view it if have Administrative Tools Installed • • • Active Directory Users and Computers Select User or Computer Properties Delegation tab NOT ENABLED BY DEFAULT The Delegation tab will only be present if a SPN has been registered Note: Example at left is for Computer

Computer Delegation • • Do not trust this user for delegation (Default) Trust this computer for delegation to any service (Kerberos only) – not secure • Trust this computer for delegation to specified services only – Use Kerberos Only – Use any authentication protocol ( not secure) – Click Add, Select Computer or User with the SPN • Delegation should be configured at User (Service Account) level

Delegation • • Do not trust this user for delegation (Default) Trust this user for delegation to any service (Kerberos only) – One Click and Go – not secure

Constrained Delegation • Trust this user for delegation to specified services only (this is Constrained Delegation and is most secure) – Use Kerberos Only (use this) – Use any authentication protocol ( not secure) – Click Add, Select the same user account (Self-Mapping). note, you must have already registered a SPN associating it with the service – Select All to add all associated services – Click Apply

SQL Check Kerberos connections • Is Kereberos connection? – • Which connection is using Kerberos? – • SELECT auth_scheme FROM sys. dm_exec_connections WHERE session_id = @@spid; SELECT session_id, net_transport, client_net_address, auth_scheme from sys. dm_exec_connections Monitor Connections from SSMS SELECT C. client_net_address , S. host_name , S. login_name , ST. text FROM sys. dm_exec_sessions S INNER JOIN sys. dm_exec_connections C ON S. session_id = C. session_id CROSS APPLY sys. dm_exec_sql_text(C. most_recent_sql_handle) ST WHERE S. program_name LIKE 'Microsoft SQL Server Management Studio%' ORDER BY S. program_name , C. client_net_address; http: //searchwindowsserver. techtarget. com /feature/Advanced-Kerberos-topics. Delegation-of-authentication • Note: When you login locally to a SQL server to access resources you are using NTLM

Linked Server Requirements • For the purpose of the example, we select "Be made using Login’s current security context" • Linked server will use the Windows account of the login to connect to the linked server. In order for this option to work, your SQL Server machine will need to be able to impersonate a local account. This option is a simple way to identify that all Windows accounts can use a linked server, without mapping each login • Requires SPN for both SQL servers • Requires delegation to be set up. • Still requires user to have SQL permissions on the linked server

Linked Servers Example • • • • • • On SQL 2012 -1 Configure Linked Server "Be made using Login’s current security context" Logon Denied Anonymous Authentication Error On Both SQL servers Open SSMS, run v: “check connections. sql” script Open SQL Config Manager, Check SQL Service logon Account (Local Service) on both SQL servers On DC, Check AD attributes Service Principal names for both SQL servers Run Set. SPN –L on both SQL Servers On both SQL Servers - Open SQL Config Manager, Edit SQL Service logon Account set to Local system On DC, Check AD attributes Service Principal names for both SQL servers Run Set. SPN –L on both SQL Servers On SQL 2012 -1 open SQL Config manager and change logon account to svc_SQLEngine On DC, Check AD attributes Service Principal names for both SQL servers and svc_SQLEngine On both SQL Servers Run Set. SPN –L On SQL 2012 -1 Install SQL Kerberos Configuration manager On SQL 2012 -1 – in SQL Kerberos Configuration manager Create SPN’s, Check Delegation tab on Kerberos Config Manager On SQL 2012 -1 run SETSPN-L sql 2012 -1 and SETSPN –L sfpcorpsvc_SQLEngine On DC 1 Configure Delegation for ANYSERVICE for svc_SQLEngine On SQL 2012 -1 – in SQL Kerberos Configuration manager Create SPN’s, Check Delegation tab on Kerberos Config Manager On DC 1 Configure Constrained Delegation for svc_SQLEngine (Self mapped to user account) On SQL 2012 -1 – in SQL Kerberos Configuration manager Click refresh and Check Delegation tab on Kerberos Config Manager On SQL 2012 -1 Configure Linked Server "Be made using Login’s current security context“ Test Connection On SQL 2012 -1 SSMS run v: “Distributed. Query. sql” script On Both SQL servers Open SSMS, run v: “check connections. sql” script

Reporting Services Server • • In a small shop, may have Reporting Services running on the same server as the database engine In an enterprise environment, more likely will move reporting services to another server (performance) or set of load-balanced servers (availability) Easiest way to determine if you will need to use Kerberos for your Reporting Services deployment is to review your data sources. If, under “Connect Using: ”, “Windows integrated security” is selected, you will require Kerberos to forward the user’s credentials to the data source. If Windows Integrated Security is selected for your data source, you will need to have the RSWindows. Negotiate setting present within the Authentication. Types section of the rsreportserver. config file for your Reporting Services Instance

Reporting Services Server • The default location (for SQL 2008) for rsreportserver. config is – C: Program FilesMicrosoft SQL ServerMSRS 10. <Instance Name>Reporting ServicesReport. Server for SQL Server 2008 and – C: Program FilesMicrosoft SQL ServerMSRS 10_50. <Instance Name>Reporting ServicesReport. Server for SQL Server 2008 R 2. • The Authentication. Types section of the rsreportserver. config should look similar to the following for Kerberos to work successfully with Reporting Services: <Authentication> <Authentication. Types> <RSWindows. Negotiate/> <RSWindows. NTLM/> </Authentication. Types> <Enable. Auth. Persistence>true</Enable. Auth. Persistence> </Authentication> • Will also see RSWindows. NTLM present as well, but is not required for Kerberos Authentication

Report Server Build and Run a report on SQL 2012 -1 Report Server • Data Source Properties – – – Use Connection embedded in my report Select type Click build, select SQL 2012 -2, Select Windows Authentication, Adventure. Works DB Test Connection Click Credentials • Use current Windows user, Kerberos delegation might be required – Click Ok, Save, then run from Report. Services

Report Server Why?

Report Server • • To Configure Kerberos for Reporting Services Need to create an AD account for Reporting Services – Svc_SQLReport. Service • Edit The Reporting Service Configuration to use the new account – Restart Reporting Services • Need to create a SPN for svc_SQLReport. Service – SETSPN –s http/SQL 2012 -1. SFPCorp. com: 80 svc_SQLReport. Service • • Need to enable Delegation for the svc_SQLReport. Service account User is impersonated by the svc_SQLReport. Service account and passes their credentials to the SQL 2012 -2 server Still Require user permission to underlying database resource Again SPN’s are registered by Domain Admins or Domain Users that have been granted: – Read service. Principal. Name – Write service. Principal. Name – Either way you’ll need to talk to a domain admin

Web Applications • • • Configuration is similar for Web Applications Kerberos is required if connection is set to Windows Integrated Security Need to create an AD account for Reporting Services – svc_Web. Application Edit The Web Application Configuration to use the new account – Restart the Web Application Need to create a SPN for Web. Application – SETSPN –s http/Web. Server. SFPCorp. com: 80 svc_Web. Application – SETSPN –s http/Web. Server. SFPCorp. com SFPCorpMy. Web. App. Pool. ID svc_Web. Application Need to enable Delegation for the svc_Web. Application account User is impersonated by the svc_Web. Application account and passes their credentials to the SQL server NOTE Can be configured for entire Web Server or for one Web Application Still Require user permission to underlying database resource Again, SPN’s are registered by Domain Admins or Domain Users that have been granted: – Read service. Principal. Name – Write service. Principal. Name – Either way you’ll need to talk to a domain admin

Tools • • • SETSPN (command line tool part of win 7, win 8, 2008 R 2 and 2012) • • • Administrative Tools Active Directory Users and Computers KLIST (part of Windows Administrative Tools) Active Directory Explorer - http: //technet. microsoft. com/enus/sysinternals/bb 963907. aspx ADSIEdit SQL Kerberos Configuration Tool – http: //blogs. msdn. com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberosconfiguration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivityissues. aspx – http: //www. microsoft. com/en-us/download/details. aspx? id=39046 • Netmon, Wire. Shark, Ethreal – packet analyzers to help troubleshoot

Terminology • Authentication – – • • Who are you? Do you belong here? What rights do you have? How can I be sure that you are who you say you are? Kerberos Authentication – Ticket based, Default authentication protocol in AD domain NTLM Authentication – challenge-response authentication protocol. Windows defaults to it when: – – Client is authenticating to a sever using an IP address Server is not in domain Server is not in your domain Firewall is blocking port 88 • • • Service Principal Names Security Principals Man-in-the-Middle Attack Replay attack Delegation • Kerberos Key Distribution Center (KDC) – port 88

Links • How to enable Kerberos authentication in SQL 2012 - http: //social. msdn. microsoft. com/Forums/sqlserver/en-US/57 e 96 bf 0 -9 ee 8 -4 d 93 -b 3 e 7 -f 211 f 23 cf 8 f 7/how-toenable-kerberos-authentication-in-sql-2012? forum=sqlsecurity • How to use Kerberos authentication in SQL Server - https: //support. microsoft. com/kb/319723 • Kerberos Authentication Overview - http: //technet. microsoft. com/en-us/library/hh 831553. aspx • Definitions - http: //msdn. microsoft. com/en-us/library/0 aa 17 e 1 f-b 3 c 1 -478 a-9 bf 0 -2 d 826888 d 081#kerberos • Kerberos Survival Guide - http: //social. technet. microsoft. com/wiki/contents/articles/4209. kerberos-survival-guide. aspx • NLTM vs Kerberos Diagrams http: //technet. microsoft. com/en-us/library/bb 742456. aspx • Windows authentication - http: //technet. microsoft. com/en-us/library/gg 189199. aspx • Linked Servers • – http: //www. databasejournal. com/features/mssql/article. php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance. htm – Security for Linked Servers - http: //technet. microsoft. com/en-us/library/ms 175537(v=SQL. 105). aspx Linked Server Errors – • • Delegation – http: //social. msdn. microsoft. com/Forums/sqlserver/en-US/f 855 e 179 -5433 -42 f 5 -bbc 8 -305896 a 144 e 1/sql-2012 -linked-servers-and-delegation – http: //technet. microsoft. com/en-us/video/Video/hh 858469 – Configuring Linked Servers for Delegation - http: //technet. microsoft. com/en-us/library/ms 189580(v=sql. 105). aspx Kerberos and Delegation – • http: //social. msdn. microsoft. com/Forums/sqlserver/en-US/cee 74 a 48 -062 e-4 fa 8 -814 e-2 c 03099 c 2 c 22/linked-server-setup-errorlogin-failed-for-user-ntauthorityanonymous-logon http: //technet. microsoft. com/en-us/video/Video/hh 858469 Set. SPN - http: //support. microsoft. com/kb/929650

Links • • Kerberos SPN's and SQL – Register a SPN for SQL Server Authentication with Kerberos - http: //www. mssqltips. com/sqlservertip/2955/register-a-spn-for-sql-server-authentication-withkerberos/ – SETSPN - http: //social. technet. microsoft. com/wiki/contents/articles/717. service-principal-names-spns-setspn-syntax-setspn-exe. aspx – Configuring Kerberos authentication for a SQL Server 2008 R 2 and Analysis Services clustered named instances - http: //www. guillesql. net/Posts/Configuring_Kerberos_authentication_SQLServer 2008 R 2_Analysis_Services_clustered_named_instances. aspx – Microsoft® Kerberos Configuration Manager for SQL Server - http: //www. microsoft. com/en-us/download/details. aspx? id=39046 OLEDB Providers and Distributed Queries – Configuring OLE DB Providers for Distributed Queries - http: //technet. microsoft. com/en-us/library/ms 190918(v=sql. 105). aspx • Recommendations for configuring Kerberos with Reporting Services - http: //support. microsoft. com/kb/2145506/en-us • Configuring Kerberos authentication for a SQL Server 2008 R 2 and Analysis Services clustered named instances http: //www. guillesql. net/Posts/Configuring_Kerberos_authentication_SQLServer 2008 R 2_Analysis_Services_clustered_named_instances. aspx • Configuring Kerberos for Microsoft Share. Point 2010 BI in 7 Steps - http: //www. bing. com/videos/search? q=kerberos&FORM=HDRSC 3&adlt=strict#view=detail&mid=9 ACB 11 B 1 D 21 CEA 5 D 2260 • Register a Service Principal Name (SPN) for a Report Server - http: //technet. microsoft. com/en-us/library/cc 281382. aspx • Understanding Kerberos Double Hop - http: //blogs. technet. com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop. aspx • Kerberos Survival Guide - http: //social. technet. microsoft. com/wiki/contents/articles/4209. kerberos-survival-guide. aspx • Adding Write Service. Principal. Name permissions to AD group - http: //social. msdn. microsoft. com/Forums/sqlserver/en-us/1 ed 937 c 8 -d 704 -4 e 9 b-b 07599 b 48 bc 4 ee 82/adding-write-serviceprincipalname-permissions-to-ad-group? forum=sqlsecurity • How to Use Set. SPN to Set Active Directory Service Principal Names -http: //www. petri. co. il/how-to-use-setspn-to-set-active-directory-service-principal-names 2. htm • Microsoft Virtual Academy: Breakthrough Insights using SQL Server 2012 : Analysis Services and Credible, Consistent data (Module 2) - Configuring and Securing Complex BI Applications in a Share. Point 2010 Environment with Microsoft SQL Server 2012 - http: //technet. microsoft. com/en-us/video/microsoftvirtual-academy-configuring-and-securing-complex-bi-applications-in-a-sharepoint-2010 -environment. aspx

Thank You from PASS www. sqlpass. org