How to Scaffold Azure AD Database

[CodeSwimBikeRunner]

I have a paas instance and need to scaffold the database, the scaffold command does not seem to support Authentication=Active Directory Integrated.

Keyword not supported: 'authentication'.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: e1601c3e-3f29-5d84-6350-2b38eeaf6adc
• Version Independent ID: 92a856d6-c5da-e215-46ac-d67c411f3ff9
• Content: Reverse Engineering - EF Core
• Content Source: entity-framework/core/managing-schemas/scaffolding.md
• Product: entity-framework
• GitHub Login: @bricelam
• Microsoft Alias: bricelam

[bricelam]

Dupe of https://github.com/dotnet/corefx/issues/8807

[divega]

@ChristopherLClark until SqlClient for .NET Core supports the using the 'authentication` keyword in the connection string and implements SqlAuthenticationProviders, in order to use AAD-based authentication you need to obtain an access token using ADAL.NET and then set the AccessToken property on the SqlConnection object before you or EF Core opens it.

We still don't have first-class support in EF Core APIs or scaffolding to do this, but you can workaround that by adding some code. In particular the comment at https://github.com/aspnet/EntityFrameworkCore/issues/11928#issuecomment-387420827 describes a good way to handle this.

[divega]

Related: https://github.com/aspnet/EntityFrameworkCore/issues/13261.

[germancasares]

I am getting confused with this ticket and this one: https://github.com/aspnet/EntityFrameworkCore/issues/11928#issuecomment-454853868

How I am supposed to scaffold an Azure AD Database? I understand that once I have the Context and the entities the Context will connect to the Database by modifying the AccessToken, but how do I scaffold it in the first place?

[bricelam]

Workaround

// Note, the EF Core tools will discover this class in your startup project and use it
class MyDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
        => .AddSingleton<IDatabaseModelFactory, MyDatabaseModelFactory>();
}

class MyDatabaseModelFactory : SqlServerDatabaseModelFactory
{
    public override DatabaseModel Create(DbConnection connection, DatabaseModelFactoryOptions options)
    {
        // TODO: Acquire an access token
        ((SqlConnection)connection).AccessToken = "";

        return base.Create(connection, options);
    }
}

This should enable Scaffold-DbContext and dotnet ef dbcontext scaffold to work.

[germancasares]

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Scaffolding;
using Microsoft.EntityFrameworkCore.Scaffolding.Metadata;
using Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal;
using System.Data.Common;

namespace Infrastructure.Crosscutting
{
    public class DatabaseModelFactory : SqlServerDatabaseModelFactory
    {
        public DatabaseModelFactory(IDiagnosticsLogger<DbLoggerCategory.Scaffolding> logger) : base(logger)
        {
        }

        public override DatabaseModel Create(DbConnection connection, DatabaseModelFactoryOptions options)
        {
            // TODO: Acquire an access token
            connection.AccessToken = "";

            return base.Create(connection, options);
        }
    }
}

I tried this but DbConnection does not have a definition for AccessToken. How can I convert it to System.Data.SqlClient.SqlConnection so that I can set the token?

EDIT: Will casting work?

            ((SqlConnection)connection).AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;

Also DatabaseModelFactoryOptions is only available in a prerelease, or can I get it on asp.net core 2.2?

[bricelam]

Oh sorry, that code was based on master. Use the appropriate overload for the version you’re using. Casting to SqlConnection will work.

[mochr]

I am trying to scaffold a database in Azure SQL, using Azure AD to get an access token. I have attempted the workaround above, but can't get it to work. The following is the code that has been added to my project:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Scaffolding;
using Microsoft.EntityFrameworkCore.Scaffolding.Metadata;
using Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Identity.Web;
using System.Data.Common;
using System.Data.SqlClient;

namespace <my project namespace>.Models.DB
{
    class MyDesignTimeServices : IDesignTimeServices
    {
        public void ConfigureDesignTimeServices(IServiceCollection services)
        {
            services.AddSingleton<IDatabaseModelFactory, MyDatabaseModelFactory>();
        }
    }

    class MyDatabaseModelFactory : SqlServerDatabaseModelFactory
    {
        readonly ITokenAcquisition tokenAcquisition;

