Ss
Databases AWS

SQL Server 2025 Express on AWS User Guide

| Product: SQL Server 2025 Express on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using the SQL Server 2025 Express on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg Microsoft SQL Server on Ubuntu listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

SQL Server 2025 is Microsoft's relational database engine. Express is its free edition, for small production databases, development and learning. SQL Server 2025 adds a native vector data type and vector indexes, a native JSON data type, regular expressions in T-SQL and Query Store on by default; in SQL Server 2025, Express includes everything that used to be Express with Advanced Services, including full-text search.

What the cloudimg image adds:

  • No shared or default password. The image ships SQL Server installed but not set up: there is no master database, no sa password and no machine key in the AMI. On your instance's first boot, mssql-firstboot.service generates an sa password on the instance and runs Microsoft's own unattended setup with it, then writes it to /root/mssql-credentials.txt (root only). No two instances share a password, a certificate or an instance identity.
  • Fail closed. SQL Server refuses to start until first boot has finished. If first boot cannot complete, SQL Server stays stopped and the next boot tries again.
  • TLS on every connection. First boot creates the instance's own TLS key and self-signed certificate, and SQL Server forces encryption (TLS 1.2) for every client, even one that does not ask for it.
  • Localhost only. SQL Server listens on 127.0.0.1:1433. The security group opens SSH only; you reach the database through an SSH tunnel, or by changing the listen address yourself (see Connect from other hosts).
  • No metadata access for the database. SQL Server and its first-boot service are blocked from the EC2 instance metadata service, and the AMI requires IMDSv2.
  • Telemetry off (Microsoft's customer feedback program), SQL Server Agent off, and xp_cmdshell, OLE Automation, CLR, ad hoc distributed queries and the remote admin connection all disabled.
  • Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on.

What is on the image: SQL Server 2025 Express Edition 17.0.5015.4 (CU9 with the 2026-10-13 security update, KB5126967), mssql-tools18 (sqlcmd, bcp) and the Microsoft ODBC Driver 18 for SQL Server 18.7.1.1, all from Microsoft's official packages.microsoft.com repositories with the signing key pinned; cloud-init and the AWS Systems Manager agent.

Default user: ubuntu · Architecture: x86_64 · Recommended instance type: m5.large

Licensing

No SQL Server licence is sold with this AMI. SQL Server Express is free under the Microsoft Software License Terms, which your instance accepts on first boot, and it may be used in production within the Express limits:

  • the lesser of 1 socket or 4 cores,
  • 1410 MB of buffer pool memory,
  • 50 GB per database,
  • no SQL Server Agent.

When a workload outgrows Express, choose a Standard or Enterprise version of the listing, which are bring your own licence.

Prerequisites

  • An AWS account subscribed to the listing, an EC2 key pair and a VPC subnet.
  • A security group that allows TCP 22 (SSH) from your admin network. Do not open 1433: SQL Server listens on the loopback address only.

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick SQL Server 2025 Express on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large or larger, your key pair and a security group as above. SQL Server needs at least 2 GB of memory.

With the AWS CLI (replace the values in angle brackets):

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=sql-server-express}]'

On a new instance first boot takes about five minutes: Amazon EBS loads the AMI's blocks from its snapshot the first time they are read, and SQL Server's setup reads 1.4 GB of program files. Until it finishes, systemctl is-active mssql-firstboot reports activating and /root/mssql-credentials.txt holds a placeholder. Later boots start SQL Server in seconds.

Step 2: Connect to your instance

Version SSH user
SQL Server 2025 Express on Ubuntu 24.04 ubuntu 
ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the service

The first-boot service and SQL Server both report active:

systemctl is-active mssql-firstboot mssql-server

active
active

Check what is listening. SQL Server listens on the loopback address only: 1433 is the database port and 1431 is an internal SQL Server port. Only SSH listens on the network.

sudo ss -Htlnp | awk '{print $4, $6}' | sed 's/,pid=[0-9]*,fd=[0-9]*//g' | sort -u

0.0.0.0:22 users:(("sshd"),("systemd"))
127.0.0.1:1431 users:(("sqlservr"))
127.0.0.1:1433 users:(("sqlservr"))
127.0.0.53%lo:53 users:(("systemd-resolve"))
127.0.0.54:53 users:(("systemd-resolve"))
[::1]:1431 users:(("sqlservr"))
[::]:22 users:(("sshd"),("systemd"))

