EN

Windows Server

Install SQL Server Express on Windows Server

Install SQL Server 2025 Express and SSMS on Windows Server, allow filtered and encrypted remote connections, then automate backups without SQL Server Agent.

9 min read, updated on 18 November 2026

On this page

Your values

Enter them once and the commands in this guide update.

SQL Server Express is the free edition of Microsoft's database engine. This guide installs SQL Server 2025 Express and SQL Server Management Studio (SSMS), opens restricted and encrypted remote access, then sets up scheduled backups.

Express edition limits

Check that these limits suit your project:

Item SQL Server 2025 Express
Maximum database size 50 GB
Buffer pool memory 1,410 MB per instance
Processor 1 socket or 4 cores, whichever is lower
SQL Server Agent Not included
Backup compression and encryption Not included
Transparent Data Encryption (TDE) Not included

Prerequisites

  • Windows Server 2019, 2022 or 2025, with an administrator account
  • .NET Framework 4.7.2 or later
  • At least 6 GB free on the system disk for the installation
  • 1 GB of memory recommended for Express (512 MB minimum)
  • The public IP address of the computer allowed to connect, if you need remote access

Run the commands in Windows PowerShell as administrator, unless stated otherwise.

Step 1: Install SQL Server Express

  1. Download Express from the Microsoft SQL Server page.
  2. Run the program, then choose Basic.
  3. Accept the license and the suggested folder, then start the installation.
  4. Note the instance name, SQLEXPRESS by default, and the connection string displayed at the end.

Your Windows account becomes an administrator of the instance (sysadmin role). Check the service:

PowerShell
Get-Service -Name 'MSSQL$SQLEXPRESS'
sqlcmd -S '.\SQLEXPRESS' -E -C -Q "SELECT @@VERSION;"

The -E option uses your Windows account. The -C option accepts the local self-signed certificate: without it, sqlcmd may refuse the connection, because encryption is mandatory by default with recent tools.

Step 2: Install SSMS

  1. Download the SSMS 22 program (vs_SSMS.exe) to the machine you chose.
  2. Run it and confirm. It opens Visual Studio Installer.
  3. Click Install, without adding any components you do not need.

On the server, connect with the name .\SQLEXPRESS and Windows authentication. Check Trust server certificate for this local connection.

Step 3: Create a database and an application account

Open a query in SSMS, or run sqlcmd -S .\SQLEXPRESS -E -C, then execute:

SQL
CREATE DATABASE AppDb;
GO
CREATE LOGIN app_user WITH PASSWORD = N'CHANGE_THIS_PASSWORD', CHECK_POLICY = ON;
GO
USE AppDb;
GO
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
GO

These roles allow reading and writing data. Only grant more rights if the application needs them, for example to create its tables.

The app_user account uses SQL Server authentication, so it can only connect after step 4. If no remote computer needs to access the instance, then skip steps 5 to 8 and go straight to the backups in step 9.

Step 4: Enable SQL Server authentication

A computer outside the domain usually connects with a SQL account, so you need to enable mixed mode.

In SSMS:

  1. Right-click the server, then open Properties and the Security page.
  2. Choose SQL Server and Windows Authentication mode, then confirm.

Using T-SQL:

SQL
USE [master];
GO
EXECUTE xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode',
    REG_DWORD,
    2;
GO

Then restart the instance:

PowerShell
Restart-Service -Name 'MSSQL$SQLEXPRESS' -Force

Leave the sa account disabled and use named accounts. Check its status:

SQL
SELECT name, is_disabled FROM sys.sql_logins WHERE name = 'sa';

Step 5: Enable TCP/IP on a fixed port

On a new Express installation, TCP/IP is disabled and the named instance uses dynamic ports. A fixed port lets you write a precise firewall rule, without opening the SQL Server Browser service (UDP 1434).

In SQL Server Configuration Manager (SQLServerManager17.msc):

  1. Open SQL Server Network Configuration, then Protocols for SQLEXPRESS.
  2. Right-click TCP/IP, then choose Enable.
  3. Open TCP/IP, then the IP Addresses tab.
  4. In the IPAll section, clear TCP Dynamic Ports and enter 1433 in TCP Port.
  5. In SQL Server Services, restart SQL Server (SQLEXPRESS).

Using PowerShell, with the official SqlServer module (accept the NuGet provider installation if prompted):

PowerShell
Install-Module -Name SqlServer -Scope AllUsers
Import-Module SqlServer

$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME
$tcp = $wmi.GetSmoObject("ManagedComputer[@Name='$env:COMPUTERNAME']/ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']")
$tcp.IsEnabled = $true
$ipAll = $tcp.IPAddresses['IPAll']
$ipAll.IPAddressProperties['TcpDynamicPorts'].Value = ''
$ipAll.IPAddressProperties['TcpPort'].Value = '1433'
$tcp.Alter()

