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
- In my Web App I have API endpoint:
POST api/migratewhich is responsible for migrate my database:
_dbContext.Database.Migrate();
- 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"
},
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.
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
- 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.