Part 2 – Azure API Application to query the Azure SQL Database

This post is the second in a series of three posts and will help you with the creation of identity pass-through authentication from a client application to API and then to an Azure SQL Database. In this post we will create an Azure API Application with .Net Core to query the Azure SQL Database.

Previous Post :

• Part 1 - Azure SQL Database with Azure Active Directory Authentication

Basics

To get everything up and running we will need to perform some basic tasks. These tasks include:

• Create a .Net Core API application
• Setting up application settings.
• Adding HttpContext

Create a .Net Core API Application

1. Open Visual Studio 2017
2. Create a new project with the template: “ASP.Net Core Web Application (.Net Framework)”.

Note: We create a ASP.Net Core Web Application with .Net Framework because the needed libraries for this solution are not build yet. This means this application will only run on Windows-based machines.

3. Select the “Web API” option and make sure you enable authentication in “Work or School Accounts” mode and choose your Azure Active Directory domain.
4. Check in the project properties if you are using the target framework “.Net Framework 4.6.2” if not set it to this version.
5. Open the NuGet Package manager and add the following package:
   • Microsoft.IdentityModel.Clients.ActiveDirectory

Setting up Application settings and adding HttpContext.

With the empty project setup we can start by adding basic things we will need in our application. The first thing is application settings in order to authenticate against Azure Active Directory and the Azure SQL Resource.

1. Add a new class and call it for example “ApplicationSettings” in this class we will add the properties we need in our configuration file.

public string ClientId { get; set; }

public string ClientSecret { get; set; }

public string AadInstance { get; set; }

public string Domain  { get; set; }

public string Audience { get; set; }

public string TenantId { get; set; }

public string AzureSqlResource { get; set; }

public string ConnectionString { get; set; }

 

Below is the description of the properties:

Property	Description
ClientId	The client id of the current application registered in Active Directory.
ClientSecret	The secret of the current client application.
AadInstance	The login URL of Azure Active Directory
Domain	The Domain of Azure Active Directory
Audience	The application resource App ID URL
TenantId	The Tenant Id of Azure Active Directory
AzureSqlResource	The resource Id of Azure SQL Databases, by default this is “https://database.windows.net”
ConnectionString	The Connection String to out Azure SQL Database

2. To load these settings within the application we will also need to place them in the .Net Core application file “appsettings.json” in this file add the following section.

"AuthenticationSettings": {
  "AadInstance": "https://login.microsoftonline.com/",
  "Audience": "{App ID URL Application}",
  "ClientId": "{ClientId}",
  "ClientSecret": "{ClientSecret}",
  "Domain": "{Azure Active Directory Domain}",
  "TenantId": "{Domain Tenant Id}",
  "ConnectionString": "Data Source={Azure SQL Server};Initial Catalog={Azure SQL Database};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",
  "AzureSqlResource": "https://database.windows.net/"
}

3. Loading these settings need to our settings object need to be configured first. Therefor we need to adjust the “ConfigureService” method in the StartUp.cs file. In this method we also add the loading of the “HttpContextAccessor”, this is all done by using Dependency Injection.

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    //Loads the configuration section into out Authentication Settings Object
    services.Configure<ApplicationSettings>(Configuration.GetSection("AuthenticationSettings"));
    //Make sure that we have a context within out ASP.Net Core Project
    services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();
}

4. Because we do not want duplicate settings in our settings file we will adjust the "Configure" methods as well.

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
    loggerFactory.AddConsole(Configuration.GetSection("Logging"));
    loggerFactory.AddDebug();

    app.UseJwtBearerAuthentication(new JwtBearerOptions
    {
        Authority = Configuration["AuthenticationSettings:AadInstance"] + Configuration["AuthenticationSettings:TenantId"],
        Audience = Configuration["AuthenticationSettings:Audience"]
    });

    app.UseMvc();
}

5. Move the values from the “Authentication” – “AzureAd” section in the “appsettings.json”file to the newly added “AuthenticationSettings” section and delete the complete “Authentication” section and save the file.

Setup a Authentication Helper

First up we will create a Authentication helper class that will handle the authentication actions. To get the identity of the logged in user we will retrieve the access token from the authorization header in the request.

1. Add a new class file to the project and call it “AuthenticationHelper”.
2. Add the below method, this method will retrieve the access token from the authorization header and construct a “UserAssertion” (The UserAssertion object is a credential type representing user credentials) object that is needed to authenticate against Azure Active Directory.

private static UserAssertion GetUserAssertion(IHttpContextAccessor httpContextAccessor) {
    UserAssertion retVal = null;

    string accessToken = httpContextAccessor.HttpContext.Request.Headers["Authorization"][0];
    string userAccessToken = accessToken.Substring(accessToken.LastIndexOf(' ')).Trim();

    Claim upn = httpContextAccessor.HttpContext.User.Claims.First(c => c.Type == "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn");

    if (upn != null) {
        retVal = new UserAssertion(userAccessToken, "urn:ietf:params:oauth:grant-type:jwt-bearer", upn.Value);
    }

    return retVal;
}

3. With the UserAssertion object that represents the current user we can retrieve a AccessToken from Azure Active Directory for Azure SQL Databases.

public static AuthenticationResult GetAuthenticationResult(IHttpContextAccessor httpContextAccessor, ApplicationSettings authSettings) {

    AuthenticationResult retVal = null;

    UserAssertion userInfo = GetUserAssertion(httpContextAccessor);
    ClientCredential clientCred = new ClientCredential(authSettings.ClientId, authSettings.ClientSecret);
    AuthenticationContext authContext = new AuthenticationContext(authSettings.AadInstance + authSettings.Domain);

    bool retry = false;
    int retryCount = 0;

    do {
        retry = false;
        try {
            retVal = authContext.AcquireTokenAsync(authSettings.AzureSqlResource, clientCred, userInfo).Result;

        } catch (AdalException ex) {
            if (ex.ErrorCode == "temporarily_unavailable") {
                retry = true;
                retryCount++;
            }
        }
    } while ((retry == true) && (retryCount < 1));

    return retVal;
}

The token retrieved by this method will be used as an access token for our Azure SQL Database

Create a API controller to query the database

1. Add a new controller to the controller folder and add the following fields and constructor in order to have everything in place (settings and httpcontext).

private IHttpContextAccessor httpContextAccessor;

private ApplicationSettings authSettings { get; set; }

public CustomerController(IHttpContextAccessor httpContextAcc, IOptions<ApplicationSettings> settings) {
    httpContextAccessor = httpContextAcc;
    authSettings = settings.Value;
}

2. Next up is implementing the “Get” method of the controller to retrieve the data from the SQL server.

[HttpGet]
public JsonResult Get() {
    JsonResult retVal = null;

    AuthenticationResult authResult = AuthenticationHelper.GetAuthenticationResult(httpContextAccessor, authSettings);

    if (authResult != null) {
        string queryString = "SELECT * FROM SalesLT.Product";

        using (SqlConnection connection = new SqlConnection(authSettings.ConnectionString)) {
            connection.AccessToken = authResult.AccessToken;
            try {
                connection.Open();
                SqlCommand command = new SqlCommand(queryString, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                DataTable table = new DataTable();  
                adapter.Fill(table);

                retVal = new JsonResult(table);

            } catch (SqlException ex) {
            }
        }
    }
    return retVal;
}

In the method the access token is retrieved by the authentication helper and passed through to the connection (the “AccessToken” properties is only available in the .Net Framework 4.6.2 or higher) then a select all query is performed on the SQL database to retrieve all the data and return it as a JSON string.

Delegate Permissions