Restart-Service -Name 'MSSQL$SQLEXPRESS' -Force
Get-NetTCPConnection -LocalPort 1433 -State Listen

Step 6: Restrict access in the firewall

Allow your address only:

PowerShell
New-NetFirewallRule -DisplayName 'SQL-Server-1433-In' -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress 'YOUR_IP' -Action Allow

This rule does not change your RDP access. The Firewall configuration guide shows the same operation in the graphical interface: add the address on the rule's Scope tab.

Step 7: Encrypt connections

Without a configured certificate, SQL Server generates a self-signed certificate that clients cannot verify. This script, adapted from the Microsoft documentation, creates a dedicated certificate. Replace the name with the one your clients will use, and keep the same PowerShell window for the rest of this step.

PowerShell
$name = 'sql.your-domain.com'
$params = @{
    Type              = 'SSLServerAuthentication'
    Subject           = "CN=$name"
    DnsName           = @($name, $env:COMPUTERNAME, 'localhost')
    KeyAlgorithm      = 'RSA'
    KeyLength         = 2048
    HashAlgorithm     = 'SHA256'
    TextExtension     = '2.5.29.37={text}1.3.6.1.5.5.7.3.1'
    NotAfter          = (Get-Date).AddMonths(36)
    KeySpec           = 'KeyExchange'
    Provider          = 'Microsoft RSA SChannel Cryptographic Provider'
    CertStoreLocation = 'Cert:\LocalMachine\My'
}
$cert = New-SelfSignedCertificate @params

# Allow the SQL Server service to read the private key
$key = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
icacls "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$key" /grant 'NT Service\MSSQL$SQLEXPRESS:R'

# Export the public part for client computers
New-Item -ItemType Directory -Force -Path 'C:\SqlCert' | Out-Null
Export-Certificate -Cert $cert -FilePath 'C:\SqlCert\sql-server.cer'

Then associate the certificate with the instance. In Configuration Manager:

  1. Right-click Protocols for SQLEXPRESS, then choose Properties.
  2. On the Certificate tab, select the certificate. If it does not appear because its name differs from the machine name, use the PowerShell equivalent below.
  3. On the Flags tab, set Force Encryption to Yes.
  4. Restart SQL Server (SQLEXPRESS).

PowerShell equivalent:

PowerShell
$instance = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').SQLEXPRESS
$net = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instance\MSSQLServer\SuperSocketNetLib"
Set-ItemProperty -Path $net -Name Certificate -Value $cert.Thumbprint.ToLower()
Set-ItemProperty -Path $net -Name ForceEncryption -Value 1
Restart-Service -Name 'MSSQL$SQLEXPRESS' -Force

Step 8: Connect from your computer

Copy C:\SqlCert\sql-server.cer to your computer, then import it in an administrator PowerShell window on that computer:

PowerShell
Import-Certificate -FilePath '.\sql-server.cer' -CertStoreLocation 'Cert:\LocalMachine\Root'

Test the port:

PowerShell
Test-NetConnection YOUR_SERVER_IP -Port 1433

In SSMS, enter:

Field Value
Server name YOUR_SERVER_IP,1433
Authentication SQL Server Authentication
Login app_user
Encryption Mandatory
Trust server certificate Unchecked
Host Name in Certificate sql.your-domain.com

The server name is written with a comma before the port. If sql.your-domain.com points to the server, use it directly as the name and leave Host Name in Certificate empty.

Check that the session is encrypted:

SQL
SELECT net_transport, encrypt_option, client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

encrypt_option should be TRUE.

Step 9: Automate backups

Express does not include SQL Server Agent, so Windows Task Scheduler takes over with sqlcmd.

Prepare the folders. The SIDs make the commands independent of the Windows language:

PowerShell
New-Item -ItemType Directory -Force -Path 'C:\Backups\SQL', 'C:\Scripts' | Out-Null
icacls 'C:\Scripts' /inheritance:r /grant:r '*S-1-5-32-544:(OI)(CI)F' '*S-1-5-18:(OI)(CI)F'
icacls 'C:\Backups\SQL' /inheritance:r /grant:r '*S-1-5-32-544:(OI)(CI)F' '*S-1-5-18:(OI)(CI)F' 'NT Service\MSSQL$SQLEXPRESS:(OI)(CI)M'

Create the file C:\Scripts\backup-sql.ps1:

PowerShell
$backupDir = 'C:\Backups\SQL'
$databases = @('AppDb')
$retentionDays = 14
$stamp = Get-Date -Format 'yyyyMMdd-HHmm'

