Kerberos for Microsoft Businesss Intelligence (BI) Services in easy steps

  • Article

Kerberos is here to stay!!

SQL Server 2012 & 2014 does not support Claims or SAML tokens, therefore Kerberos for SQL Server BI services are here to stay for a while. You can follow the below steps in order to set up and configure Kerberos for Windows Authentication for the various BI Services that we have in the MSBI stack.

Let us first understand, why do we need Kerberos?

When you are dealing with multiple servers in an environment, you might run into the famous "Double Hop" issue.. For example, lets assume that there are 3 servers (Server 1, 2 & 3). Let's say an application tries to authenticate, starts at Server 1, authenticates on Server 2 and everything goes well. However, if Server 2 wants to further authenticate to Server 3, it will most probably give the error message "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON". This is the classic "double hop" issue. This happens because you can go a single hop with impersonation or NTLM, but not beyond that. Credentials are simply not passed on further.

So, this brings us to the topic of 'Delegation v/s Impersonation'. Impersonation does not give you all rights and it does not go more than one hop.

In the case of delegation, Server 1 delegates to Server 2, Server 2 may either choose to impersonate or delegate to Server 3. So, now that we know when and where we need Kerberos, let's focus on the BI Services. Typically, we have the following applications, which would leverage Kerberos. For example,

  • Performance Point Services
  • Excel Services
  • Reporting Services (If you are not using stored credentials)
  • SQL Server Analysis Services (Only supports Windows Authentication)

Now, when you set Service Principal Names (SPN) to enable delegation, you need to set it on the service and the machines delegating the tickets. For the SQL Server, there are well known service names like MSOLAPDisco, MSOLAP, MSSQL.

Here are the following steps that you need to follow to successfully enable Kerberos.

  • Enable Kerberos on your SharePoint Web Application.ž

  • žEnable the 'Claims to Windows Token' (C2WTS) Service in SharePoint.

 

**Note: C2WTS needs to run on all the App servers.

SharePoint internally use Claims. There is a service that runs on the App Server called 'C2WTS' which does the protocol transitioning. It runs under the local system account. Therefore, it also needs to delegate.

Go to the AD container "Computers" and then delegate to the Database Engine.

  • žCreate an HTTP SPN for the account that is running Web app's application Pool

For example:

<For WebApp>

SetSPN -S http://sp2013 Domain\ServiceAccount

<For SQL Server>

SetSPN -S MSSQLSvc/DB:1433 Domain\ServiceAccount

**Note: Dont use -A (it arbitrarily creates an SPN, even if its a duplicate)

    -S (checks for Duplicates and then creates them if it does not exist)

  • žCreate a dummy SPN for the account that is running the service application
  • žCreate an MSOLAPSvc.3 SPN for the service account running Analysis Services
  • žConfigure Constrained Delegation for the Service Application account to Analysis Services
  • žConfigure Constrained Delegation for the Application Server machine

Once done, reboot the computer, since al of this is cached with a 15 minute expiration.

Hopefully, this should help you to solve a lot of your BI issues with Authentication.