SQL Server 2022 Standard on Ubuntu 22.04

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick SQL Server 2022 Standard on Ubuntu 22.04 as the software version, select your Region, and choose Launch through EC2.

Choose m5.large or larger, your key pair and a security group as described above. SQL Server needs at least 2 GB of memory.

Alternatively, launch 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-standard}]'

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 first boot finishes, systemctl is-active mssql-firstboot reports activating, and /root/mssql-credentials.txt holds a temporary value instead of your credentials. Later boots start SQL Server in seconds.

Connect over SSH as the ubuntu user:

ssh -i <your-key.pem> ubuntu@<public-ip>

Confirm that the first-boot service and SQL Server both report active:

systemctl is-active mssql-firstboot mssql-server

active active

Then check what is listening. SQL Server listens on the loopback address only:

• 1433 is the database port.
• 1431 is an internal SQL Server port.
• 1434 is the local dedicated administrator connection (DAC). A sysadmin can use this emergency connection from the instance itself when SQL Server stops responding.

Remote DAC is off, and 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")) 127.0.0.1:1431 users:(("sqlservr")) 127.0.0.1:1433 users:(("sqlservr")) 127.0.0.1:1434 users:(("sqlservr")) 127.0.0.53%lo:53 users:(("systemd-resolve")) [::1]:1431 users:(("sqlservr")) [::1]:1434 users:(("sqlservr")) [::]:22 users:(("sshd"))

Only root can read the credentials file. 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 900 Oct 24 19:59 /root/mssql-credentials.txt SA_USER= SA_PASSWORD= <set-at-first-boot>= TLS_CERT= TLS_CERT_SHA256=

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

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

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

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

-C tells sqlcmd to trust the instance's self-signed certificate. Step 6 shows how to pin the certificate 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, CAST(SERVERPROPERTY('EngineEdition') AS int) AS engine_edition"

edition version cu engine_edition ---------------------------------------- -------------------- ---------- -------------- Standard Edition (64-bit) 16.0.4305.5 CU27 2

An engine_edition value of 2 means Standard edition.

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

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 belongs to this instance and is named after its hostname:

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

subject=CN = ip-172-31-81-248 notAfter=Oct 21 19:58:42 2036 GMT X509v3 Subject Alternative Name: DNS:ip-172-31-81-248, DNS:localhost, IP Address:127.0.0.1 sha256 Fingerprint=BF:09:A1:53:D2:0B:53:72:2E:8D:55:78:AB:AD:04:91:90:7F:3D:26:EE:7B:55:9F:CC:D9:2F:F7:E1:CB:C6:32

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 to your client and add it to the client's trusted certificates. Copy the public certificate only, never mssql.key. In tools that support it, you can pin the fingerprint instead. The certificate names localhost and 127.0.0.1, so verification also passes through the SSH tunnel in Step 8.

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 2022 Standard'');'); SELECT id, body FROM demo.dbo.notes; DROP DATABASE demo;"

id body -- ---- 1 hello from SQL Server 2022 Standard

For an application, create its own login and database instead of using sa. Run the following in an interactive sqlcmd session (/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C), and choose 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

The safest way to use SQL Server Management Studio, Azure Data Studio, VS Code or an application on your workstation is an SSH tunnel. With a 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 and encryption set to Mandatory (or Strict). 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 that connect by another name must trust the certificate, or use a certificate that you issue for that name (mssql-conf set network.tlscert and network.tlskey, then restart).