Base solution for your next web application
Open Closed

Implementing Azure SQL Server Managed Identity (SQL Token AD Authentication) #9781


User avatar
0
bancentral created

Hi.

We are using a project (.Net CORE 2.2 + JQuery) and we are looking for implementing an Azure SQL Server Managed Identity, for security porpuses we would like to remove the username and password from the connectionString in the appSetting by implementing the Managed Identiy connection. Do you have any documentation to apply this to the aspnet zero project? I tried implementing Azure SQL Server Managed Identity following the Microsoft documentacion by setting up some code in the Startup.cs ConfigureServices method but this did not worked. Do you have any suggestions?

This is what we have now following some documentations:

public IServiceProvider ConfigureServices(IServiceCollection services) { var connectionString = "Server=tcp:xxxxxx.database.windows.net;Initial Catalog=xxxxxxx;"; services.AddTransient(a => { var sqlConnection = new SqlConnection(connectionString); var credential = new DefaultAzureCredential(); var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" })); sqlConnection.AccessToken = token.Token; return sqlConnection; });

....

Thanks in advance.


2 Answer(s)
  • User Avatar
    0
    bancentral created

    Hello guys, i could implement what i was looking for, in case any other user had the same requirement, i had to overwrite the main project DbContext Constructor and set the corresponding configuration by calling the database token authentication here.

  • User Avatar
    0
    hra created

    Hi,

    For anyone who is interested, we have been operating with Managed Identity for a while - where the AbpDbContext obtains the Token within its constructor - however, recently we have started adding unit tests - and because the database is mocked with sqlite - this becomes a bit messy because it needs to know whether its running within unit tests or not.

    This is what we came up with, which is far cleaner anyway:

    public class AzureSqlConnectionTokenInjector : DbConnectionInterceptor
    {
        private AzureServiceTokenProvider _tokenProvider;
    
        public AzureSqlConnectionTokenInjector()
        {
            _tokenProvider = new AzureServiceTokenProvider();
        }
    
        protected virtual async Task EnsureAccessToken(DbConnection connection)
        {
            if (connection is SqlConnection sqlConnection
                && connection.ConnectionString.ToUpper().Contains("DATABASE.WINDOWS.NET")
                && string.IsNullOrWhiteSpace(sqlConnection.AccessToken))
                sqlConnection.AccessToken = await _tokenProvider.GetAccessTokenAsync("https://database.windows.net/");
        }
    
        public override InterceptionResult ConnectionOpening(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
        {
            EnsureAccessToken(connection).Wait();
    
            return base.ConnectionOpening(connection, eventData, result);
        }
    
        public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
        {
            await EnsureAccessToken(connection);
    
            return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
        }
    }
    

    Then use this in your 'DbContextConfigurer' class:

    public static class MyDbContextConfigurer
    {
        public static void Configure(DbContextOptionsBuilder<MyDbContext> builder, string connectionString)
        {
            builder.UseSqlServer(connectionString)
                .AddInterceptors(new AzureSqlConnectionTokenInjector());
        }
    
        public static void Configure(DbContextOptionsBuilder<MyDbContext> builder, DbConnection connection)
        {
            builder.UseSqlServer(connection);
        }
    }