Cannot Migrate DB from code with EF core and AAD authorization to SQL server

228 Views Asked by At

Context

I am trying to authorize my WebApp in my SQL server. Both WebApp and SQL server are hosted on Azure. I use Net core 3.1 and EF core 3.1. My web app is hosted on linux machine. I also have to implement feature which enable to migrate database from API endpoint.

Repro tutorial I used

  1. In my Web App I have API endpoint: POST api/migrate which is responsible for migrate my database:
    _dbContext.Database.Migrate();
  1. In my web app I've enabled Managed Service Identity. It could be done from Azure portal, but I have done it in ARM template for my site:
    "identity": {
      "type": "SystemAssigned"
    }, 
  1. In my AAD I have created group (with type Security) called MSI-SQLSERVER-ACCESSGROUP. I added my web app to this group as service principal. I could do it because I'd enabled MSI before.

  2. In my business database, I have added Contained user called MSI-SQLSERVER-ACCESSGROUP

    CREATE USER MSI-SQLSERVER-ACCESSGROUP FROM EXTERNAL PROVIDER
    ALTER ROLE db_datareader ADD MEMBER MSI-SQLSERVER-ACCESSGROUP
    ALTER ROLE db_datawriter ADD MEMBER MSI-SQLSERVER-ACCESSGROUP
    ALTER ROLE db_ddladmin ADD MEMBER MSI-SQLSERVER-ACCESSGROUP
  1. I add AccessToken to db connection in my dbContext constructor:
    var conn = (SqlConnection)_dbContext.Database.GetDbConnection();
    conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

Problem

I can access database data by dbContext. Unfortunately I cannot migrate my DB. My endpoint return HTTP 500. Logs say:

The specified schema name "{appId}@{AAD tenant id}" either does not exist or you do not have permission to use it.

Tried

I have given db_securityadmin, db_accessadmin, db_owner to my user but it did not help.

0

There are 0 best solutions below