IIS Pass-through Authentication for SQL and OLAP using Visual Studio 2017

How to setup an IIS web site to access Microsoft SQL and Analysis Services (SSAS aka OLAP) and run in the security context of the currently logged-in user.

Why would you want this? Well - imagine having a JSON-returning API that retrieves user-specific OLAP data.

This link was tremendously helpful:
http://richardlees.blogspot.com/2010/10/ssas-dynamic-security.html

(I'll be soon have a more detailed post on the intermediate "factless" measure table needed and just how this works)


In IIS: Right-click to create a new Virtual Directory:

Be sure "Connect As" is set to "Application User (pass-though authentication)

 

Right-Click; convert to Application, setting application pool to ".Net v4.5 Classic"

 

 

See also this MS bb515251 that compares Classic vs Integrated mode:

Moving an ASP.NET Application from IIS 6.0 to IIS 7.0

 

As well as this:

http://stackoverflow.com/questions/716049/what-is-the-difference-between-classic-and-integrated-pipeline-mode-in-iis7

 

 

Be sure to set proper authentication to IIS. Both ASP.Net Impersonation and Windows Authentication need to be set (you likely need to have these settings in respective application web.confg as well):

 

 

Code runs at the server in the security context of the currently logged in user! This means that (typically domain) account needs to have file-level access to the web application and all resources touched. Code is NOT running under the safety of a restricted service account! 

 

Needless to say, the code should be written "safely" - particularly if someone with administrative permissions log in; say, a sysadmin logs in and does some pen-testing! Pay particular attention to any user-input features (google things like SQL injection and cross-site scripting for more information).

 

Unlike Visual Studio 2015 (at least as of March, 2017)... Visual Studio 2017 does not seem to "see" a NuGet package for the ADOMD client to access SSAS. It is a manual msi download & install.

 

Be sure ADOMD client is installed by downloading from:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

 

or by viewing "Redistributing ADOMD.NET":

https://msdn.microsoft.com/en-us/library/ms123470.aspx

 

 

 

Once installed, include a VS2017 project reference by browsing to
 <system drive>:\ProgramFiles\Microsoft.NET\ADOMD.NET\version number

 

and selecting the file to reference:

 

Here's a link on ADOMD.NET Client Programming:
https://msdn.microsoft.com/en-us/library/ms123477.aspx

ADOMD Connection Strings: https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdconnection.connectionstring.aspx

Connection String Properties (Analysis Services):
https://msdn.microsoft.com/en-us/library/dn140245.aspx

For the application:

Include <identity impersonate="true"/>  in system.web:

 <?xml version="1.0" encoding="utf-8"?>  
 <!--  
  For more information on how to configure your ASP.NET application, please visit  
  go.microsoft.com/fwlink/?LinkId=169433  
  -->  
 <configuration>  
  <system.web>  
   <compilation debug="true" targetFramework="4.5.2"/>  
   <httpRuntime targetFramework="4.5.2"/>  
   <identity impersonate="true"/>  
  </system.web>  
  <system.codedom>  
   <compilers>  
    <compiler language="c#;cs;csharp" extension=".cs"  
     type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"  
     warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701"/>  
    <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"  
     type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"  
     warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>  
   </compilers>  
  </system.codedom>  
 </configuration>  

Note that apparently "no value" is actually a value! Changing authentication settings manually in IIS, then deploying an app.config with different settings (or even missing values)... will change those settings in IIS!

For some (likely hardened security) reason, I am not able to view the web site at the server, even FQDN :

 

But it does work remotely - for client browsers not on the local server.

 

The point of running IIS code in the security context of the current user is to allow user-specific permissions - specifically user specific data - in SSAS OLAP cubes.... (details coming soon).