Deploying ASP.NET Core app to Azure fails when connecting to / adding the database

Question

I am trying to deploy an ASP.NET Core 3.1 app using EF Core and Identity to Azure using an Azure SQL database. It builds successfully but fails deployment with the following error:

Trackily -> C:\Users\szapt\source\repos\sprzeng\Trackily\Trackily\bin\Release\netcoreapp3.1\Trackily.dll
Trackily -> C:\Users\szapt\source\repos\sprzeng\Trackily\Trackily\bin\Release\netcoreapp3.1\Trackily.Views.dll
Trackily -> C:\Users\szapt\source\repos\sprzeng\Trackily\Trackily\obj\Release\netcoreapp3.1\PubTmp\Out\
Generating Entity framework SQL Scripts...
Executing command: dotnet ef migrations script --idempotent --output "C:\Users\szapt\source\repos\sprzeng\Trackily\Trackily\obj\Release\netcoreapp3.1\PubTmp\EFSQLScripts\Trackily.Areas.Identity.Data.TrackilyContext.sql" --context Trackily.Areas.Identity.Data.TrackilyContext 
Generating Entity framework SQL Scripts completed successfully
Adding sitemanifest (sitemanifest).
Adding database (data source=tcp:trackilydbserver.database.windows.net,1433;initial catalog=Trackily_db;user id=trackilydb@trackilydbserver)
C:\Program Files\dotnet\sdk\3.1.400\Sdks\Microsoft.NET.Sdk.Publish\targets\PublishTargets\Microsoft.NET.Sdk.Publish.MSDeploy.targets(140,5): Warning : Cannot connect to the database 'Trackily_db'.  
Retrying operation 'Add' on object dbFullSql (data source=tcp:trackilydbserver.database.windows.net,1433;initial catalog=Trackily_db;user id=trackilydb@trackilydbserver). Attempt 1 of 10.
C:\Program Files\dotnet\sdk\3.1.400\Sdks\Microsoft.NET.Sdk.Publish\targets\PublishTargets\Microsoft.NET.Sdk.Publish.MSDeploy.targets(140,5): Warning : The database 'Trackily_db' could not be created.  
Retrying operation 'Add' on object dbFullSql (data source=tcp:trackilydbserver.database.windows.net,1433;initial catalog=Trackily_db;user id=trackilydb@trackilydbserver). Attempt 2 of 10.
C:\Program Files\dotnet\sdk\3.1.400\Sdks\Microsoft.NET.Sdk.Publish\targets\PublishTargets\Microsoft.NET.Sdk.Publish.MSDeploy.targets(140,5): Warning : The database 'Trackily_db' could not be created.  
...(this continues for 10 attempts)...
C:\Program Files\dotnet\sdk\3.1.400\Sdks\Microsoft.NET.Sdk.Publish\targets\PublishTargets\Microsoft.NET.Sdk.Publish.MSDeploy.targets(140,5): Error : Web deployment task failed. ((2020-08-08 6:08:21 PM) An error occurred when the request was processed on the remote computer.)

(2020-08-08 6:08:21 PM) An error occurred when the request was processed on the remote computer.
The database 'Trackily_db' could not be created.
Login failed for user 'trackilydb'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.Web.Deployment.SqlServerDatabaseProvider.AddHelper(DeploymentObject source)
Publish failed to deploy.

This is my first time deploying an app and using Azure, and I am following the instructions at:

https://learn.microsoft.com/en-us/aspnet/core/tutorials/publish-to-azure-webapp-using-vs?view=aspnetcore-3.1#deploy-the-app-to-azure

to do so.

I am currently thinking there might be an issue with applying the migrations I have locally with EF Core to Azure SQL. However, the information about this has been confusing to me and it seems like the migrations should be automatically applied with the following publish settings:

Publish settings - 1

Publish settings - 2

Thus far I have only used a local SQL database (SQL Server Express LocalDB, I believe) with EF Core and Identity while developing the app.

appsettings.json

