Issue creating a database at run (with EF) time with Managed Identity (not user/pass)

Question

Issue creating a database at run (with EF) time with Managed Identity (not user/pass)

Sturla Þorvaldsson 176

When we are running EF Core´s MigrateAsync() command with a connection string that has Active Directory Managed Identity in it, it fails when the database doesn't already exist.

We have this EF Core github issue where Arthur Vickers mentions that maybe you guys would have some information regarding this issue and if there was any special pattern you would recommend us using?

Hope to hear from you.

0 comments

1 answer

Your answer

Answer 1

SSingh-MSFT 16,461 Moderator

Hi @Anonymous ,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand from the GitHub link, you are getting "Login failed for user '<token-identified principal>" when creating database in migration (at runtime) while creation of DB with Managed Identity.

In order to troubleshoot this issue, I would suggest first you need to make your SQL database AAD-enabled. Then you can create a user for your Managed Identity like this:

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;

After this, assign permissions to that user as needed.

Hope this helps. Please let us know if this works well and your inputs.

Also, added C# tags so that the team also could share inputs if related to EF side.

Thank you.

Sturla Þorvaldsson 176 Reputation points

2023-03-07T12:17:40.2333333+00:00
Thanks for the reply @ShaktiSingh-MSFT

We have checked and our SQL is AAD-enabled and since we are using the admin identity we should have every permission needed.

We were having this issue in another project also were we figured out that we also had to have Connection.Open() before being able to run any migration on top of the database having been created (with raw SQL) before.

Are you able to do this? It would be easy to create a web app project with migration on startup and see if it succeeds or not.

Here are quick steps to create the project (just to help this along)

dotnet new web -n TestProject

cd TestProject

dotnet add package Microsoft.EntityFrameworkCore

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet tool install --global dotnet-ef

dotnet ef migrations add InitialCreate

dotnet new classlib -n TestProject.Models

cd TestProject.Models

dotnet add MyEntity.cs

dotnet ef migrations add MyMigration

Then you need to

Add MigrateAsync() code somewhere so its run on runtime

Update the connection string

Push to Azure
SSingh-MSFT 16,461 Reputation points Moderator

2023-03-10T13:58:05.9566667+00:00

Hi @Sturla ,

Thanks for the reply.

Since the issue looks to be at the code side and not with Database, I need to get this checked with team collaboration.

Please allow some time to check and I will get back to you with inputs.

Thanks for your patience.
SSingh-MSFT 16,461 Reputation points Moderator

2023-03-14T09:34:50.14+00:00

Hi @Sturla ,

Thanks for your patience.

In order to get this issue troubleshooted, I recommend you to raise a support ticket in case you have support plan.

If you do not have a plan, let us know so that we can enable one time free support ticket for you to get this issue resolved.

Thanks.
SSingh-MSFT 16,461 Reputation points Moderator

2023-03-16T06:08:12.86+00:00

Hi @Sturla ,

We have not heard back from you. Could you please let us know if you need any assistance in getting the support ticket raised?

Thanks
Sturla Þorvaldsson 176 Reputation points

2023-05-02T08:04:31.83+00:00

Hi @ShaktiSingh-MSFT, sorry but I didn´t receive any email notification for your replies. We have raised a ticket about this that will be first handled by SoftwareOne (don´t know if you know them). I then guess this will end on your table if they can´t figure it out.

In the meantime we have moved from using the MigrateAsync() to using raw SQL to create the database and then running the Migrations. That works but it would be good to be able to use the EF functionality out of the box.
SSingh-MSFT 16,461 Reputation points Moderator

2023-05-02T09:21:30.72+00:00

Hi @Anonymous ,

Could you please share the SR# of the support case raised by you so that I can track the status.

It is good to know that you are able to proceed with the work. However, for the feature recommendation suggested by you, we encourage you to raise your Feeback over Azure SQL Database Feeback Channel so that concerned team would prioritize the request depending on the importance and up-votes.
Sturla Þorvaldsson 176 Reputation points

2023-05-17T13:04:32.91+00:00

Hi sorry for super late reply (because I don´t get any notification when you answer here)

The ticket number is INC7471809

Short Description: Issue creating a database at run (with EF) time with Managed Identity (not user/pass)

Hope this helps
SSingh-MSFT 16,461 Reputation points Moderator

2023-05-18T04:47:00.8933333+00:00

Hi @Sturla ,

Thanks for sharing the details with us.

Could you please check in Q&A profile settings if email is configured.

Link: https://learn.microsoft.com/en-us/answers/support/preferences