Quick what type of password cannot be cracked? The answer is, one that is not known to anyone. You cannot reveal what you do not know. This is why so many people use Password Managers, we create insanely long passwords that we cannot remember, nor do we need to, and use them – their length and complexity makes it very difficult to crack. Plus, by making it easy to create these kinds of passwords we can avoid the other problem where the same password is used everywhere.

If you were to guess which password you would LEAST like to be compromised I am willing to bet many of you would indicate the password your web app uses to communicate with its database. And yet, I have seen so many cases where passwords to database are stored in web.config and other settings files in plain text for any would be attacker to read and use at their leisure. So I figured tonight I would tackle one of the easiest and most common ways to secure a password.

Remember RBAC

If you have been following my blog you know that, particularly of late, I have been harping on security through RBAC (Role Based Access Control). In the cloud especially, it is vital that applications only have access to what they need to carry out their role, such is the emphasis of least privileged security.

In Microsoft Azure, as well as other cloud platforms, we can associate the ability to read and update a database with a particular role and grant our web application an identity that is a member of that role. In doing so, we alleviate ourselves from having to manage a password while still ensuring that the application can only access data relevance to its task and purpose.

Microsoft actually has a doc article that details the steps I am going to take quite well. I will be, more or less, running through it and will enumerate any gotchas Link: https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

Creating your API

As I often do, I like to start from the default project template for a .NET Core Web API project. This means I have a basic API setup with the WeatherForecast related assets. The first goal will be to set this up as an EF Core driven application that auto creates its database and seeds with some data – effectively we are going to replace the GET call with a database driven select type operation.

To aid with this, and to remove myself from writing out how to build an API I am providing the source code here: https://github.com/jfarrell-examples/DatabaseMSI. From this point I will only call out certain pieces of this code and shall assume, moving forward, you have an API that you can call an endpoint and it will return data from the database.

Create a Database Admin

For the majority of these steps you will want to have the Azure CLI installed and configured for your Azure instance. You can download it here

Access Azure Active Directory from the Portal and create a new user. You can find this option off the main landing in the left navigation sidebar. Your use does not need to be anything special though, I recommend setting the password yourself.

Once the user is created, open a private window or tab and log in to https://portal.azure.com as that user. You do this to validate the account and reset the password, it shows as Expired otherwise. We are going to use this user as your Azure SQL Admin (yes, I assume you already created this).

The tutorial linked above provides a command line query to search for your newly created Azure AD User and get its corresponding objectId (userId for the uninitiated). I personally prefer just using the following command:

az ad user list –query “[].{Name: displayName,Id: objectId}” -o table

This will format things nicely and require you only to look for the display name you gave the user. You will want to save the objectId to a shell variable or paste it somewhere you can easily copy it.

az sql server ad-admin create –resource-group <your rg> –server-name <db-server-name> –display-name ADMIN –object-id <ad-user-objectId>

This command will install our user as an admin to the target SQL Server. Replace the values above as shown. You can use whatever you like for display-name.

Congrats you have now linked the AD User to SQL Server and given them admin rights. We wont connect as this user, but we need this user to carry out certain tasks.

Configure the WebAPI to Support MSI Login

As a note, the link above also details the steps for doing this with ASP .NET, I wont be showing that, I will be focusing only on ASP .NET Core.

We need to inform that which is managing our database connection, if anything (EF Core for me in this case) that we are going to use MSI authentication. As with most MSI related things, this will entail getting an access token from the identity authority within Azure.

Open the DbContext and add the following code as part of your constructor:

For this to work you will need to add the Microsoft.Azure.Services.AppAuthentication NuGet package but the rest of this can be pasted in as a good starting point.

I have also added a AppSettings Env which denotes the present environment. In this case, since I am showing an example I will only have Local and Cloud. In professional projects the set of allowable values is going to be higher. From a purpose standpoint, this allows the code to use a typical connection method (username and password) locally.