Step 4: Get your sa password

The credentials file is readable only by root. List the setting names it holds:

sudo ls -l /root/mssql-credentials.txt
sudo grep -oE '^[A-Z_0-9]+=' /root/mssql-credentials.txt

-rw------- 1 root root 686 Oct 20 00:49 /root/mssql-credentials.txt
SA_USER=
SA_PASSWORD=
SQL_ENDPOINT=
TLS_CERT=
TLS_CERT_SHA256=

Read the whole file with sudo cat /root/mssql-credentials.txt and store the password in your password manager. It is unique to this instance.

Never pass the password to sqlcmd with -P: it would land in your shell history and the process list. sqlcmd reads it from the SQLCMDPASSWORD environment variable instead. Load it once per shell:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)"

The blocks below repeat that line so that each one works on its own. sqlcmd is on the PATH of every login shell (/opt/mssql-tools18/bin); the blocks use the full path.

Step 5: Connect with sqlcmd

-C tells sqlcmd to trust the instance's self-signed certificate (Step 6 shows how to pin it instead).

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)"
/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -Q "SET NOCOUNT ON; SELECT CAST(SERVERPROPERTY('Edition') AS nvarchar(40)) AS edition, CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(20)) AS version, CAST(SERVERPROPERTY('ProductUpdateLevel') AS nvarchar(10)) AS cu"

edition                                  version              cu
---------------------------------------- -------------------- ----------
Express Edition (64-bit)                 17.0.5015.4          CU9

Step 6: Confirm the connection is encrypted

SQL Server forces encryption, so even a client that asks only for optional encryption (-No) gets an encrypted session:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)"
/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -No -Q "SET NOCOUNT ON; SELECT encrypt_option, protocol_type, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID"

encrypt_option                           protocol_type                            auth_scheme
---------------------------------------- ---------------------------------------- ----------------------------------------
TRUE                                     TSQL                                     SQL

The certificate is this instance's own, named after its hostname:

openssl x509 -in /etc/ssl/mssql/mssql.pem -noout -subject -enddate -ext subjectAltName -fingerprint -sha256

subject=CN = ip-172-31-83-35
notAfter=Oct 17 00:48:51 2036 GMT
X509v3 Subject Alternative Name:
    DNS:ip-172-31-83-35, DNS:localhost, IP Address:127.0.0.1
sha256 Fingerprint=FC:5A:69:98:E3:34:02:36:F6:E6:18:71:DD:77:CF:32:DF:DF:D0:4A:AE:A4:4E:4D:C0:8C:EA:7E:1F:11:FE:4A

The same fingerprint is in /root/mssql-credentials.txt as TLS_CERT_SHA256. To verify the server instead of trusting it blindly, copy /etc/ssl/mssql/mssql.pem (the public certificate, never mssql.key) to your client and add it to the client's trusted certificates, or pin the fingerprint in tools that support it. Because the certificate names localhost and 127.0.0.1, verification also passes through the SSH tunnel in Step 8.

Step 7: Run SQL

Create a database, write a row, read it back and drop the database:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)"
/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -W -Q "SET NOCOUNT ON; CREATE DATABASE demo; EXEC('USE demo; CREATE TABLE dbo.notes (id INT IDENTITY PRIMARY KEY, body NVARCHAR(200) NOT NULL); INSERT INTO dbo.notes (body) VALUES (N''hello from SQL Server 2025 Express'');'); SELECT id, body FROM demo.dbo.notes; DROP DATABASE demo;"

id body
-- ----
1 hello from SQL Server 2025 Express

For an application, create its own login and database instead of using sa. Run this in an interactive sqlcmd session (/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C), choosing your own strong password:

CREATE DATABASE appdb;
GO
CREATE LOGIN appuser WITH PASSWORD = N'<a-strong-password>', CHECK_POLICY = ON;
GO
USE appdb;
CREATE USER appuser FOR LOGIN appuser;
ALTER ROLE db_owner ADD MEMBER appuser;
GO

Step 8: Connect from other hosts

