How to Connect ASP.NET Core to MS SQL Server on Windows Hosting

Most ASP.NET Core applications store their data in SQL Server, and a connection that works on your laptop often fails on a hosting server. The reasons are predictable: the connection string points at the wrong database, the database user lacks rights, the connection is encrypted with a certificate the server does not trust, or the secret was left in a file that never reached production. This guide shows how to create the database and user in Plesk, write a connection string that works on Windows hosting, connect with Entity Framework Core, and run the migration safely.

SiteHarbour’s Windows hosting plans include MS SQL Server 2019 Standard Edition on every plan, and the Business and Professional plans allow remote management of MS SQL. The steps below use the database that comes with the plan.

Before you start#

• An ASP.NET Core application that already uses Entity Framework Core, or that you intend to build with it.
• Your Plesk login, and access to the Databases section for the website.
• The NuGet package for SQL Server: Microsoft.EntityFrameworkCore.SqlServer. Install it with the matching version of your other EF Core packages.

Step 1: Create the database and its user in Plesk#

In Plesk, open Databases for the website and choose the option to add a database. Select MS SQL Server as the type, enter a database name such as shop_prod, and create a database user with a long password from your password manager. Give the user access to this database only.

Expected: the database appears in the list with its user. Do not give the application user rights to create other databases or to manage the server. Use one database and one user per application.

Step 2: Write the connection string#

The connection string names the server, the database and the user. In production, use the server name shown in Plesk for the database, which is usually localhost from the website itself:

Server=localhost;Database=shop_prod;User Id=shop_app;Password=YOUR_PASSWORD;Encrypt=True;TrustServerCertificate=False;

Replace the password and the names with your values. The Encrypt=True setting encrypts the traffic between the app and the database. If the server’s certificate is not trusted, the connection fails with a message about the certificate. Set TrustServerCertificate=True only as a temporary test, and then fix the certificate, because it removes protection against a man-in-the-middle attack.

Step 3: Keep the secret out of the project#

Store the connection string on the server, not in a file that goes into your repository. The safest place is the environment variable set for the website in Plesk, which the app reads through configuration:

ConnectionStrings__Default=Server=localhost;Database=shop_prod;User Id=shop_app;Password=YOUR_PASSWORD;Encrypt=True;

In ASP.NET Core, a double underscore in an environment variable name becomes a colon in configuration, so this value is read as ConnectionStrings:Default. Keep the production appsettings.json free of the password.

Step 4: Register the database context#

In Program.cs, register the context with the connection string from configuration:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Expected: the app starts without a configuration error. If it fails at startup with a missing connection string, the environment variable name is wrong. Check the spelling of ConnectionStrings__Default.

Step 5: Run the migration once, deliberately#

Create the schema from your migrations. Do this from a build machine that has the same connection string, rather than running migrations automatically on every start of a production site:

dotnet ef database update --connection "Server=localhost;Database=shop_prod;User Id=shop_app;Password=YOUR_PASSWORD;Encrypt=True;"

Expected: a list of migrations applied, then a finished message. Check the tables in Plesk’s database tools. Take a backup of the database before each migration on a live site, because a migration that drops a column cannot be undone from the schema alone.

Step 6: Test one database page#

Open a page that reads from the database, such as a product list or a login. A page that loads without data can hide a broken connection, so use one that shows real rows. If the page returns a 500 error, read the production log, as described in how to fix ASP.NET Core 502.5 process failure.

Common mistakes#

• Using the admin login in the app. The application needs rights to its own database only.
• Trusting every certificate. TrustServerCertificate=True is a test setting, not a production one.
• Committing the password. Rotate it at once if it reached a repository.
• Running migrations on every start. Run them deliberately, after a backup.
• Connecting to a different server name. From the website itself, localhost is the name that works.

Frequently asked questions#

Can I connect from my own computer to the hosted database?#

Remote management is available on the Business and Professional plans. Allow only your own address, and use encryption. Do not expose the database port to the whole internet.

Which SQL Server version does the plan use?#

SiteHarbour’s Windows plans include MS SQL 2019 Standard Edition, so write the code and migrations to work on that version.

Why does the connection work locally but not on the server?#

Usually the server name, the encryption settings, or a missing environment variable. The production logs show the exact error.

Do I need MariaDB as well?#

Only if a different application on the same plan uses it. The Plesk plans include MariaDB for those applications, and MS SQL for ASP.NET Core.

Connecting ASP.NET Core to MS SQL Server on Windows hosting comes down to three things: one database and one user for the app, a connection string kept out of the project, and migrations run deliberately after a backup. Encrypt the connection, test one database page, and keep the production password on the server only. For the Plesk side of the deployment, read how to deploy ASP.NET Core on Plesk Windows hosting.