Trouble publishing ASP.NET project with Code First database to Azure - Permission

Question

I cannot migrate my Code First database to Azure - Error hints at permission, but I can't figure out what's missing?

• I have created an empty SQL-database in Azure
• (I can publish the web site without the EF migration)
• Connection string in appsettings.json (+Development+Production) - Startup: ConfigureServices - DbContext gets the right connectionstring
• serviceDependencies points to the right connectionstring - Local to LocalSecretsFile and Web Deploy to AzureAppSettings
• I have commented out the LocalDB connection though (and changed reference in ConfigureServices)
• I haven't deleted old migrations
• Azure: App Service - Configuration - Application Settings -> Connection string is set
• Azure: SQL Server - Access control (Classic administrator): Service administrator (me) has full access
• Azure: SQL Server - Firewalls and virtual networks: Client IP + App Inbound + 6 * App Outbound applied - 'Deny public network access' is not checked - Yes to 'Allow Azure services and resources ...'

When publishing: (Also tried running Visual Studio as administrator)

• I can Validate connection
• 'File Publish Options': I choose 'Remove additional files at Destination'
• 'Databases': 'NameofConnStr': Use this connection string at runtime
• 'Entity Framework Migrations': Apply this migration on publish
• 'Site Extensions Options': Tried both with and without this
• (I do not use the Key Vault)

Result:

• Publish Executing command: dotnet ef migrations script --no-build --idempotent --configuration Release --output "..."
• Generating Entity Framework SQL scripts completed successfully
• Adding sitemanifest (sitemanifest)
• Adding Child sitemanifest
• Error: Web deployment task failed. (Make sure the database connection string for the server is correct and that you have appropriate permission to access the database. (Web Deploy Provider is "dbFullSql").
• Error details:Could not complete an operation with the specified provider ("dbFullSql") when connecting using the Web Management Service. This can occur if the server administrator has not authorized the user for this operation
• Learn more for "ERROR_USER_NOT_AUTHORIZED_FOR_DBFULLSQL": Web Deploy error codes - Diagnosis: A non-administrative user attempted to perform an operation with a Web Deploy provider for which the user is not currently authorized
• (Build succeeded, Publish failed)

What's missing? Should I add a new migration (and delete the old ones)? Is there some problem with commenting out the connection to LocalDb? Is there some setting in Azure I missed?

Answer 1

Well I finally succeeded in migrating my database.

I upgraded Visual Studio 2019 from 16.11.13 to 16.11.14. That's probably not the reason why it worked, but I just note it because I'm not sure what made the difference.

If someone has the same problem I'll try to summarize what worked although I think I have tried the combinations below before:

• Ran Visual Studio as admin
• I deleted the previous migrations (made in LocalDB) and added a new
• The connectionstring only had Data Source/Initial Catalog/User Id/Password - i.e. not MultipleActiveResultSets/Encrypt/TrustServerCertificate/Connection Timeout
• I ignored KeyVault (and LocalDB) service dependencies
• New Publishing Profile
• Published the Web app first (without migration)
• Chose Apply migration and published again

All seems straight forward so it's a bit of a mystery ?!