Connecting to Oracle in .NET Core and reading files located on a network share in IIS

I learned some new useful things about .NET Core recently when I attempted to query an Oracle database to find paths to files located on a restricted network share.

Querying an Oracle Database

The first thing I learned, is that if you want to integrate an Oracle database with your app, it is necessary to separately install the Oracle Data Provider for Entity Framework Core. The provider is available as the NuGet Oracle.EntityFrameworkCore package. Take note of the dependences because it is not guaranteed to stay in sync with the latest updates to Entity Framework Core. I had to downgrade one of my other packages to a version that was compatible with an older version of Microsoft.EntityFrameworkCore.Relational when I first installed it.

Once you have installed the package, you will be able to connect to an Oracle database with a connection string like so:
optionsBuilder.UseOracle(@”User Id=blog;Password=<password>;Data Source=pdborcl;”); After that, all of the queries and classes you will write should be the same as if if you were connecting to one of the database types that is bundled with Entity Framework Core.

Understanding the IIS AppPool Identity

Once I constructed a database query to find the paths to my files, my next step was to enable my application to read them from a folder on the network that grants access only to certain Windows users. My personal Windows account has read permission to the folder, so I did not notice that I couldn’t access the files until I published the application to the web server. That is when I learned that applications running in IIS conduct business with the Identity that is assigned to their Application Pool. The default identity is “ApplicationPoolIdentity” which means that the name of the AppPool will be used to determine whether it has permission to do things in Windows.

You can check in IIS Advanced Settings to find the name of the AppPool assigned to your application. For .NET Core apps it is usually
IIS AppPool\CoreApp.

You can grant the AppPool access to files on the local web server machine easily, by adding that user in the security settings for the folder.

You can also grant the AppPool permission to log into a local SQL Express database with the commands:

CREATE LOGIN [IIS APPPOOL\CoreApp] FROM WINDOWS 
CREATE USER [IIS APPPOOL\CoreApp] FOR LOGIN [IIS APPPOOL\CoreApp]

Accessing files from other machines on the network is more complicated.

The way I eventually found that worked, is to configure the application to impersonate a Windows active directory user that has the necessary permissions. We have a service account that we use for these purposes. To set a Windows user as the Identity for your application pool, in IIS go to Application Pools > Select your pool > Advanced Settings > Identity. Change the Identity to “Custom Account” and enter its username and password. The application will now impersonate that user and inherit its permissions.

Making files downloadable on the web

Once I was able to read the files, my final step was to make the files available for users to download from my webpage. One way to do this is to copy the files from the network share to a folder on the local web server that is configured as a virtual directory. You can copy the files using the .NET codeSystem.IO.File.Copy(networkFilePath, webserverFilePath, false); Then you can simply construct hyperlinks to the files with an html tag. <a href="webserverFilePath">FileName</a>

But, if as I suspect, you don’t want the files to hang around on your web server after the user downloads them, another solution is to serve up each file in response to an action. To do this, you can create a separate cshtml page. I called mine LoadImage.cshtml. In the code-behind, you write an onGet function like this:

LoadImage.cs code:

public async Task<ActionResult> OnGetAsync(string dosPath) {
     try {
          if (System.IO.File.Exists(dosPath)) {
               byte[] planImage = await Task.Run(() =>
               System.IO.File.ReadAllBytes(dosPath));
               string contentType = GetMimeMapping(dosPath);
               string fileName = Path.GetFileName(dosPath);
               return File(planImage, contentType, fileName);
          else {
               return Content("File not found at " + dosPath); } 
     } catch (Exception e) {
          return Content(e.Message); } 
}

You are passing the path to the file into this function. You can call the function in the cshtml with a hyperlink that invokes a JavaScript function. The script opens the LoadImage view in a new window, with the path passed in as a parameter. The experience for the user will be that the file downloads or an error message displays.

LoadImage.cshtml code:

@foreach (var item in Model.FileList) {
  <a href="#" onclick="LoadFile('item.dosPath')">item.fileName</a> }
<script> function LoadFile(dosPath) {
  window.open("LoadImage?dosPath=" + dosPath, "_blank"); } </script>

In case you don’t know the file type, I also wrote a fancy .NET function in the code-behind to detect it intelligently.

LoadImage.cs GetMimeMapping function:

using Microsoft.AspNetCore.StaticFiles;
private string GetMimeMapping(string dosPath) {
     string contentType;
     new FileExtensionContentTypeProvider().TryGetContentType(
          dosPath, out contentType);
     return contentType ?? "application/octet-stream"; }