CODEWITHSUNDEEP
asp.net-mvcazure-sql-databasesession-stateazure-managed-identity
jay
jay

Reputation: 1175

Session State MVC .net 4.8 using Azure SQL managed identity

I am using session variable which is stored in Azure SQL and cannot use redis cache.

I need to connect to azure sql using managed identity (cannot use user name and password).

Since we are using .net 4.8 we cannot use ";Authentication=Active Directory" Default regardless if we put it in the connection string or sessionstate.

I believe that ";Authentication=Active Directory" only works with .net core. therefore the solution provided by this article below does not work https://www.c-sharpcorner.com/article/moving-mvc-session-state-in-azure/.

We are also not allowed using username and password to connect to DB due to organisation policy. Does anyone have any other solutions? Thanks.

    <sessionState
    cookieName="MySession" cookieSameSite="Strict" mode="SQLServer"
    sqlConnectionString="Server=some-azure-sql-db.database.windows.net,1433;Database=ASPState;Authentication=Active
    Directory Default" />

Upvotes: 1

Views: 1033

Answers (1)

Rajesh Mopati
Rajesh Mopati

Reputation: 1514

As you cannot use Active Directory authentication and are not allowed to use a username and password to connect to the Azure SQL Database then you can use Azure Key Vault to store the connection string.

This approach assumes that you have already configured your Azure SQL Database to allow access from Azure services.

If you haven't done then you need to configure a firewall rule to allow access from Azure services, as well as enable the "Allow access to Azure services" setting in the Azure SQL Database configuration.

You can create a secret in Azure Key Vault with the connection string and then use the managed identity of your application to authenticate with the Key Vault to retrieve the connection string.

Steps to implement this approach:

  1. Create an Azure Key Vault instance and add a secret with the connection string to it.
  2. Enable managed identity for your Azure Web App or Azure Function that is hosting your application.
  3. Grant managed identity permissions to access the Key Vault.
  4. And modify your code to authenticate with the Key Vault using your managed identity and retrieve the connection string.

Retrieving the connection string from the Key Vault secret by creating a new instance of the SecretClient using the managed identity of the application.

using Azure.Identity;
using Azure.Security.KeyVault.Secrets;
string keyVaultUrl = "https://my-keyvault.vault.azure.net/";
string secretName = "connection-string";

var client = new SecretClient(new Uri(keyVaultUrl), new DefaultAzureCredential());

KeyVaultSecret secret = client.GetSecret(secretName);
string connectionString = secret.Value;

Fetching the secret

    [HttpGet]
    public string GetSecret()
    {
        var value = _configuration["Your Secret Name"];
        return "Value for Secret [YourSecretName] is : " + value;
    }

Fetching secret value

enter image description here

And the retrieved connection string from the Key Vault, can be used to establish a connection to the Azure SQL Database and store session variables as usual.

Another Approach

You can also use the Microsoft.Data.SqlClient library to connect to Azure SQL using managed identity.

And use the DefaultAzureCredential class to get a token from Azure Active Directory and then add it to the database connection.

Sample code to connect to Azure SQL using managed identity in .NET 4.8.

using Microsoft.Data.SqlClient; 
var conn = (System.Data.SqlClient.SqlConnection)Database.Connection; 
var credential = new Azure.Identity.DefaultAzureCredential();
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" })); 
conn.AccessToken = token.Token;

Thanks to Delora Bradish for the MSDoc.

Upvotes: -1

Related Questions