Can MSI work with EF CodeFirst?

Question

I've gotten stuck on this for quite a while now, with no luck advancing it on my own.

I am trying to connect from an Azure App Service to a EF CodeFirst managed database, using an MSI token.

When I deployed the App Service using ARM I produced an Output that ensured that it created a Service Principal:

 {
  "principalId":"98f2c1f2-0a86-4ff1-92db-d43ec0edxxxx","
  tenantId":"e6d2d4cc-b762-486e-8894-4f5f440dxxxx",
  "type":"SystemAssigned"
 }

In Kudu the environment variables show that it is being installed:

MSI_ENDPOINT = http://127.0.0.1:41239/MSI/token/
MSI_SECRET = 7C1B16Fxxxxxxxxxxxxx

I have provided in the Azure Portal a connection string as follows:

Data Source=nzmoebase0000bt.database.windows.net;Initial Catalog=nzmoebase0001bt;Connect Timeout=300;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=300;

I've added the principal to the database as an Owner.

Note: I cannot do the same for the master db.

The token is added to the DbContext as follows:

The token is being added using:

    static async Task AttachAccessTokenToDbConnection(IDbConnection dbConnection)
    {
        SqlConnection sqlConnection = dbConnection as SqlConnection;
        if (sqlConnection == null)
        {
            return;
        }
        string msiEndpoint = Environment.GetEnvironmentVariable("MSI_ENDPOINT");
        if (string.IsNullOrEmpty(msiEndpoint))
        {
            return;
        }

        var msiSecret = Environment.GetEnvironmentVariable("MSI_SECRET");
        if (string.IsNullOrEmpty(msiSecret))
        {
            return;
        }

        // To get around:
        // "Cannot set the AccessToken property if 'UserID', 'UID', 'Password', or 'PWD' has been specified in connection string."
        var terms = new[] {"UserID","Password","PWD=","UID=" };
        string connectionString = dbConnection.ConnectionString;

        foreach (var term in terms)
        {
            if (connectionString.Contains(term, StringComparison.InvariantCultureIgnoreCase))
            {
                return;
            }
        }

        string accessToken = await AppCoreDbContextMSITokenFactory.GetAzureSqlResourceTokenAsync();
        sqlConnection.AccessToken = accessToken;
    }

With tracing on, the token is:

 .eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI....

Which decoded using jwt.io gave:

{
  "typ": "JWT",
  "alg": "RS256",
  "x5t": "FSimuFrFNoC0sJXGmv13nNZceDc",
  "kid": "FSimuFrFNoC0sJXGmv13nNZceDc"
}.{
  "aud": "https://database.windows.net/",
  "iss": "https://sts.windows.net/e6d2d4cc-b762-486e-8894-4f5f440dxxxx/",
  "iat": 1522783025,
  "nbf": 1522783025,
  "exp": 1522786925,
  "aio": "Y2NgYPjNdyJd9zrzpLavJSEzNIuPAAA=",
  "appid": "d1057cea-461b-4946-89a9-d76439c2xxxx",
  "appidacr": "2",
  "e_exp": 262800,
  "idp": "https://sts.windows.net/e6d2d4cc-b762-486e-8894-4f5f440dxxxx/",
  "oid": "98f2c1f2-0a86-4ff1-92db-d43ec0edxxxx",
  "sub": "98f2c1f2-0a86-4ff1-92db-d43ec0edxxxx",
  "tid": "e6d2d4cc-b762-486e-8894-4f5f440dxxxx",
  "uti": "59bqKWiSL0Gf0bTCI0AAAA",
  "ver": "1.0"
}.[Signature]

I Added Persist Security Info = True as per several recommendations on the net, but that did nothing detectable.

Data Source=nzmoebase0000bt.database.windows.net;Initial Catalog=nzmoebase0001bt;MultipleActiveResultSets=False;Persist Security Info = True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

The error I am getting is:

[InvalidOperationException: This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection.]

Has anyone gotten a connection to a database, using CodeFirst, with Migrations, and MSI? At this point, after several weeks of being really stuck, I'm starting to wonder if it is possible.

Thanks for any help -- even if just proof that it can work, for starters.

Has this been answered? A colleague of mine is having the exact same issue. — sgeddes, Jan 10 '19 at 19:21
I am also trying to connect to SQL server database using MSI + code first approach(EntityFrameworkCore6). I have tried generating the token using the interceptor(Azure.Identity) as well as AzureServiceTokenProvider(AppAuthentication). I am getting errors while connecting to the database from my local machine. My database is not on the local. But, I am trying to connect it from Visual Studio. But, it gives me errors like below: Stored credentials not found. Need to authenticate user in VSCode Azure Account. See the troubleshooting guide for more information. does anyone have solution? — Jigna, Mar 04 '22 at 21:28

score 1 · Answer 1 · answered Jan 11 '19 at 21:34

1

Unfortunately, to my knowledge, no. A major stumbling block to a project that had to fall back to unsecure Username/password loaded connection strings.

answered Jan 11 '19 at 21:34

user10902655

11
1

score 1 · Answer 2 · answered May 01 '19 at 09:27

You can set the access token on the sql connection like that:

Install the Microsoft.Azure.Services.AppAuthentication nuget package

set up your context class like that:

public class MyDatabaseContext : DbContext
{
    public MyDatabaseContext(DbContextOptions<MyDatabaseContext> options)
        : base(options)
    {
        // Get the db connection
        var connection = (SqlConnection)Database.GetDbConnection();

        // Add the access token
        connection.AccessToken = new AzureServiceTokenProvider()
            .GetAccessTokenAsync("https://database.windows.net/")
            .ConfigureAwait(false).GetAwaiter().GetResult();
    }

    public DbSet<MyTable> MyTable { get; set; }
}

Can MSI work with EF CodeFirst?

2 Answers2

Linked