Remember, it is essential that, when developing systems that will access cloud resources, we ensure a solid way for developers to interact with those same resources (or a viable alternative) without having to change code or jump through hoops.

The final bit is to prepare our connection string for use in the Cloud with MSI. In .NET Core this change is absurdly simple, the tutorial shows this but you will want this connection string to be used in your cloud environments:

“Server=tcp:<server-name>.database.windows.net,1433;Database=<database-name>;”

With this in place, we can now return to the Cloud and finish our setup.

Complete the manged identity setup

The use of MSI is built upon the concept of identity in Azure. There are, fundamentally, two types: user defined and system assigned. The later is the most common as it allows Azure to manage the underlying authentication mechanics.

The enablement of this identity for your Azure Resources is easy enough from the portal but, it can also be done via the Azure CLI using the following command (available in the tutorial linked above):

az webapp identity assign –resource-group <rg-name> –name <app-name>

This will return you a JSON object showing the pertinent values for your Managed Identity – copy and paste them somewhere.

When you use a Managed Identity, by default, Azure will name the identity after the resource for which it applies, in my case this was app-weatherforecast. We need to configure the rights within Azure SQL for this identity – to do that, we need to enter the database.

There are a multitude of ways to do this but, I like how the tutorial approaches it using Cloud Shell. sqlcmd is a program you can download locally but, I always prefer to NOT add additional firewall rules to support external access. Cloud Shell allows me to handle these kind of operations within the safety of the Azure Firewall.

sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P “<aad-password>” -G -l 30

This command will get you to a SQL prompt within your SQL Server. I want to point out that the add-user-name is the domain username assigned to the user you created earlier in this post. You will need to include the “@mytenant.domain” suffix as part of the username. You are logging in as the ADMIN user you created earlier.

When your application logs into the SQL Server it will do as a user with the name from the identity given (as mentioned above). To support this we need to do a couple things:

• We must create a user within our SQL Server database that represents this user
• For the created user we must assigned the appropriate SQL roles, keeping in mind principle of least privileged access

From the referenced tutorial, you will want to execute the following SQL block:

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
GO
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
GO
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

Remember, identity-name here is the name of the identity we created earlier, or the name of your Azure resource if using System Assigned identity.

So, your case may vary but, deeply consider what roles your application needs. If you application will only be access the database to read you can forgo adding the datawriter and ddladmin roles.

If the database is already set in stone and you wont need new tables created by an ORM than you likely will not need the ddladmin role. Always consider, carefully, the rights given to a user. Remember, our seminal aim as developers is to ensure that, in the event of a breach, we limit what the attacker can do – thus if they somehow spoof our MSI in this case, we would want them to be confined to ONLY this database. If we used a global admin, they would then have access to everything.

Congrats. That is it, you now have MSI authentication working for your application.

Closing Thoughts

Frankly, there are MANY ways to secure the credentials for critical systems like databases in applications, from encryption, to process restrictions, to MSI – all have their place and all address the radically important goal of limiting access.

The reason I like MSI over many of these options is two principle reasons:

1. It integrates perfectly into Azure and takes advantage of existing features. I always prefer to let someone else do something for me if they are better at it, and Microsoft is better at identity management than I am. Further, since we can associate with roles inside Azure its easier to limit access to the database and other systems the corresponding application accesses
2. It totally removes the need to store and manage a password. As you saw above, we never referenced a password at any point. This is important since an attacker cannot steal what is never made available.

Attackers are going to find our information, they are going to hack our systems, we can do what we can to prevent this but, it will happen. So, the least we can do is make their efforts useless or limit what they can steal. Keep passwords out of source code, use Key Vault, and leverage good automation pipelines to ensure sensitive values are never exposed or “kept in an Excel somewhere”. Ideally, the fewer people that know these passwords the better.

The database is, for many applications, the critical resources and using MSI can go a long way to protecting our data and ensuring proper access and limit blast radius for attacks.

Thanks.