Impersonating EWA users in your UDFs

Excel Services UDFs are generally run under the credentials of the service account used by the Office Server installation.  This may not be exactly what you want if you would like your UDFs to return data that is specific to the user that opened the workbook.  This is also not what you would want if you want the UDF code to perform other functions (e.g. read/write to SQL, access system resources, etc...) under the credentials of the user that opened the workbook.

In a more thorough example, you may want to have an UDF that will read certain information from a SQL database.  It may be the case that depending on the user's credentials the information returned by SQL is different, and we want that reflected in our UDF so the data that is returned to Excel is correct.

The function below will do everything you need.  All you have to do is call this function in your UDF (which needs to be decorated with the ReturnsPersonalInformation=true UdfMethod attribute) before you do anything that needs that user's credentials:

 NOTE: Shahar pointed out a bug that I left out here, once you are done doing what you'd like to do as the impersonated EWR user you will want to release the impersonation from the thread, so that it goes back to the default account, that way you don't have an Excel Server thread running with the user's credentials when it's not needed. I have changed the code to fix this problem, here's the new code and how to use it.

// Causes the current UDF to impersonate the user that loaded it through EWR

private WindowsImpersonationContext impersonateUser()

{

WindowsIdentity wi = null;

try

{

// Get the user that loaded Workbook & impersonate

// NOTE: To re-use this for a Client UDF first

// check that your process name is w3wp since

// impersonation is not needed on the Client.

wi = (WindowsIdentity)

System.Threading.Thread.CurrentPrincipal.Identity;

}

catch (System.Exception ex)

{

      throw new InvalidOperationException(

                            "Impersonation failed.")

}

return wi.Impersonate();

 }

USAGE:  

To use this code such that you only impersonate the user during the needed block, here's what you do:

// uses impersonation

public void useImpersonation()

{

     using (WindowsImpersonationContext wiContext = impersonateUser())

     {

          // Inside this block you are impersonating the user

     }

     // No longer impersonating user

}