Msbuild: Publish to Azure fails when Database updates are needed

[bradleypatton]

Publishing an ASP.NET Core web app to Azure 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.)

From the cmd line:

msbuild /p:DeployOnBuild=true /p:PublishProfile=Staging /p:Configuration=Release /p:Username=xxx /p:Password=xxx

_GenerateEFSQLScripts:
  Generating Entity framework SQL Scripts...
  Executing command: dotnet ef migrations script --idempotent --output "C:\Dev\Neptune.Core\Neptune.Web\obj\Release\netcoreapp2.
  1\PubTmp\EFSQLScripts\Neptune.Web.Data.ApplicationDbContext.sql" --context Neptune.Web.Data.ApplicationDbContext
  Generating Entity framework SQL Scripts completed successfully
_PrepareForMsDeployPublish:
  Deleting file "C:\Dev\Neptune.Core\Neptune.Web\obj\Release\netcoreapp2.1\PubTmp\Neptune.Web.SourceManifest.xml".
  Deleting file "C:\Dev\Neptune.Core\Neptune.Web\obj\Release\netcoreapp2.1\PubTmp\Neptune.Web.Parameters.xml".
  Creating "C:\Dev\Neptune.Core\Neptune.Web\obj\Release\netcoreapp2.1\PubTmp\Neptune.Web.SourceManifest.xml" because "AlwaysCrea
  te" was specified.
  Creating "C:\Dev\Neptune.Core\Neptune.Web\obj\Release\netcoreapp2.1\PubTmp\Neptune.Web.Parameters.xml" because "AlwaysCreate"
  was specified.
MSDeployPublish:
  Starting Web deployment task from source: manifest(C:\Dev\Neptune.Core\Neptune.Web\obj\Release\netcoreapp2.1\PubTmp\Neptune.We
  b.SourceManifest.xml) to Destination: auto().
  Adding sitemanifest (sitemanifest).
  Adding database ()
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 : Web deployment task failed. (Make sure the database connection string for the server is correc
t and that you have appropriate permission to access the database. (Web Deploy Provider is "dbFullSql"). [C:\Dev\Neptune.Core\Ne
ptune.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.Core\Neptune.Web\Neptune.Web.csproj]
  Publish failed to deploy.    
        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:

1. Azure database server firewall settings shows my client IP address on the allowed list
2. 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
3. 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.
4. The db user is the only one I've created for the database and all other interactions using that user work fine.
5. 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.

Environment data

msbuild /version output:
Microsoft (R) Build Engine version 15.7.179.6572 for .NET Framework
Copyright (C) Microsoft Corporation. All rights reserved.

15.7.179.6572

OS info:
Windows 10

dontnet --version
2.1.300

[therealjawss]

i ended up here while trying to solve the same error message. The reason i got this same error was that i checked apply migrations but didnt' provide the connection string to the azure sql db. Once i grabbed that from Azure and put it in, the publish worked for me.

[livarcocc]

This is not the right repo for this issue.

To Get the quickest answer to your questions, please file a VS Feedback ticket and this issue will be triaged to the appropriate team.

[kgothatsotlaka]

i ended up here while trying to solve the same error message. The reason i got this same error was that i checked apply migrations but didnt' provide the connection string to the azure sql db. Once i grabbed that from Azure and put it in, the publish worked for me.

Thank you!

[Phoenix-encoder]

i ended up here while trying to solve the same error message. The reason i got this same error was that i checked apply migrations but didnt' provide the connection string to the azure sql db. Once i grabbed that from Azure and put it in, the publish worked for me.

Thanks u save me