Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Accessing SQL Server with Managed Identities

Every developer knows that keeping credentials in code is a terrible idea, however, they some still have that moment of weakness, typically followed by, “It’ll just be temporary.” The simple fact is that storing credentials in a inconspicuous configuration file during development is just the easy way out. This is why Microsoft developed Managed Identities for Azure resource. Managed Identities used to securely gain access to an Azure KeyVault is a easy and safe way to store and access secrets for your application.

A common tactic then is to migrate all the sensitive connection strings into the KeyVault and simply pulling those down for the application to use to connect to a SQL server. However, authenticating to an Azure resource to retrieve the credentials to connect and authenticate with another Azure resource seems a bit redundent. Why not just skip the KeyVault step and have the SQL server authenticate directly with your application using its managed identity?

Enabling Managed Identity for an App Service

For this tutorial we’ll assume you already have an app service setup for your ASP.NET Core project. So, let’s go ahead and open the Azure Portal and navigate to that resource. Once there, find and select the menu item under “Settings” labeled “Identity”. On this page, should be a bright toggle switch, flip that to “On” and hit “Save” in the upper toolbar and we are done configuring the App Service. Though, you may want to leave this page open, as we’ll need its Object ID later.

1-mi

Grant the App Service Database Access

Now we need to grant database access to the newly established managed identity. Unlike a KeyVault, there’s no easy way to do this through the Azure Portal. So, we’ll have to get our hands dirty with the Azure CLI tools. Simply login using az login and then enter the following command.

az sql server ad-admin create --resource-group ResourceGroupName --server-name ServerName --display-name ADMIN --object-id "ObjectId"

Where:

  • ResourceGroupName is the name of the resource group the SQL server belongs to.

  • ServerName is the SQL Server we are trying to access.

  • ObjectId is the App Service’s object id that can be found on its Identity Settings page.

2-sql 1-mi

For our example then, the command would look like so:

az sql server ad-admin create --resource-group TutorialGroup --server-name tutorial-sql --display-name ADMIN --object-id "6c1f8191-5881-42b6-b108-6241109c22e1"

Now, we’re going to have to create a sql user for the managed identity and grant it the necessary permissions. Open up SQL Server Management Studio or whichever tool you use to run sql queries and enter the following.

CREATE USER [IdentityName] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [IdentityName];
ALTER ROLE db_datawriter ADD MEMBER [IdentityName];
ALTER ROLE db_ddladmin ADD MEMBER [IdentityName];
GO

Where IdentityName is the name of the managed identity in Azure AD. This should always be the same as the App Service name.

Thus, in our case the previous query would look like so:

CREATE USER [tutorial-app-service] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [tutorial-app-service];
ALTER ROLE db_datawriter ADD MEMBER [tutorial-app-service];
ALTER ROLE db_ddladmin ADD MEMBER [tutorial-app-service];
GO
Note:

You may also want to add your Azure Active Directory account to gain access to the server during development. Simply run the command and query again, this time replacing the App Service’s Object Id with your Azure AD account’s Object Id and replacing the Identity Name with your Azure AD associated email address.

Modify your ASP.NET Core Project

We’ll need to make the necessary adjustments to our ASP.NET Core project for it to be able to access the database correctly.

First, we need to install the NuGet package Microsoft.Azure.Services.AppAuthentication either through the Package Manager Interface or the Console by entering the following:

Install-Package Microsoft.Azure.Services.AppAuthentication -Version 1.4.0

Next, the connection string must be modified and any credentials removed from it or else the connection to the server will fail.

"DefaultConnection": "Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<database-name>;"

Onto the project’s Startup.cs file. Add a DbContext to your app similar to below.

services.AddDbContext<TutorialContext>(options => 
{
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
});

Finally, let’s modify your DbContext’s constructor to tell it to authenticate using it’s managed identity.

public TutorialContext(DbContextOptions<TutorialContext> options) : base(options)
{
    var connection = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
    var tokenProv = new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider();
    connection.AccessToken = tokenProv.GetAccessTokenAsync("https://database.windows.net/").Result;
}

The DbContext will only authenticate on first run and then retrieves the token from memory on for subsequent requests.

Your app should now be able to authenticate with the SQL Server using its Managed Identity. If you heeded my warnings, you should still be able to also run the app locally and have it access the database through your Azure AD account.

Share this post:

No comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\