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.
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
- Download Express from the Microsoft SQL Server page.
- Run the program, then choose Basic.
- Accept the license and the suggested folder, then start the installation.
- Note the instance name,
SQLEXPRESSby default, and the connection string displayed at the end.
Your Windows account becomes an administrator of the instance (sysadmin role). Check the service:
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
- Download the SSMS 22 program (
vs_SSMS.exe) to the machine you chose. - Run it and confirm. It opens Visual Studio Installer.
- 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:
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;
GOThese 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:
- Right-click the server, then open Properties and the Security page.
- Choose SQL Server and Windows Authentication mode, then confirm.
Using T-SQL:
USE [master];
GO
EXECUTE xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode',
REG_DWORD,
2;
GOThen restart the instance:
Restart-Service -Name 'MSSQL$SQLEXPRESS' -ForceLeave the sa account disabled and use named accounts. Check its status:
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):
- Open SQL Server Network Configuration, then Protocols for SQLEXPRESS.
- Right-click TCP/IP, then choose Enable.
- Open TCP/IP, then the IP Addresses tab.
- In the IPAll section, clear TCP Dynamic Ports and enter
1433in TCP Port. - In SQL Server Services, restart SQL Server (SQLEXPRESS).
Using PowerShell, with the official SqlServer module (accept the NuGet provider installation if prompted):
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 ListenStep 6: Restrict access in the firewall
Allow your address only:
New-NetFirewallRule -DisplayName 'SQL-Server-1433-In' -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress 'YOUR_IP' -Action AllowThis 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.
$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:
- Right-click Protocols for SQLEXPRESS, then choose Properties.
- 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.
- On the Flags tab, set Force Encryption to Yes.
- Restart SQL Server (SQLEXPRESS).
PowerShell equivalent:
$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' -ForceStep 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:
Import-Certificate -FilePath '.\sql-server.cer' -CertStoreLocation 'Cert:\LocalMachine\Root'Test the port:
Test-NetConnection YOUR_SERVER_IP -Port 1433In 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:
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:
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:
$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-ItemThe -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.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 HIGHESTRegularly 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:
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:
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'showsRunning.Get-NetTCPConnection -LocalPort 1433 -State Listenreturns a line.- Your allowed computer connects with
encrypt_optionset toTRUE. - From another address, port 1433 does not respond.
Get-ScheduledTaskInfo -TaskName 'Backup-SQL-Server'showsLastTaskResultat0after a run, and a recent.bakfile 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 |
A network-related or instance-specific error (errors 26 or 40)
- 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-Inrule.
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:
Get-ScheduledTaskInfo -TaskName 'Backup-SQL-Server' | Select-Object LastRunTime, LastTaskResultCode 1 often indicates a sqlcmd failure: run the script manually with the same account. If that account's password has changed, update the task:
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.