        public MyDatabaseModelFactory(IDiagnosticsLogger<DbLoggerCategory.Scaffolding> logger, ITokenAcquisition tokenAcquisition) : base(logger)
        {
            this.tokenAcquisition = tokenAcquisition;
        }

        public override DatabaseModel Create(DbConnection connection, DatabaseModelFactoryOptions options)
        {
            var scopes = new string[] { "https://database.windows.net//.default" };
            var accessToken = tokenAcquisition.GetAccessTokenOnBehalfOfUserAsync(scopes).Result;

            string serverName = "<my server>.database.windows.net";
            string databaseName = "<my database>";

            var connectionStringBuilder = new SqlConnectionStringBuilder
            {
                { "Data Source", $"tcp:{serverName},1433" },
                { "Initial Catalog", $"{databaseName}" },
                { "Persist Security Info", "False" },
                { "Connect Timeout", "30" },
                { "Encrypt", "True" },
                { "TrustServerCertificate", "False" },
            };

            connection.ConnectionString = connectionStringBuilder.ConnectionString;
            ((SqlConnection)connection).AccessToken = accessToken;

            return base.Create(connection, options);
        }
    }
}

This code does not build. Line 18 (services.AddSingleton) gives two errors:

The type 'my project namespace.Models.DB.MyDatabaseModelFactory' cannot be used as type parameter 'TImplementation' in the generic type or method 'ServiceCollectionServiceExtensions.AddSingleton<TService, TImplementation>(IServiceCollection)'. There is no implicit reference conversion from 'my project namespace.Models.DB.MyDatabaseModelFactory' to 'Microsoft.EntityFrameworkCore.Scaffolding.IDatabaseModelFactory'.

and

The type 'IDatabaseModelFactory' exists in both 'Microsoft.EntityFrameworkCore.Relational.Design, Version=1.1.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60' and 'Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'

There is also an error at line 31:

The type 'DatabaseModel' exists in both 'Microsoft.EntityFrameworkCore.Relational.Design, Version=1.1.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60' and 'Microsoft.EntityFrameworkCore.Relational, Version=3.1.1.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'

The version I'm using is .NET Core 3.1.

Is this still the recommended workaround to be able to scaffold an Azure SQL database? How can I fix the errors to be able to run the Scaffold-DbContext command?

[ErikEJ]

You are using EF Core 1 somewhere, and should remove those references: "Microsoft.EntityFrameworkCore.Relational.Design, Version=1.1.6.0,"

[mochr]

Ok, thanks! I removed the package and now the project builds. Now I need to find a way to get the right access token. Maybe using

var accessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

is a better way instead of using Microsoft.Identity.Web.

[mochr]

I finally got the scaffolding to work, at least for one database. For the other database only the context was generated, and not the table definitions.

I used the AzureServiceTokenProvider from Microsoft.Azure.Services.AppAuthentication, but had to re-enter my credentials in Tools -> Options -> Azure Service Authentication.

I also had to replace System.Data.SqlClient with Microsoft.Data.SqlClient.

The connection string seems to overwrite the parameter for the Scaffold-DbContext command, but the command parameter is what is added to the generated context-class.

[dedreira]

Hi! Do you plan to release this feature anytime? It would be cool to have the scaffold working using AAD Auth without workarounds 😃

[ErikEJ]

@dedreira You can do this already!

First add this to your project file:

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.1"/>

Then:

Scaffold-DbContext -Connection "Server=tcp:server.database.windows.net,1433;Initial Catalog=test;Authentication=Active Directory Interactive;Connection Timeout=60;" Microsoft.EntityFrameworkCore.SqlServer

Docs

[ErikEJ]

@ajcvickers Potentially this can be closed now?

[dedreira]

@dedreira You can do this already!

First add this to your project file:

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.1"/>

Then:

Scaffold-DbContext -Connection "Server=tcp:server.database.windows.net,1433;Initial Catalog=test;Authentication=Active Directory Interactive;Connection Timeout=60;" Microsoft.EntityFrameworkCore.SqlServer

Docs

@ErikEJ thanks a lot!! it works smoothly :)