I have a desktop app that creates a database using ef core migrations.
The app can use Postgres, Sql Server or Sqlite. I would like to use an Azure Sql Instance, with azure ad, to hold the database. I created an azure subscription, added an Azure Sql Instance, and set up azure ad on the subscription with one user. This user has global admin rights and is the admin for the Azure Sql Instance (the server has mixed mode authentication).
Then I created a wpf app, that has a simple database of books and their authors, and created migrations for Postgres, Sql Server and Sqlite. The migrations work for all the databases supported. At this point I added MSAL, and a db interceptor, I was already using Microsoft.Data.SqlClient version 5.
EF Core: Best practice way to connect to Azure SQL with AD Authentication
Then I registered the desktop application, with azure ad, as a multitenant application and set api permissions (I have also done another app registration, using single tenant). Using "APIs my orginization uses", I added Azure sql database, because Azure Sql Server is not listed (could this be a problem).
https://learn.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app
I can enter a server name for azure sql instance and a database name, then a valid access token is retrieved in the db interceptor and set on the SqlConnection. After Migrate is called there are quite a few sql exceptions and I get the error "Login failed for user ''".
The application displays a server name, user name, password and database name. It supports sql server right now, and has three ways to run:
- azure sql instance with azure ad Enter the azure sql instance (xxx.database.windows.net), database name and click created
- azure sql instance with its admin Enter the azure sql instance (xxx.database.windows.net), admin user name and password, database name and click created
- Local sql server with integrated security
Enter the sql server name, NO username or password (windows security), database name and click create. - Local sql server without integrated security Enter the sql server name, username, password, database name and click create.
The wpf app has no view model, it is just a simple means of allowing input for the user.
The app connects GtContext to the db interceptor AzureAdDbConnectorInterceptor, and the interceptor uses AzureAdCredentials to get the access token which is then set in the property AccessToken of Microsoft.Data.SqlClient.SqlConnection.
How can I get this working using azure ad and create the database using ef core migrations?
