ASP.NET Core Publish to Azure fails with Database Update

Question

Publishing an ASP.NET Core web app with database migrations fails with the following error in Visual Studio

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. dbFullSql http://go.microsoft.com/fwlink/?LinkId=178034  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_USER_NOT_AUTHORIZED_FOR_DBFULLSQL.)

And from the cmd line:

"C:\Dev\Neptune.Web\Neptune.Web.csproj" (default target) (1) ->
(MSDeployPublish target) ->
  C:\Program Files\dotnet\sdk\2.1.300\Sdks\Microsoft.NET.Sdk.Publish\build\netstandard1.0\PublishTargets\Microsoft.NET.Sdk.Publi
sh.MSDeploy.targets(139,5): error : Web deployment task failed. (Make sure the database connection string for the server is corr
ect and that you have appropriate permission to access the database. (Web Deploy Provider is "dbFullSql"). [C:\Dev\Neptune.Web\Neptune.Web.csproj]
C:\Program Files\dotnet\sdk\2.1.300\Sdks\Microsoft.NET.Sdk.Publish\build\netstandard1.0\PublishTargets\Microsoft.NET.Sdk.Publish
.MSDeploy.targets(139,5): error : Error details:Could not complete an operation with the specified provider ("dbFullSql") when c
onnecting using the Web Management Service. This can occur if the server administrator has not authorized the user for this oper
ation. dbFullSql http://go.microsoft.com/fwlink/?LinkId=178034  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERR
OR_USER_NOT_AUTHORIZED_FOR_DBFULLSQL.) [C:\Dev\Neptune.Web\Neptune.Web.csproj]

    0 Warning(s)
    1 Error(s)

Here's the weird thing its works fine if I comment out the local database connection string and apply the update directly using the connection string to the Azure SQL database (ie connect to the database directly in code):

C:\Dev\Neptune.Web>dotnet ef database update
Applying migration '20180613012042_CreateDate'.
Done.

Things I've done/checked:

Azure database server firewall settings shows my client IP address on the allowed list
I can connect to the database in Visual Studio and see the tables. I can see the changes to the schema once the database update has been applied
I've tried publishing from both inside VS and from the CL and same results when there database migrations to apply. If there are no migrations or the "Apply migrations" checkbox is unchecked then publish works fine.
The db user is the only one I've created for the database and all other interactions using that user work fine.
This was working in the past. Not sure if it stopped with the upgrade to 2.1 or before then but this failure started recently.

Sorry haven't had a chance to try that out. Will see if I can do so in a day or so. May log an issue on the tooling github site as well and see what they say. — Brad Patton

Joey Cai Joey Cai · Accepted Answer · 2018-06-15T09:35:52

According to your description, it is so weird. Here is an article about migratie existing asp.net core 2.0 application to asp.net core 2.1 you could refer to.

And Managing database schema and seeding data with EF core migrations.

If you want to apply migration at runtime, you could add the following code int the Configure method of Startup.cs.

 using (var scope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
    {
        scope.ServiceProvider.GetRequiredService<ApplicationDbContext>().Database.Migrate();
    }

For more details, you could refer to this thread.

ASP.NET Core Publish to Azure fails with Database Update

1 Answers