Guest Post: Part 3–Highly Available BI: Me , Myself and I
About Chris
Chris Testa-O'Neill is a Senior Consultant for Coeo Ltd, a leading provider of SQL Server Managed Support and Consulting in the UK and Europe. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, sole author of the MCTS SQL Server 2008 Microsoft E-Learning courses and technical reviewer for SQL Server 2012 BI Official Microsoft courses for Microsoft Learning . He is heavily involved with the SQL Server community as a speaker and an organiser of SQLBits , a Regional Mentor for SQLPASS and he runs his own user group in Manchester, UK. As well as being certified as a SQL Server MCDBA, MCTS and MCITP in all tracks. Chris is also a Microsoft Certified Trainer , and a Microsoft Certified Systems Engineer in Windows 2000 and Windows 2003. In his spare time Chris loves playing in a band as a guitarist/lead vocalist. You can contact Chris at chris@coeo.com or on twitter as @ ctesta_oneill .
Me , Myself and I - Authentication, Delegation and Impersonation in a Highly Available BI environment
Part III of a highly available business intelligence environment deals with the important consideration of configuring authentication mechanisms within the infrastructure.
Detailed information can be found in the Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products white paper. This article deals with the key concepts and considerations that need to take place when planning authentication, delegation and impersonation.
Authentication.
Authentication is the process of verifying the identity of a user on a network.
Three forms of authentication can be configured when building a SharePoint farm which supports a SQL Server 2012 highly available BI environment. Kerberos, NTLM and Claims Based authentication. PowerPivot for SharePoint at the moment does not support Claims Base Authentication. For brevity, this authentication method in not explored.
The differences between Kerberos and NTLM are analogous to a scenario such as visiting a theme park like Blackpool Pleasure beach or Disneyworld.
NTLM (NT Lan Manager)
When I was a young lad, my parents would take me for a day trip to Blackpool Pleasure Beach. In those days, you were free to walk around the theme park. If you wanted to go on a ride, you were challenged. Meet the height requirement for the ride, pay your cash and you were allowed to enjoy what the ride had to offer. Once completed, you could then go to another ride where once again you were given the same challenge. Meet the height requirement and pay your cash. If you could not meet this challenge you were unable to get on the ride.
This is similar with NTLM authentication, except the challenge is to provide a correct user name and password to gain access to a network resource. The process of repeating authentication challenges can be cumbersome to users. This is where Kerberos can help.
Kerberos
Kerberos is an authentication protocol that is designed to provide a single sign-on environment to reduce the repetitive nature of NTLM authentication.
Today, Blackpool Pleasure Beach is enclosed within a security fence. As a result, when you go to a theme park such as Blackpool Pleasure Beach or Disneyworld, the authentication takes place at the entrance of the theme park, or the ticket booth. It is here where you are challenged. Pay your cash and the ticket booth operator will provide you with a ticket (or wristband) that is valid for the day. Adults will receive a different ticket to children.
The result of receiving the ticket means that should you wish to go on a ride, then all you have to do is show your ticket at the entrance of the ride. The ride is programmed to accept the correct tickets and reject tickets that are not valid.
Kerberos works in a similar way. You authenticate against a Domain Controller (ticket booth) in a domain (Blackpool Pleasure Beach/Disneyworld), which will issue you a session ticket (ticket) should you meet the challenge (username and password). Once you have this ticket, you will present it to network resources such as SQL Server or a File Server (a ride). If you are in the list on the resource then you will get access.
*Note I am ignoring resource permissions here as that is a separate topic known as authorisation.
Kerberos provides convenience in that you only have to authenticate once, the session ticket that you are presented with contains information that will be presented to the resource when you access it and is valid for 8 hours. And Kerberos provides additional capabilities that are very important when setting up a highly available BI environment:
Delegation
Kerberos delegation is the process of giving an Active Directory account permissions to perform a task. An example could be the ability to impersonate another user account.
Impersonation
Kerberos impersonation is the process of one account impersonating the credential of another account. Delegation of this permission must be done first for impersonation to work.
So back at Blackpool Pleasure Beech, my entire family have got their tickets and are enjoying the rides. When it comes towards lunch time we need to get cash out of the cash machine to pay for lunch. What normally happens is that while I take my kids on another ride, I will give my wife my cash card. This very act is delegation, as I am giving my wife permission to be me when she goes to the cash machine on my behalf. When she gets to the cash machine and types in the four digit PIN code to access my account. The very act of her typing in the PIN code is impersonation as she is impersonating my credentials
So how does this apply to a highly available BI environment?
Let’s remind ourselves of the environment we discussed in the second part of this series of blog posts
This software and hardware is collectively used together to create the following environment.
Should a user want to access a PowerPivot, Report Builder or PowerView report that uses a back end source databases shown at the bottom of the diagram. Kerberos, delegation and impersonation will be required if you must retain the identity of the user who originally made the request for the report. The steps of maintaining the users credentials over two or more connections is referred to as a double hop and there becomes a requirement to delegate the rights to authenticate as a given users identity. Kerberos works with this scenario as a user authenticates using their password only once when they log onto the domain. After that it is the session ticket that is used to authenticate. As a result we have the ability to delegate control of a user’s or even workstations session tickets.
Therefore, should a user connect to an application such as PowerPivot, Report Builder or PowerView that accesses data in a backend database, by default a connection will be made using the service account of the application. If there is a need to audit access against the backend database. It will audit the fact that the service account accessed the backend and not the user who made the request for the report.
In order to retain the identity of the user who originally made the request for the report, we have to perform a number of tasks:
- We first must represent SQL Server 2012 Business Intelligence applications as objects within Active Directory
- We must use delegation to an account to be able to impersonate a user against a back end server.
Exposing Applications as Active Directory objects
When a computer is joined to an Active Directory domain, this process will create an object within Active Directory automatically. When a person joins a company, an account will be manually created in Active Directory to provide a logical representation of the individual as a user object. The purpose of doing this is so that these objects can be secured within the organisation’s domain.
Like user accounts, applications are not necessarily registered within Active Directory automatically. As a result, we must manually register an application within Active Directory. This is done using the setspn command line tool. SPN is a service principal name and is the name by which a Kerberos client uniquely identifies an instance of an application for a given target computer. There are many ways that SetSPN can be used. However, in the context of exposing SQL Server 2012 BI applications in Active Directory, the following syntax is used:
Setspn –S <service class>/<host>:<port> <service account name>
<service class> denotes the name of the service or application. If it is SQL Server then the <service class> is MSSQLSvc. Analysis Services is MSOLAPSvc.3 and Reporting Services would be HTTP
<host> is the fully qualified domain name or netbios name an which the application is running. The recommended practice is that each application should have two entries: one for fully qualified domain name and one for NetBIOS.
<port> is optional and is used to define the port on which the service is running. This should be used when multiple instances of an application is running
<service account name> is the service account that is defined for the application
As a result, If there is a default SQL Server instance running on a computer named CoeoSQL.Coeo.Local under the service account Coeo\SQLService, two spn’s would be registered as follows
For the fully qualified domain name it would be:
Setspn –S MSSQLSvc/CoeoSQL.Coeo.Local:1433 Coeo\SQLService
For the NetBIOS name it would be:
Setspn –S MSSQLSvc/CoeoSQL:1433 Coeo\SQLService
If it was a default Reporting Services instance on the same computer using the service account Coeo\RSService it would be:
Setspn –S HTTP/CoeoSQL.Coeo.Local: Coeo\RSService
Setspn –S HTTP/CoeoSQL:1433 Coeo\RSService
These will register service principal names within Active Directory that can then be delegated. As a result, start to draft up a list of applications, fully qualified domain name and their associated service accounts for any applications that will be subjected to the double hope issue
Using Delegation to setup impersonation
The act of creating SPNs will enable a new tab in the user account properties in Active Directory named Delegation. It is here that you will be able to setup delegation so that credentials can be forwarded from one service to another. However, make sure in the user account properties in the Account tab that the option “Account is sensitive and cannot be delegated” is not selected as this means delegation cannot be setup. Within the Delegation tab, the check box must be selected for “Trust this user for delegation to the specified service only”. This is known as constrained delegation. Once selected, choose Kerberos and click on ADD to add the service to which the account can be delegated to as shown in the following graphic.
Setting up SPNS and delegations will need to be done for all applications that will subject to the double hop issue that was outlined earlier.
So now we have an understanding of the key concepts of authentication, delegation and impersonation; and have an understanding on the impact of these concepts on a SQL Server 2012 Highly Available environment. The key considerations when dealing with this setup is provided by the best advice I received from Adam Saxton (Twitter | Blog), Senior Escalation Engineer based at the Microsoft CSS.. He states that when undertaking this activity, you should come up with a checklist:
- Know your environment
- What are the service accounts for the applications
- What SPNs does the application need
- What type of SPNs should you define
- What delegation is required
- Be mindful of Active Directory and Local policy settings (eg Account is sensitive and cannot be delegated)
- Consider any application specific settings
Once you have answered these questions you will be able to set up the right SPNs and Delegation to ensure that a user’s credential is passed through to back end data sources.
Disclaimer: You should not hand over your cash card as it will compromise your security. And no children or partners were harmed in the making of this blog J
Some additional useful resources: