Author: Warren LaFrance

A developer for over 15 years now. Focused on Web using the ASP.Net platforms.

Powershell convert table colm index to letters for excel

~ Warren LaFrance ~ Leave a comment


#==============================================================================
# Convert columns Interger values to Letters for excel column range selections
#==============================================================================
# 1 based values, if your array is not zero based subtract 1 from index first
function ExcelColumnIndexToName {
param(
[Parameter(Mandatory = $true)] [int] $index
)

[string]$range = [string]::Empty;
if ($Index -lt 0 ) { return $range; }

[int]$a = 26;
$mfr = [Math]::Floor([Math]::Log(($Index) * ($a – 1) / $a + 1, $a))
[int]$x = [int]$mfr;
$Index -= [int]([Math]::Pow($a, $x) – 1) * $a / ($a – 1);
for ([int] $i = $x + 1; $Index + $i -gt 0; $i–) {
$range = ([char](65 + $Index % $a)).ToString() + $range;
$Index /= $a;
}
return $range;
}

 

So in another method that loops through the columns I could do the following

 

foreach ($tmpDataColm in $table.Columns) {

# This is the index integer value of the column in the columns array of the datatable

$colIndex = $table.Columns.IndexOf($tmpDataColm);

Write-Host($colIndex);

$colLetter = ExcelColumnIndexToName($colIndex);

#$excelCol = ExcelColumnIndexToName($table.Columns.IndexOf($tmpDataColm));

Write-Host(“Table Column index value: $colIndex to Excel letter: ” + $colLetter );

 

}

So the output in the console looks like this

psoutput

You can use this to make range selections without knowing how many columns the sql is creating and etc…

 

Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING

~ Warren LaFrance ~ Leave a comment

I needed to pull data from a ASE Database (T-SQL), filter it and then dump it into a excel workbook.  Finally wrap all that up and attach it to a Email via SMTP…

See below for inline code or jump over to my github repo
https://github.com/wlafrance/Powershell/blob/master/QueryExcelEmail.ps

Continue reading “Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING” →

Quick NODE Demo site exposed to Internet…

~ Warren LaFrance ~ Leave a comment

WARNING – YOU WILL EXPOSE YOUR COMPUTER TO THE WORLD…

Simplest option ever: local tunnel

Even if you have a dynamic IP, or you’re under a NAT, you can deploy your app and serve the requests right from your computer using a local tunnel.  This option is suited for some quick testing, demo a product or sharing of an app with a very small group of people.

A very nice tool for this, available on all platforms, is ngrok.
Using it, you can just type ngrok PORT and the PORT you want is exposed to the internet. You will get a ngrok.io domain, but with a paid subscription you can get a custom URL as well as more security options (remember that you are opening your machine to the public Internet).

Another service you can use is https://github.com/localtunnel/localtun

‘ILoggerFactory’ does not contain a definition for ‘AddLog4Net’ and no accessible extension method

~ Warren LaFrance ~ Leave a comment

To fix this issue you need to run the npm command to install the MS Extension.

If you are getting this error:

‘ILoggerFactory’ does not contain a definition for ‘AddLog4Net’ and no accessible extension method ‘AddLog4Net’ accepting a first argument of type ‘ILoggerFactory’ could be found (are you missing a using directive or an assembly reference?)

You will need to add this extension to our project: [ link ]

Run this NUGET Command from the Package Manager Console in Visual Studio.

install-package Microsoft.Extensions.Logging.Log4Net.AspNetCore

 

VUE.JS and Angular – Enable Windows Authentication In Web API And Angular

~ Warren LaFrance ~ Leave a comment

Original Article: https://www.c-sharpcorner.com/article/enable-windows-authentication-in-web-api-and-angular-app/

Re-posted in case site goes away

Other resource: https://www.npmjs.com/package/activedirectory

 

 

Introduction

In this article, we will learn about how to use inbuilt Windows authentication in Web API and Angular application for authentication and authorization purposes.

Description

We have a requirement for in-house project development in the Angular App using Web API. As the purpose of this application is to use inside office only,  so it’s suggested to use Windows Authentication mode.

Prerequisite

To access any web API from Angular or any Ajax method Web API must be CORS (Cross Origin Resource Sharing) enabled otherwise the request is not executed.

You can achieve this by referring to the below links.

Step 1

Create Web API Project and in Web.config select Authentication mode as “Windows”,

Enable Windows Authentication in Web API and Angular App

 

Web Config Code snippet

  1. <system.web>
  2.     <authentication mode=“Windows” ></authentication>
  3. </system.web>

Or you can publish web API project in IIS and Enable Windows Authentication from there.

Enable Windows Authentication in Web API and Angular App

 

So, based on the above screenshot you can enable Windows authentication for Web API Project.

Step 2

Use Authorize attribute on the controller or on any action method for security.

Enable Windows Authentication in Web API and Angular App

 