{
  "ConnectionStrings": {
    "TrackilyContextConnection": "Server=(localdb)\\mssqllocaldb;Database=Trackily;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Startup.cs

public class Startup
    {
        private readonly IWebHostEnvironment _env;

        public Startup(IConfiguration configuration, IWebHostEnvironment env)
        {
            Configuration = configuration;
            _env = env;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<TrackilyContext>(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString("TrackilyContextConnection")));

            services.AddDefaultIdentity<TrackilyUser>(options => options.SignIn.RequireConfirmedAccount = false)
                .AddEntityFrameworkStores<TrackilyContext>();

            if (_env.IsDevelopment())
            {
                services.Configure<IdentityOptions>(options =>
                {
                    options.Password.RequireDigit = false;
                    options.Password.RequireLowercase = false;
                    options.Password.RequireNonAlphanumeric = false;
                    options.Password.RequireUppercase = false;
                    options.Password.RequiredLength = 3;
                    options.Password.RequiredUniqueChars = 0;

                    options.Lockout.AllowedForNewUsers = false;
                });
            }
            else
            {
                services.Configure<IdentityOptions>(options =>
                {
                    options.Password.RequireNonAlphanumeric = false;
                    options.Password.RequireUppercase = false;
                    options.User.AllowedUserNameCharacters =
                        "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-.";
                    options.User.RequireUniqueEmail = true;
                });
            }

            services.AddScoped<TicketService>();
            services.AddScoped<UserTicketService>();
            services.AddScoped<CommentService>();
            services.AddScoped<ProjectService>();
            services.AddScoped<UserProjectService>();

            services.AddAuthorization(options =>
            {
                options.AddPolicy(
                    "TicketEditPrivileges",
                    policyBuilder => policyBuilder.AddRequirements(
                        new TicketEditPrivilegesRequirement()
                    ));
                options.AddPolicy(
                    "ProjectEditPrivileges",
                    policyBuilder => policyBuilder.AddRequirements(
                        new ProjectEditPrivilegesRequirement()));
                options.AddPolicy(
                    "ProjectDetailsPrivileges",
                    policyBuilder => policyBuilder.AddRequirements(
                        new ProjectDetailsPrivilegesRequirement()));
                options.AddPolicy(
                    "ProjectDeletePrivileges",
                    policyBuilder => policyBuilder.AddRequirements(
                        new ProjectDeletePrivilegesRequirement()));
            });

            services.AddScoped<IAuthorizationHandler, TicketEditPrivilegesUserIdHandler>();
            services.AddScoped<IAuthorizationHandler, ProjectEditPrivilegesProjectIdHandler>();
            services.AddScoped<IAuthorizationHandler, ProjectDetailsPrivilegesProjectIdHandler>();
            services.AddScoped<IAuthorizationHandler, ProjectDeletePrivilegesProjectIdHandler>();
        }

        public static void Configure(IApplicationBuilder app, IWebHostEnvironment env, UserManager<TrackilyUser> userManager)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseDatabaseErrorPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                app.UseHsts();
            }

            app.Use(async (context, next) =>
            {
                await next();
                if (context.Response.StatusCode == 404)
                {
                    context.Request.Path = "/Home/Error/404";
                    await next();
                }
            });

            app.UseHttpsRedirection();
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthentication();
            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
                endpoints.MapRazorPages();
            });

            DbSeeder.SeedUsers(userManager);
        }
    }

Program.cs

public class Program
    {
        public static void Main(string[] args)
        {
            CreateHostBuilder(args).Build().Run();
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }

I have tried to change the connection string in appsettings to target the Azure SQL database using the connection string provided on the Azure portal, but that did not fix deployment. Alternatively, I think the issue could be connecting the app to the Azure SQL database but I'm not sure why setting the connection string wouldn't fix this.

Any direct help or pointing me in the right direction would be greatly appreciated.

You should be targeting the Azure SQL database in the connection string. Did you have your SQL server's username and password in the connection string? — tp803
– tp803, Commented Aug 9, 2020 at 15:51
@tp803 thank you, I think I was using the wrong login information in the connection string. It's a bit confusing because when you create the database there is a database administrator username and also a database connection username. I think I was using the latter in everything. — groznyj
– groznyj, Commented Aug 9, 2020 at 22:49

groznyj · Accepted Answer · 2020-09-09 01:35:30Z

2

I was able to fix my problems (though I was trying a bunch of things and I'm not sure which one specifically fixed it).

To other people who might encounter a similar issue, I would try the following steps:

Check that you are using the connection string found on your database's Azure page. You should be using your admin username and password, not the database username and password.
Add code to perform a migration on the database in the Configure method of Startup.cs. For example, services.BuildServiceProvider().GetService<YourDbContextName>().Database.Migrate();.
Connect to the Azure SQL database from within Visual Studio's SQL Server Object Explorer. You can follow the guide here https://medium.com/net-core/deploy-an-asp-net-core-app-with-ef-core-and-sql-server-to-azure-e11df41a4804 to do so.

edited Sep 9, 2020 at 1:35

answered Aug 9, 2020 at 22:47

groznyj

631 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Deploying ASP.NET Core app to Azure fails when connecting to / adding the database

appsettings.json

Startup.cs

Program.cs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

appsettings.json

Startup.cs

Program.cs

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related