MigrateDatabaseToLatestVersion.Execute not working when deploying to web app in Azure but works fine locally when pointing to db in azure

Question

I have a problem with my automatic database migrations with my ASP.NET Core 8 Web API. It works fine when running my app locally, while pointing to the SQL database in Azure, but when I deploy it to the App Service in Azure, it fails. I'm using the admin credentials in my connection string at this point, so it really confuses me.

I followed this Youtube tutorial for context: https://www.youtube.com/watch?v=o9eEEFGgSHw

I'm using this code in my Program.cs:

I get this error in the log in Azure when the app deploys and tries to (what I guess) is running the MigrateDatabaseToLatestVersion.Execute:

I have no idea why this happens (I see that it says operation.ErrorCode:InsufficientAccountPermissions), and the ONLY difference is that the app is deployed in Azure, so it really confuses me how it can update the cloud database just fine when running locally, but not when deployed.

I tried using a user with db_reader, db_writer and db_owner permissions, but switched to the admin credentials directly in the connection string, but the error still persists.

I tried allowing "Access from other Azure resources" to no avail. Disabling the automatic migrations code lets the app deploy and run (but the whole purpose is automatic migrations).

I have absolutely no idea how it can break just because I'm deploying and not running it locally, as the local build can update the cloud database without issue.

Answer 1

MigrateDatabaseToLatestVersion.Execute:

The above error occurs mostly due to the version mismatch. Check the version both in local and azure portal. Try by providing contributor role instead of reader, writer, owner. Try with the below code it successfully worked both in local and in azure portal. Use the connection string pattern used in the below code.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "sqlblogdb": "Server=<server_name>.database.windows.net,1433;Initial Catalog=<db_name>;User ID=pbadmin1;Password=<password>;Connection Timeout=30"
  }
}

Below is the controller.cs:

namespace _78515051.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BlogController : ControllerBase
    {
        private IBlogService _blogService;

        public BlogController(IBlogService blogService)
        {
            _blogService = blogService;
        }

        [HttpGet]

        public async Task<IActionResult> GetAll()
        {
            var blogs = await _blogService.GetAllAsync();
            return Ok(blogs);
        }

        [HttpGet("{id}")]

        public async Task<IActionResult> GetById(int id)
        {
            var blog = await _blogService.GetByIdAsync(id);
            if(blog == null)
            {
                return NotFound();
            }
            return Ok(blog);
        }

        [HttpPost]

        public async Task<ActionResult> Create(Blog blog)
        {
            var createdBlog = await _blogService.CreateAsync(blog);
            return CreatedAtAction(nameof(GetById), new { id = createdBlog.Id}, createdBlog);
        }

        [HttpPut("{id}")]

        public async Task<IActionResult> Update(int id,  Blog updatedBlog)
        {
            var blog = await _blogService.UpdateAsync(id, updatedBlog);
            if (blog == null)
            {
                return NoContent();
            }
            return NotFound();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            var blog = await _blogService.DeleteAsync(id);  
            if(blog == null)
            {
                return NotFound();
            }
            return NoContent(); 
        }
    }
}

Below is the entity used to create a db and a table:

namespace _78515051.Entity
{
    public class Blog
    {
        public int Id { get; set; } 
        public string Name { get; set; }    
        public string Description { get; set; } 
        public string Author { get; set; }  
    }
}

Output: