MSI可以与EF CodeFirst一起使用吗? [英] Can MSI work with EF CodeFirst?

问题描述

我已经在这个问题上停留了很长时间，没有运气独自推进.

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

我正在尝试使用MSI令牌从Azure应用服务连接到EF CodeFirst托管数据库.

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

当我使用ARM部署App Service时，我产生了一个输出，以确保它创建了服务主体:

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"
 }

在Kudu中，环境变量表明正在安装它:

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

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

我在Azure门户中提供了如下的连接字符串:

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.

将令牌添加到DbContext中，如下所示:

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;
    }

启用跟踪后，令牌为:

 .eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI....

使用jwt.io解码的结果如下:

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]

我根据网上的一些建议添加了 Persist Security Info = True ，但这没什么可检测的.

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;

我得到的错误是:

[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.]

有人使用CodeFirst，Migrations和MSI与数据库建立了连接吗?在这一点上，经过数周的实际困扰，我开始怀疑是否有可能.

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.

推荐答案

不幸的是，据我所知，没有.项目的主要绊脚石，必须转而使用不安全的用户名/密码加载的连接字符串.

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

这篇关于MSI可以与EF CodeFirst一起使用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！