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
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”
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
- 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
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
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.
This error occurs where no SPN has been set for a specific service – You need to read post 1 again
This error occurs when your “source” server is not allowed to Trust this computer for delegation to any service.
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
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.