foreach ($db in $databases) {
    $file = Join-Path $backupDir "$db-$stamp.bak"
    $query = "BACKUP DATABASE [$db] TO DISK = N'$file' WITH INIT, CHECKSUM; RESTORE VERIFYONLY FROM DISK = N'$file' WITH CHECKSUM;"
    sqlcmd -S '.\SQLEXPRESS' -E -C -b -Q $query
    if ($LASTEXITCODE -ne 0) { exit 1 }
}

Get-ChildItem -Path $backupDir -Filter '*.bak' |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$retentionDays) } |
    Remove-Item

The -b option returns an error to Windows if the backup fails. Without it, the task would appear to have succeeded.

Test the script, then schedule it to run every night. The account you specify must be a member of the Windows Administrators group and hold the sysadmin role, like the account that installed SQL Server. Windows will ask for its password:

PowerShell
powershell.exe -NoProfile -ExecutionPolicy Bypass -File 'C:\Scripts\backup-sql.ps1'
schtasks /Create /TN 'Backup-SQL-Server' /TR "powershell.exe -NoProfile -ExecutionPolicy Bypass -File C:\Scripts\backup-sql.ps1" /SC DAILY /ST 02:30 /RU 'YOUR_USER' /RP '*' /RL HIGHEST

Regularly copy the .bak files off the server: a backup stored on the same disk does not protect you from a failure of that disk or from malicious encryption.

Test a restore

Restore the backup under another name, without touching the production database. First, retrieve the logical file names:

SQL
RESTORE FILELISTONLY FROM DISK = N'C:\Backups\SQL\AppDb-20260101-0230.bak';

Then adjust the names AppDb and AppDb_log if the query shows other values:

SQL
DECLARE @data nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @log  nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultLogPath'));
DECLARE @mdf  nvarchar(300) = @data + N'AppDb_Test.mdf';
DECLARE @ldf  nvarchar(300) = @log + N'AppDb_Test.ldf';

RESTORE DATABASE AppDb_Test
FROM DISK = N'C:\Backups\SQL\AppDb-20260101-0230.bak'
WITH MOVE N'AppDb' TO @mdf, MOVE N'AppDb_log' TO @ldf, RECOVERY;

Check some of the data, then delete the copy with DROP DATABASE AppDb_Test;.

Check the result

  • Get-Service 'MSSQL$SQLEXPRESS' shows Running.
  • Get-NetTCPConnection -LocalPort 1433 -State Listen returns a line.
  • Your allowed computer connects with encrypt_option set to TRUE.
  • From another address, port 1433 does not respond.
  • Get-ScheduledTaskInfo -TaskName 'Backup-SQL-Server' shows LastTaskResult at 0 after a run, and a recent .bak file exists.

Troubleshooting

Error 18456 Login failed for user

The exact state appears in the SQL Server error log, usually C:\Program Files\Microsoft SQL Server\MSSQL17.SQLEXPRESS\MSSQL\Log\ERRORLOG:

State Cause
58 The server only accepts Windows authentication: go back to step 4
8 Incorrect password
7 Account disabled and incorrect password
5 or 2 Unknown account
38 or 46 Requested database not found
  • Make sure you enter IP,1433, with a comma.
  • Check that TCP/IP is enabled and that the service has been restarted.
  • Check that the port is listening with Get-NetTCPConnection -LocalPort 1433.
  • Compare your public address with the one in the SQL-Server-1433-In rule.

The certificate chain was issued by an authority that is not trusted

Your computer does not trust the certificate. Import sql-server.cer into the trusted root certification authorities. Avoid checking Trust server certificate for access over the Internet: the traffic would be encrypted, but the server's identity would no longer be verified.

The target principal name is incorrect

The name you used does not match the certificate. Fill in Host Name in Certificate or connect with the name listed in the certificate.

The service no longer starts after configuring the certificate

The service account probably cannot read the private key. Run the icacls command from step 7 again, then check ERRORLOG. As a last resort, set ForceEncryption back to 0 and clear the Certificate value in the SuperSocketNetLib key to restart the instance.

Error 3201 or Access is denied during backup

The SQL Server service writes the file, not your account. Check the permissions of NT Service\MSSQL$SQLEXPRESS on C:\Backups\SQL.

The scheduled task fails

Read its last result code:

PowerShell
Get-ScheduledTaskInfo -TaskName 'Backup-SQL-Server' | Select-Object LastRunTime, LastTaskResult

Code 1 often indicates a sqlcmd failure: run the script manually with the same account. If that account's password has changed, update the task:

PowerShell
schtasks /Change /TN 'Backup-SQL-Server' /RU 'YOUR_USER' /RP '*'

Error 1827 licensed limit

The database has reached the 50 GB Express limit. Delete data, archive it in another database, or move to a higher edition.