Skip to main content

SQL Server & Azure SQL

Tagnet supports SQL Server and Azure SQL Database as an alternative to the default SQLite provider. SQL Server is the recommended choice for hosted cloud deployments and for on-premises production environments where multiple concurrent users, high read/write throughput, or enterprise backup and recovery capabilities are required.

Part of: Administration — server-side configuration reference

When to Use SQL Server​

ScenarioRecommended Provider
Single-site, low-volume deploymentSQLite (default)
Multi-user production on-premisesSQL Server
Azure-hosted (Tagnet Cloud)Azure SQL
High read/write throughput (many readers, frequent scans)SQL Server
Enterprise backup and DR requirementsSQL Server

SQLite is a file-based database that works well for smaller deployments and development environments. SQL Server handles higher concurrency, supports enterprise-grade backup tooling, and integrates with Azure's managed database services.

Configuration​

To use SQL Server, set two values in your configuration:

{
  "DatabaseProvider": "SqlServer",
  "ConnectionStrings": {
    "DefaultConnection": "<your connection string>"
  }
}

The DatabaseProvider setting accepts Sqlite (default) or SqlServer. Tagnet uses the correct migration assembly for the selected provider automatically; no other changes are needed.

Important: Place connection strings in appsettings.Production.json (in the data directory) or via the ConnectionStrings__DefaultConnection environment variable. Never store database credentials in appsettings.json, which is overwritten during upgrades.

Connection String Formats​

Use this format when Tagnet runs as an Azure App Service with a system-assigned managed identity. No password is required.

Server=tcp:your-server.database.windows.net,1433;Database=tagnet;Authentication=Active Directory Default;Encrypt=True;

The App Service's managed identity must be granted the db_owner role (or at minimum db_datareader, db_datawriter, and ddl_admin) on the target database. See Azure Setup below.

SQL Server with Username and Password (On-Premises)​

Use this format for on-premises SQL Server instances with SQL authentication.

Server=your-server\INSTANCE;Database=tagnet;User Id=tagnet_user;Password=your-password;Encrypt=True;TrustServerCertificate=True;

Replace your-server\INSTANCE with your server name and instance (omit \INSTANCE for the default instance). TrustServerCertificate=True is typically needed for on-premises servers using self-signed certificates.

SQL Server with Windows Authentication (On-Premises)​

Use this format when Tagnet runs as a Windows service account that has database access.

Server=your-server;Database=tagnet;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;

Database Setup​

Tagnet runs EF Core migrations automatically on startup. You do not need to create tables manually. You do need to create the database and grant the application user sufficient permissions.

Minimum Required Permissions​

The database user or managed identity that Tagnet connects as needs:

  • db_datareader: read all tables
  • db_datawriter: insert, update, delete on all tables
  • ddl_admin: create and alter schema (required for migrations on startup)

db_owner includes all of the above and is the simplest choice for initial setup.

SQL Server: Create Database and User​

-- Create the database
CREATE DATABASE tagnet;
GO

-- Create a SQL login and user
CREATE LOGIN tagnet_user WITH PASSWORD = 'your-strong-password';
USE tagnet;
CREATE USER tagnet_user FOR LOGIN tagnet_user;
ALTER ROLE db_owner ADD MEMBER tagnet_user;
GO

Azure Setup​

Follow these steps to configure Azure SQL with Managed Identity for a Tagnet App Service deployment.

1. Create an Azure SQL Server and Database​

  1. In the Azure portal, create an Azure SQL Server resource
  2. Enable Microsoft Entra authentication (formerly Azure AD) on the server
  3. Set a Microsoft Entra admin: this is typically an Azure AD security group or your admin account
  4. Create an Azure SQL Database named tagnet on that server
    • For dev/QA environments, the Basic or S0 tier is sufficient
    • For production, choose a tier based on expected read/write volume and storage requirements
    • Azure SQL Elastic Pools are supported and recommended for hosting multiple environments on shared capacity

2. Enable Managed Identity on the App Service​

  1. Open the App Service in the Azure portal
  2. Go to Identity > System assigned
  3. Set the status to On and save
  4. Note the Object (principal) ID: you will need it in the next step

3. Grant the Managed Identity Database Access​

Connect to the Azure SQL database using an account with Microsoft Entra admin privileges and run:

-- Replace 'your-app-service-name' with your App Service name
CREATE USER [your-app-service-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [your-app-service-name];

The user name must match the App Service name exactly (this is the display name of the managed identity in Microsoft Entra ID).

4. Configure the Connection String​

In the App Service Configuration > Application settings, add:

NameValue
DatabaseProviderSqlServer
ConnectionStrings__DefaultConnectionServer=tcp:your-server.database.windows.net,1433;Database=tagnet;Authentication=Active Directory Default;Encrypt=True;

Replace your-server with your Azure SQL Server name (without .database.windows.net).

5. First Start and Migrations​

On the first startup with SQL Server configured, Tagnet will:

  1. Connect to the database using the configured credentials
  2. Run all pending EF Core migrations automatically
  3. Create all tables, indexes, and constraints

Check the application logs if startup takes longer than expected; migration output is logged at the Information level.

Database Size on the About Page​

When SQL Server is configured, the About page (visible to administrators) shows the current allocated database size and, for Azure SQL databases with a configured maximum, the limit. For example:

Database Size    2.4 MB / 32 GB

This reflects the allocated size of the data files from sys.database_files. For Azure SQL Elastic Pools, the maximum shown is the per-database maximum configured on the pool.

Backups​

Azure SQL​

Azure SQL databases are backed up automatically by Microsoft. Point-in-time restore is available for the retention period configured on the database (7 days by default, up to 35 days on premium tiers). No additional backup configuration is required.

To restore a database, use the Azure portal or Azure CLI:

az sql db restore \
  --resource-group your-rg \
  --server your-server \
  --name tagnet \
  --dest-name tagnet-restored \
  --time "2025-01-15T10:00:00Z"

On-Premises SQL Server​

Use SQL Server Agent jobs or a backup solution such as Ola Hallengren's maintenance scripts to schedule regular full and differential backups. Ensure transaction log backups are included if the database is in Full recovery mode.

Troubleshooting​

Login failed for user​

Verify the connection string credentials. For Managed Identity connections, confirm the App Service's managed identity has been added as a database user (CREATE USER ... FROM EXTERNAL PROVIDER).

Cannot open database requested by the login​

The database name in the connection string does not match an existing database on the server. Verify the database exists and the Database= value is correct.

Timeout expired / connection pool exhausted​

The default connection pool size is typically sufficient for Tagnet deployments. If you see timeouts under heavy load, check:

  • Server CPU and DTU usage in Azure Monitor
  • Whether another process is holding long-running transactions or locks
  • Consider scaling up the Azure SQL tier or SQL Server instance

Migration fails on startup​

If the application user does not have ddl_admin or db_owner, EF Core migrations will fail with a permission error. Grant the required permissions and restart.

To run migrations manually without granting schema-change permissions to the application account, use the EF Core CLI before deployment:

DatabaseProvider=SqlServer dotnet ef database update \
  --project src/StratumGlobal.TagNet.Data.Migrations.SqlServer \
  --startup-project src/StratumGlobal.TagNet.Api