The safest way to use SQL Server Management Studio, Azure Data Studio, VS Code or an application on your workstation is an SSH tunnel. Port 1433 stays closed to the network:

ssh -i <your-key.pem> -N -L 1433:127.0.0.1:1433 ubuntu@<public-ip>

Then connect your client to 127.0.0.1,1433 with SQL Server authentication as sa, encryption Mandatory (or Strict), and either trust the server certificate or install mssql.pem as a trusted certificate (Step 6).

If applications on other hosts in your VPC must connect directly, open the listen address and restart SQL Server, then allow TCP 1433 in the security group from those hosts' CIDRs only, never from 0.0.0.0/0:

sudo /opt/mssql/bin/mssql-conf unset network.ipaddress
sudo systemctl restart mssql-server

Encryption stays forced. The certificate names the instance's hostname, localhost and 127.0.0.1; clients connecting by another name need to trust the certificate or use a certificate you issue for that name (mssql-conf set network.tlscert and network.tlskey, then restart).

Security details

Hardening settings, as SQL Server reports them:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)"
/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -W -Q "SET NOCOUNT ON; SELECT name, CAST(value_in_use AS int) AS value_in_use FROM sys.configurations WHERE name IN (N'xp_cmdshell', N'clr enabled', N'Ole Automation Procedures', N'Ad Hoc Distributed Queries', N'remote admin connections') ORDER BY name"

name value_in_use
---- ------------
Ad Hoc Distributed Queries 0
clr enabled 0
Ole Automation Procedures 0
remote admin connections 0
xp_cmdshell 0

Network, TLS and telemetry settings, and the metadata block on both services:

sudo /opt/mssql/bin/mssql-conf get network
sudo /opt/mssql/bin/mssql-conf get telemetry customerfeedback
systemctl show -p IPAddressDeny mssql-server mssql-firstboot

ipaddress : 127.0.0.1
tcpport : 1433
tlscert : /etc/ssl/mssql/mssql.pem
tlskey : /etc/ssl/mssql/mssql.key
tlsprotocols : 1.2
forceencryption : 1
customerfeedback : false
IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
  • First boot (/usr/local/sbin/mssql-firstboot.sh) creates the TLS key and certificate, generates the sa password into a root-only file in memory, and runs mssql-conf -n setup accept-eula with MSSQL_PID=Express, passing the password in the environment (never on a command line). It then checks the edition and build, that a wrong sa password is refused, that the connection is encrypted, that port 1433 serves the instance's certificate and listens on localhost only, and that the metadata service is unreachable, before it writes the credentials file. Its log is journalctl -u mssql-firstboot.
  • Start gate. mssql-server.service has a drop-in, /etc/systemd/system/mssql-server.service.d/50-cloudimg.conf, that blocks the metadata service and refuses to start before first boot has written /var/lib/cloudimg/mssql-ready.
  • Data and logs live under /var/opt/mssql (data/, log/errorlog); configuration is /var/opt/mssql/mssql.conf.

Maintenance

Updates. Ubuntu security updates install automatically. SQL Server cumulative and security updates come from Microsoft's repository, which the image already trusts; install them when you are ready:

sudo apt-get update
sudo apt-get install --only-upgrade mssql-server mssql-tools18 msodbcsql18

Backups. Back up to a local file, then copy it off the instance (for example to Amazon S3):

sqlcmd -S 127.0.0.1 -U sa -C -Q "BACKUP DATABASE appdb TO DISK = N'/var/opt/mssql/data/appdb.bak' WITH CHECKSUM"

Express does not support backup compression or encrypted backups. Take EBS snapshots of the volume as a second layer.

Change the sa password. Connect as sa and run ALTER LOGIN sa WITH PASSWORD = N'<new-password>';, then update /root/mssql-credentials.txt if you keep it.

Memory. Express uses at most 1410 MB of buffer pool memory whatever the instance size, so m5.large is enough for most Express workloads.

Support

cloudimg provides 24/7 technical support for this image by email and live chat: support@cloudimg.co.uk. SQL Server licensing questions go to Microsoft. Other versions of this listing are described in the SQL Server on Ubuntu guide.

Microsoft and SQL Server are trademarks of the Microsoft group of companies. Ubuntu is a registered trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by Microsoft or Canonical.