Code snippet for WebAPI Controller

  1. [EnableCors(origins: “*”, headers: “*”, methods: “*”, SupportsCredentials = true)]
  2.     public partial class WebAPIController : ApiController
  3.     {
  5.         /// <summary>
  6.         /// This method contains Authorize attribute for authentication and authroization
  7.         /// </summary>
  8.         /// <returns></returns>
  9.         [HttpGet]
  10.         [Authorize]
  11.         [Route(“api/AuthenticateUser”)]
  13.         public HttpResponseMessage AuthenticateUser()
  14.         {
  15.             if (User != null)
  16.             {
  17.                  return Request.CreateResponse(HttpStatusCode.OK, new
  18.                 {
  19.                     status = (int)HttpStatusCode.OK,
  20.                     isAuthenticated = true,
  21.                     isLibraryAdmin = User.IsInRole(@“domain\AdminGroup”),
  22.                     username = User.Identity.Name.Substring(User.Identity.Name.LastIndexOf(@“\”) + 1)
  23.                 });
  24.             }
  25.             else
  26.             {
  27. //This code never execute as we have used Authorize attribute on action method
  28.                 return Request.CreateResponse(HttpStatusCode.OK, new
  29.                 {
  30.                     status = (int)HttpStatusCode.BadRequest,
  31.                     isAuthenticated = false,
  32.                     isLibraryAdmin = false,
  33.                     username = “”
  34.                 });
  36.             }
  37.          }
  38.     }

As per the above screenshot, I have added [Authorize] attribute in AuthenticateUser Action method. This attribute makes sure that action is only executed if the user entered a valid credential otherwise it will display 401 Unauthorized access.

Here, I have added [Authorize] attribute only to action method. This can be put at Controller level as well and if the application has multiple Roles then it can be extended by passing Role name along with Authorize Attribute. You can explore more from WebAPI Authorization

While testing WebAPI from localhost (With Visual Studio by hitting F5 or Ctrl + F5) I am always getting this error {“Message”:”Authorization has been denied for this request.“}. However, if I publish this same website in IIS then I am getting a valid response as below with no extra work required.

{“status”:200,”isAuthenticated”:true,”isLibraryAdmin”:false,”username”:”admin”}

Here, username (admin it can be any user) is logged in user in windows system (or Virtual Machine).

Stack Overflow Question – https://stackoverflow.com/questions/24194941/windows-authentication-not-working-in-iis-express-debugging-with-visual-studio

When we run or debug the application from Visual Studio it is not hosted in IIS, instead it hosts in IISExpress which is part of the Visual Studio and stores the minimum required configuration or default configuration to run any application.

So, based on the provided solution from above Stack overflow question I have enabled windows authentication (<windowsAuthentication enabled=”true”>) in “applicationhost.config” file which resides at Project root directory “.vs\config”, this folder is hidden you must enable the show all hidden files and folder option.

Enable Windows Authentication in Web API and Angular App

 

By Default, Windows authentication value is false in  “applicationhost.config”

Now, we have successfully enabled Windows authentication in WebAPI Project.

Step 3

As per the prerequisite enable CORS at controller level along with SupportCredentials true,

Enable Windows Authentication in Web API and Angular App

 

As per screenshot, enable CORS with the provided configuration. Here Instead of “*“(Allow from any origin), you can restrict with specific IP Address or domain name.

Step 4

CORS is enabled from the server side. Now while requesting API, pass flag withCredentials: true from the frontend.

For jQuery Ajax you must pass the request as below.

  1. $.ajax({url:apiURL ,xhrFields: {   withCredentials: true }, success:successHandler });

 

For Angular you must pass the request as below.

  1. private options = new RequestOptions({ withCredentials: true });
  2. this.http.get(this.baseUrl, this.options)

Code Snippet

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. “https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js”&gt;
  6. $(document).ready(function(){
  7.     $(“button”).click(function(){
  8.     // IIS localhost but with host domain name, with support credential true in Enable CORS
  9.      //var apiURL=”http://libraryapi.com/api/authenticateuser&#8221;;
  10.     // IIS Localhost with support credential true in Enable CORS
  11.     //var apiURL=”http://localhost:3005/api/AuthenticateUser&#8221;;
  12.     //IIS Express with support credential true in Enable CORS
  13.     var apiURL=“http://localhost:51647/api/AuthenticateUser&#8221;;
  15.         $.ajax({url:apiURL ,xhrFields: {
  16.                         withCredentials: true
  17.                     }, success: function(result){
  18.                     console.log(JSON.stringify(result));
  19.                     document.write(JSON.stringify(result));
  20.             $(“#div1”).html(JSON.stringify(result));
  21.         }});
  22.     });
  23. });
  25. </head>
  26. <body>
  28. “div1”>

    Let jQuery AJAX to Access Web API Response with Windows Authentication

  30. <button> Click Me to Get Web API Response</button>
  32. </body>
  33. </html>

Now, we have successfully consumed Windows Enabled Authenticated WebAPI .

In case of accessing WebAPI, the dialog for login and password prompts infinitely for the correct password. Then you need to disable the loopback check by executing PowerShell command

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name “DisableLoopbackCheck” -value “1” -PropertyType dword

Reference link

Conclusion

In this article, we learned about how to enable Windows Authentication in Web API and Consume Secure web API from jQuery Ajax and Angular App.

IIS stopped working for .NET WEBFORMS app

~ Warren LaFrance ~ Leave a comment

On my local box IIS just stopped working when the sites are mapped to IIS and not the DEV IIS Express instance.

After a bit of searching I did the following to my web.config. I have not had time to research what caused the change in my box that has broken it, but for now I need it to just work.

 

The changes…

In the appSettings I added these two sections for my old ASP.NET Web Forms application

    <add key="aspnet:UseTaskFriendlySynchronizationContext" value="false" />
    <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />