Twice over the past couple of weeks I’ve been asked by two separate people, what I knew about Kerberos delegation? Truthfully I replied “Not a lot. That’s usually been put in place already by whomever configured the server infrastructure and user accounts”. Luckily for me, this time it was the case, but it piqued my interested enough for me to spend some gaining a better understanding of an authentication protocol that I have only ever really taken for granted.
Even though there is a vast amount about this topic already on the net I still struggled collate it into something small and digestible. I hope this short(ish) blog will help someone else out who wants to just be able to “pick it up” and get things working… hopefully!
Who needs Kerberos?
I won’t get too much into what Kerberos is etc. you can easily go and check that out on the Wikipedia, but what does it allow us to do, and why do we sometimes need it?
While often considered to be more secure, and place less load on domain controllers etc., the main reason why it’s necessary to use this authentication is when credentials of an already authenticated user needs to get passed around from one machine to another. In the BI world this is typically something as simple as apply row level security to data that is being surfaced via an intermediary. This could be Reporting Services, or a website such as SharePoint perhaps.
In this simple diagram, the “Client” is logged into the “Presentation” server, perhaps even over the internet. But then the “Presentation” server is surfacing some data to that user. The trick comes when the data being surfaced needs to be restricted by the original user’s credentials.
The key things to consider are that the:
- “Presentation Layer” contains an account that is allowed to delegate
- “Data Sources” contain definitions of services that can be delegated to via a defined Service Principal Name
To set this all up is actually not the horrible undertaking that many people think it to be. We can simply step through the following tasks.
- Identify data sources – Create SPNs for each one.
- Enable delegation from the “Presentation” server service account(s)
- Assign which SPNs the “Presentation” service account(s) can delegate to.
First off, the following points rely on a couple of assumptions with regards to the infrastructure in use.
I’ve assumed a Microsoft environment, with MS SQL Server as a back end. This is what I personally have utilised in the past, but Kerberos delegation is by no means limited to this. The information below should stand you in good stead to working out the modifications required for non-SQL data sources.
Secondly, the domain and server versions are assumed to be modern-ish, in that they are at least 2008 edition plus. Although all the principles are the same for earlier versions of both the domain and SQL some additional work is required which won’t be covered, but will be pointed out so Google is your friend there.
Create Service Principal Names
Ok, so the first step is to create Service Principal Names (SPNs) for each service that will need to be delegated to. This is in essence a directory of services that will ultimately be assigned to services running on the presentation server so that credentials can be ‘handed-off’ successfully.
An SPN is created with a PowerShell command constructed as follows:
setspn.exe -S <SPN> <AccountName>
This is simple enough, but when you start getting into how to actually construct these arguments it can get tricky. More information about the setspn.exe here.
- SPN = <service class>/<NetBIOS | FQDN>[:<port | instance>]
- AccountName = AD\ServiceAccount
Ok, perhaps the AccountName portion isn’t so tricky. It is after all just the domain account that runs the services that you are creating the SPN for. It is best practice in MS SQL land to run these services as domain accounts, but not a requirements. In that scenario, just use the host name instead of the user account.
But what about service class? As a quick starter for 10, if a SQL Server data source was running as a default instance on a machine named SQL-SERVER-01 under the user credentials of DOMAIN\SQL01-Svc the syntax would look like this:
setspn.exe -S MSSQLSvc/SQL-SERVER-01 DOMAIN\SQL01-Svc
The service class for this is before the first slash (MSSQLSvc), and a typical list of them for a Microsoft SQL implementation environment might look like this:
||SQL Server DB Engine
||SQL Server Analysis Services
||Also for SSL apparently (untested)
There are some other gotchas here too, but instead of reeling them off in a paragraph that is a bit tricky to understand I’ll just throw out some examples all on machine name SQL-SERVER-01 with the service running under the credentials of DOMAIN\SQL01-Svc
-- Default SQL Server Instance
setspn.exe -S MSSQLSvc/SQL-SERVER-01 DOMAIN\SQL01-Svc
setspn.exe -S MSSQLSvc/SQL-SERVER-01.Domain.Full.Path DOMAIN\SQL01-Svc
-- SQL Server Named Instance – just specify the port
setspn.exe -S MSSQLSvc/SQL-SERVER-01:123456 DOMAIN\SQL01-Svc
setspn.exe -S MSSQLSvc/SQL-SERVER-01.Domain.Full.Path:123456 DOMAIN\SQL01-Svc
-- SSAS Default Instance
setspn.exe -S MSOLAPSvc.3/SQL-SERVER-01 DOMAIN\SQL01-Svc
setspn.exe -S MSOLAPSvc.3/SQL-SERVER-01.Domain.Full.Path DOMAIN\SQL01-Svc
-- SSAS Named Instance – add the name of the instance, i.e. “Tabular”
setspn.exe -S MSOLAPSvc.3/SQL-SERVER-01:Tabular DOMAIN\SQL01-Svc
setspn.exe -S MSOLAPSvc.3/SQL-SERVER-01.Domain.Full.Path:Tabular DOMAIN\SQL01-Svc
Note: Here I have added a NetBIOS and FQDN version of each command. It’s probably a good idea to run both commands as some applications might form one type of SPN, and others another. Although in my investigations it is most often the FQDN version.
You can also, if you have it all set up, use DNS records to identify servers, but make sure these are “A” records as CNAMEs just won’t work. This is part of the Kerberos specification.
Once the SPN records have been created within Active Directory, the service accounts that will delegate from need to be enabled for this purpose. Luckily this is quite straight forward, and is actually just a case of creating an empty SPN record within AD for each of these services.
For example, if there was a service that ran SharePoint Excel services on the presentation server named DOMAIN\Excel-Svc, a sample SPN might look something like this:
setspn.exe -S DUMMY/Excel-Svc DOMAIN\Excel-Svc
The “DUMMY/Excel-Svc” portion can actually be anything you like, but this seems sensible to me.
The screen shot below highlights the single reason that this step is required, in order to ensure that the following tab is visible in the AD properties of that user account.
Adding SPNs to Service Accounts
Once the delegation tab is enabled on each of the service accounts that will need to be delegated from, it’s time to tell them which services that they can delegate to.
On the delegation tab, choose the “Add…” button
Then enter the name of the service account that you wish to delegate to, and search the AD for this
The resulting dialog (not shown) will list all defined SPNs attached to that user account, and allow you to choose the ones that you want to be able to delegate to. Here I have selected the default instances of SQL Server and Analysis Services.
Choose OK, and then we are all done!!
Note: In earlier than 2008 Active Directory integration levels this searching mechanism may not be available. I’d hope people aren’t still using integration levels that early, but if they are… get to the googling!
Wrap Up & Verification
The only thing really left to do is to verify that everything works! The best method that I can see of verification is to fire up SQL profiler and using just default settings get it to look at incoming requests for the target data sources when they are being accessed from the presentation later.
All being well, you should be able to clearly see the username of whomever you are logged in as from a client machine attempting to access the data source(s). From there on it is just standard security access permissions to contend with.
That’s all the setup done, I strongly encourage the planning on SPNs and service names in detail long before you actually start getting to the configuration as it will save you a lot of potential pain. There is a very good document from Microsoft entitled “Microsoft BI Authentication and Identity Delegation” that is strongly recommended bedtime reading.
Kerberos delegation is actually quite straight forward and will only take perhaps 30 to 40 minutes to implement and verify provided the planning is correctly in place, much longer to troubleshoot.
I hope this blog helps someone, and feel free to ask some questions in the comments, and I’ll do my best to help out.