From Hellhound to Best of Breed – A Guide To Setting up Kerberos Delegation with SharePoint BI Stack – Part II

If you’ve followed the instructions from post 1 then you have setup Kerberos delegation for Excel Services in SharePoint 2013.  Here is how to check if it’s working as it should:

Checking the configuration

The easiest way to see if the setup is successful is to try to create an Excel document which accesses a database on the server you added the SPN and delegation for.  Save the document to a document library on a site using Excel Services and test the Data Refresh by clicking on Refresh Selected Connection. Ignore the warning stating that the connection might not be secured – it may be turned off in Excel Services Application configuration.

If Kerberos has been setup correctly you will have succeeded at refreshing data from the source.

You can verify it on you data source by running this query on the data source (provided it’s a SQL Server):

Select 
    s.session_id,
    s.login_name,
    s.host_name,
    c.auth_scheme
from sys.dm_exec_connections c
inner join sys.databases d on database_id = d.database_id
inner join sys.dm_exec_sessions s on c.session_id = s.session_id
where d.database_id = 5 –- Replace by the database id of your content database

It should return you the session list and you should be able to see Kerberos in the auth_scheme column

image

If not you’d probably get an error and it’s time for some error finding….

“We were unable to refresh one or more data connections in this workbook”

image

Means that the user logged onto SharePoint doesn’t have permissions on the data source (rejoy because the Kerberos part went well).

If against all hope it went wrong you will find a list of some of the most common errors I’ve met through my SharePoint career.

Debugging Kerberos Configuration on SharePoint 2013

There are a lot of blogs posts and websites dedicated to troubleshooting Kerberos configuration.

If you’ve followed the indications from my previous post you shouldn’t be reading this. If you are chances that something went wrong are quite important. For being able to troubleshoot the setup we need some tools (I will add new tools as I discover them) :

Kerberos Troubleshooting Tool List:

  • Windows Event viewer
  • SetSPN
  • ADSI Edit

Enabling Kerberos Event Logging on a specific computer

Actually, the first thing you want to look at is if there are any event log entries. But unfortunately this is not logged by default or it might be disabled. So please have a look at the following key in regedit:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsaKerberosParameters and the Registry value is LogLevel.

If LogLevel doesn’t exist add it and set the DWord value to 1

image

This way you are now logging Kerberos event into the event log. In the Sytem log, source name is Security-Kerberos. Below is an error example

image

Kerberos errors

______________________________________________________________________

KDC_ERR_PREAUTH_REQUIRED

Actutally this is not an error but the default behaviour. Actually the Key Distribution Center requires all accounts to use pre-authentication. Pre-authentication can be disabled on the user account.

So if the checkbox “Do Not require Kerberos Preauthentication” on the user account was checked you will never see this event entry.

image

______________________________________________________________________

KDC_ERR_S_PRINCIPAL_UNKNOWN

This error occurs where no SPN has been set for a specific service – You need to read post 1 again

______________________________________________________________________________

KDC_ERR_BADOPTION

This error occurs when your “source” server is not allowed to Trust this computer for delegation to any service.

image

Check your source server (SharePoint) in AD.

If the userAccountControl = 0x1000 it is not trusted for delegation and here lies the error

The right value is 0x81000 (int 528384). you can either change it manually or use the UI

image

______________________________________________________________________________

I will add more error descriptions as soon as I meet some new ones and instructions on how to resolve them.

In the next post I will introduce a tool to automatize the process of creating SPNs and adding Kerberos delegation so you’ll never have to gather and script it again and then we will be able to move on configuring the rest of the BI stack for SharePoint 2013.

Happy SharePointing

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: