The one about Single Sign-on for IIS apps using Azure App Proxy

   Hi guys , I'm back again with a little nugget of wisdom, talking today about Azure App Proxy.
For those of you not familiar with it, Azure Application Proxy provides secure remote access to on-premises web applications if you have single sign-on with Azure AD.So basically this removes the need for a vpn client making your on-premise applications available on the go.Pretty cool stuff, I would say.
    You can find comprehensive documentation and guides here: https://docs.microsoft.com/en-us/azure/active-directory/manage-apps/application-proxy

    As a proof of concept, my team and I played around with a couple of apps , some using forms-based authentication, some using windows-integrated and there is a tiny gotcha for IIS-based apps in order to get them working, which I'll mention later on in this article.
So looking at Single Sign-on options after you go through App registration and follow the steps in the MS guidelines you will want to go for WIA.
    This will work just fine and it's quite easy to set up:

   You basically start by following all the steps in this article:
https://docs.microsoft.com/en-us/azure/active-directory/manage-apps/application-proxy-configure-single-sign-on-with-kcd

   TL;DR version as follows:

   1.Install and configure your connector server(s).Very important: set up SPNEGO
https://docs.microsoft.com/en-us/azure/active-directory/manage-apps/application-proxy-connectors
https://docs.microsoft.com/en-us/azure/active-directory/manage-apps/application-proxy-connector-groups

REG ADD "HKLM\SOFTWARE\Microsoft\Microsoft AAD App Proxy Connector"

/v UseSpnegoAuthentication /t REG_DWORD /d 1net stop WAPCSvc & net start WAPCSvc

    2.Register your application(s):
https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app


    3.Go to your web app server on-prem and create an SPN with setspn -U -A HTTP/servername DOMAIN\service_account_name.Make sure the app pool runs under the service_account_name owning the SPN.

    4.Go to your AD Users and Computers console, select your connector servers, go to the delegation tab, allow for any delegation method and add the SPN created at step 3.

   5. In Azure AD go to Enterprise Applications - Select your registered app and add the Internal Application SPN and the delegated Login Identity:

    6. Test the app and connect to it via SSO after the Azure MFA login.If it doesn't work and you get Access Forbidden in your browser you may see this in the event viewer of your on-premises Application Proxy Connector server:

If this is the case you may want to add Negociate: Kerberos as an Authentication Provider in IIS for that particular app pool.In my case it was failing as the only enabled provider was NTLM and it obviously didn't handle Kerberos requests very well.:

And if it still fails please go to your App pool - Configuration editor --> system.webServer --> security --> authentication --> basicAuthentication - defaultLogonDomain: True

Have fun!

Using Python to create a json data source for Power BI out of a folder

    OK, so I'm back on the blog after a 6 year break with a nice, hopefully useful article on how to overcome certain Power BI limitations when using a folder as a data source.
    TL;DR --> python script is here: Github repo

  This endeavour started with the need of presenting a data trend in a line chart and all the relevant data at hand was split across hundreds .csv files, each file containing a daily summary of that data but no date information enclosed in the file.

So the meta data info is all we had at hand:

   At first I tried adding all the relevant .csv files to a folder as a data source and used Power BI to get data from that folder.As it turns out Power BI can't aggregate file meta data alongside the enclosed combined data from each file.


  Or you could probably add a custom column to do that but i haven't managed to figure that one out after searching the community forums.

   When combining the data you just get to play around with the file contents:

   So at this point I thought why not write a Python script that would fix this issue for me.I basically thought of parsing all the data in the .csv files and adding them as key:value pairs in a dictionary object that would be exported in a json file(which Power BI handles very well as a data source).
   The key can be the creation date or modified date of each of the .csv files and the value can be the actual data which we want to present in our time-series chart.
   For this to happen we would need to use the OS, csv, datetime and json modules, a function that will read the directory and get the dates and for each date it goes through each file and grabs the contents in a respective list to that date.The script can be obtained from my Github repo.
  After I've processed all the files I got a nice json file with the data structured like this:

   After loading it as a data source in PowerBI with Get Data - > Json, you get something like this:



   You can then convert it to a table and extract the values which are going to be separated by a delimiter.Through Power BI magic you can split them into columns and re-combine them , just in case the date format is dd/mm/yy in the file and your system is mm/dd/yy or the other way around.If you don't recombine them you will get a nasty error when trying to convert from text to date to get the time series date hierarchy.Screenshots of the subsequent transformations below:

 

     An important thing to note is that the list positions in Python starts at 0 which caused my dates to be misaligned to the data by one day.I managed to find this out by comparing some of the report values to their previous day in the tool that generated the csv's.The fix was easy in Power BI by adding a custom column which would offset the NewDate column by -1 day: