Sharepoint, Kerberos and Internet…

Lately I’ve been doing some work around Sharepoint solutions. Our company ( focuses on business applications built on top of the Microsoft Sharepoint+SQL BI platform delivered to customers through Internet in the cloud computing model. This is still (at least in Poland) a novelty and there are many challenges. Not only technical, but also business – its not easy to find customers for those kind of advanced IT solutions. But since my blog is supposed to be at least a bit technical let me focus on that first part:0

In our team I’m also the ‘infrastructure guy’, which means that I forgot how to write code and now have to do the boring, dirty stuff;) Anyway if you are configuring a platform that uses IIS, Sharepoint, SQL DB, Reporting, Analysis and want to expose that to Internet you will be faced with many challenges – regardless if you are a programmer or an “ITpro/admin”. This Microsoft platform is powerful in its potential but still developing on those aspects like ease of programming or installation/configuration/maintenance/operations.

Anyway, let’s get to the point: you will find lots of useful information on the internet about how to configure Kerberos and Sharepoint. However most of them focus on INTRANET scenarios. Here I want to give some short tips/pointers on the most often “hicups” when exposing a solution built of varying technologies (Sharepoint Excel Services and Reporting Services, SQL Analysis Services/OLAP) over Internet. My whole list of various issues I met along the way is huge, but let me give you the top 3, which caused my biggest headaches (least or not documented anywhere in😉

  1. If you expose Sharepoint web apps to internet you cannot use Kerberos on those sites – why? because you can either use forms based authentication or windows, but NTLM (your user out there in the Internet won’t find your internal DC and even if he would, he would not get through all the firewalls with his Kerberos ticket). But if you will want (or need, as shows point 2) to delegate user credentials to the backend, you will need to configure CONSTRAINED DELEGATION, where first hop is NTLM (or any other auth) and subsequent are KERBEROS. Here is the must read:
  2. If you want to expose OLAP cubes over internet, there is a way with the magical msmdpump.dll. Two things to watch out for:
    1. you need to use this scheme of authentication: basic auth to IIS website with msmdpump.dll, then Kerberos to the backend OLAP (which is point 1)
    2. If you configure the above read, and install the hotfix mentioned there on ALL your farm and backend servers. Otherwise you will notice strange “message altered in transit” errors and OLAP will not work correctly. This applies to both OLAP over internet (msmdpump.dll) as well as OLAP over Excel Services
  3. There is a problem if you use the same EXCEL to connect to OLAP cube over internet (pointing to the msmdpump.dll) and still want to VIEW it in browser by using Excel Services: that will fail because of how excel services security model is currently implemented. Don’t want to go into details on that, but MSFT is aware of this and is “discussing on whether to fix it or not” (the usual;)) The only workround it to dynamically in code substitute the EXTERNAL .odc connecting to msmdpump.dll to an INTERNAL .odc that connects directly to Analysis Services when querying the Excel via Excel Services. Not nice, but otherwise you cannot view the same Excel with PivotTable in ECS and use it on the internet.

To get all this right its important to understand Kerberos, and Internet is where you will find more then enough info on this. For troubleshooting I found these tools useful:

  • Netmon (of course)
  • Fiddler  (‘lightweight http netmon’;))
  • Sharepoint logs
  • IIS failed request logging
  • and of course www,

If you have any questions, or some feedback (other big issues you met in such setups) ping me by mail or using this blog (comments/contact).


~ by alipka on January 22, 2010.

4 Responses to “Sharepoint, Kerberos and Internet…”

  1. “The only workround it to dynamically in code substitute the EXTERNAL .odc connecting to msmdpump.dll to an INTERNAL .odc that connects directly to Analysis Services when querying the Excel via Excel Services.”
    Could you elaborate on this? I don’t understand what code and where could do such a substitution. Do you mean like write a simple .aspx page that figures out if the client is external or internal and returns a different odc based on the results?
    I was trying to get the pump url to work both in excel and excel services with little success. For some reason excel services seems to be connecting to the pump with no credentials instead of windows.
    Also, have you tried crating multiple connections in the workbook? One with the pump and one with the server name? I read somewhere this is possible, that if it failed to connect to one then it would connect to the next. The idea being, if it can’t connect to the internal server name (external excel clients) then it would connect to the pump.
    My current setup has TMG in front doing FBA and kerberos constrained delegation. It all works great. This excel/excel services data connetion issue is one of my remaining issues. Any help would be greatly appreciated.

    • Hi Tony, I have not tried the workaround I had in mind. in the end we only used the EXTERNAL odc. What I had in mind is to build some code into our workflow, because we used a workflow to read the excel through excel services. but i have no idea if that would work. unfortunately combining both internal and external connection seems impossible (or I could not set it up). sorry to be not of much help

      • Andrzej,

        Fortunately I was able to create a workaround. Basically what I did was create two odc files. One called myconn.odc and one called myconn_external.odc2. Myconn.odc contains the internal connection and myconn_external.odc2 contains the data pump url. I then deployed an http handler for sharepoint that handles .odc files. I can now use from the outside because my handler intercepts the request and then returns to myconn_external.odc2. When run through excel services, a web request is not made, the file is retrieved through the database directly, so the substitution is not made and all is well. Sort of confusing but works great! Notice the “.odc2” extension instead of “.odc” on the external odc file. This was to avoid creating an infinite loop in the http handler. Hope that helps someone else.

  2. How did you add the odc2 file extension in SharePoint?

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: