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 :

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.

  1. Select the “Web API” option and make sure you enable authentication in “Work or School Accounts” mode and choose your Azure Active Directory domain.
  2. Check in the project properties if you are using the target framework “.Net Framework 4.6.2” if not set it to this version.
  3. 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
  1. 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/"
}
  1. 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>();
}
  1. 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();
}
  1. 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;
}
  1. 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;
}
  1. 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

In order for the application to delegate the permissions to Azure SQL Server the application registered for our API within Azure Active Directory will need to get this permission. The following steps will make sure you set this up and add alter the last settings within the configuration.

  1. Navigate to the Azure Portal (https://portal.azure.com/)
  2. Open the “Azure Active Directory” blade.
  3. In the blade click on “App Registrations”.

App Registrations

  1. In the “App Registrations” blade find the application that was registered by the creation of your project and click on it.
  2. In the application blade that opens click on “Required Permissions”.
  3. In the blade that opens click on “Add” to add a new permission.
  4. In the “Select an API” search for Azure SQL Database and select it.

App Permissions

  1. The permission step will open, make sure you select “Access Azure SQL DB and Data Warehouse” under “Delegated Permissions” and click “Select” and “Done to save the configuration.

App Delegated Permissions

In order to make this work without any problems we need to give consent for this application to access Azure SQL Databases as the logged in user.

App Grant Permissions

Note: Normally a user will get a consent window but as this is a API we will set this consent automatically for every user by clicking “Grant Permissions”.

  1. To make use of delegated permissions we also need a ClientSecret. Generate this within the Azure Portal and update the “appsettings.json” file within your project with this key and your connection string.

Publish the Application

With the application ready publish the API to Azure as you know from regular .Net framework projects. In the next post we will  create a console test application that will call the API with a specific user.

Related Posts

Restricting access to your Azure Web Application As you may know almost everything that is deployed to Azure is publicly available. As with Azure SQL Database you do not have a firewall available for...
Configure access to a private network for a Azure App Services On-Premise connections for Azure App Services can be created by using Hybrid Connections. Hybrid connections do not need any development or re-configu...
Extensions and Tips for deploying with Azure Resource Templates Working with Azure Services in different subscriptions means that the Azure Services need to run in different subscriptions. This often occurs when we...
Part 3 – Console application to call a API with Azure Active Directory Aut... This post is the third and last in a series of three posts and will help you with the creation of identity pass-through authentication from a client a...
Part 1 – Azure SQL Database with Azure Active Directory Authentication This post is the first post in a series of three posts and will help you with the creation of identity pass-through authentication from a client appli...
Pass-Through Authentication with Azure Active Directory, Azure SQL, Azure API an... In situations you need to login to an application and use that identity to access an API (pass-through identity) and also get data from Azure SQL Serv...

Leave a Reply

Your email address will not be published. Required fields are marked *